Paging in SQL Server 2014

 

Designing Query paging in SQL Server 2014
 
Recently, I have been working on SQL Server paging and realized that the latest way of paging query of SQL Server 2014 has great performance when compared to the classical or custom query that we use for paging.
 
In this article I am depicting both the old way of SQL Paging and how query paging works in SQL Server 2014 and difference between both.
 
Before we start with SQL Query on Paging lets create a sample demo table which insert 10K rows in a table.


create table pageTbl
(id int identity(1,1) not null primary key,
name varchar(25) not null,
dt datetime default getdate()
)
 
Insert 10 K rows in the demo table "pageTbl".
 
insert into pageTbl (name) values ('item' + convert(varchar,isnull(@@identity,0)))
go 10000
 
Get the count to verify whether 10K rows has been inserted.
select count(1) from pageTblGet the top 10 records to verify
select top 10 * from pageTbl 

 
You can see that the rows are inserted into the table with different item number.
 
The old way of paging is SQL Server is being done as shown below, 
declare two variables which hold page number and other for page size and
we create a virual row ROW_NUMBER which has an row count to order the rows and we use an order by clause to order the rows and few page calculations which used to display the relevant rows. 
declare @pagenum as int, @rowpage as int

set @pagenum = 2
set @rowpage = 10

select * from (select ROW_NUMBER() over (order by id) as number, name, dt from pageTbl) as mydata
where number between ((@pagenum - 1) * @rowpage + 1) and (@pagenum * @rowpage) order by number
 
If we run the above query the result will be shown as below, this will display rows from 11 to 20 as we need data for page 2 and the page size is 10.

The above query has been enhanced in of SQL Server 2012 or later versions.

declare @pagenum int, @rowspage int
set @pagenum = 2
set @rowspage = 20
select id, name, dt from pageTbl order by id offset((@pagenum-1) * @rowspage) rows fetch next @rowspage rows only

 



If you take a look at the Query execution plan between two queries, there is an filter which costs around 2% with the classical way. First it do an assignment, filter, top, segment and then it do the index scan.



 



 

With the latest way if you see the execution plan there will be an assignment and then it directly scan the index which has a faster way of doing paging. The cost is reduced dramatically when taking large number of records into consideration and the performance also varies.

 

This is the way you can do paging in SQL Server 2014.

 

Happy coding!

Read More

ManiRekha 12:04 AM  No comment

ALTER TABLE in SQL Server

The below SQL Tutorial explains all the basic syntactical ALTER statements that we can perform in SQL like Add Column, Alter Column, Rename Column, Drop Column etc..

DescriptionThe SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Adding a column
ALTER TABLE table_name
ADD column_name column-definition;
Ex:
The following statement will add a column "EMPName" in the Employee table which has a size of 50 characters.

ALTER TABLE Employee ADD EMPName VARCHAR(50)
GO

Adding a column with default value:
The following example adds a column that allows null values and has no values provided through a DEFAULT definition. In the new column, each row will have NULL.

ALTER TABLE dbo.Employee ADD EMPName VARCHAR(50) NULL ;
GO

Adding a column with UNIQUE constraint:
The following example adds a new column with a UNIQUE constraint.

CREATE TABLE dbo.Employee (column_a INT) ;
GO
ALTER TABLE dbo.Employee ADD EMPName VARCHAR(50) NULL
CONSTRAINT empname_unique UNIQUE ;
GO

To view the schema:

EXEC sp_help Employee ;
GO

Adding a column with CHECK constraint:
The following example adds a constraint to an existing column in the table. The column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added.

CREATE TABLE dbo.Employee ( column_a INT) ;
GO

INSERT INTO dbo.Employee VALUES (-1) ;
GO

ALTER TABLE dbo.Employee WITH NOCHECK
ADD CONSTRAINT emp_check CHECK (column_a > 1) ;
GO

EXEC sp_help Employee;
GO

The WITH NOCHECK option will ignore validating the existing records with the constraint that we are creating.

Adding a column with default constraint:
The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column with value 50.

CREATE TABLE dbo.Employee ( EmpID INT, EMPName VARCHAR(50)) ;
GO
INSERT INTO dbo.Employee (EmpID)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.Employee
ADD CONSTRAINT emp_def
DEFAULT 'ABCEmployee' FOR EMPName ;
GO

Once the constraint has been created insert a new record without specifying a value for EMPName where the default constraint has been created.

INSERT INTO dbo.Employee (EmpID) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.Employee ;
GO

You will see the ABCEmployee is being inserted to the new record as a default value.

Adding nullable column with default values:
The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

CREATE TABLE dbo.Employee ( column_a INT) ;
GO
INSERT INTO dbo.Employee VALUES (1) ;
GO

ALTER TABLE dbo.Employee
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.Employee ;
GO


Modifying an existing column

ALTER TABLE table_name
  ALTER COLUMN column_name column-definition;

Ex:
ALTER TABLE Employee ALTER COLUMN EMPName VARCHAR(100)

 The above statement modify the size of the existing column "EMPName" from 50 to 100 characters.


Drop an existing column
ALTER TABLE table_name
  DROP COLUMN column_name;

Ex:
ALTER TABLE Employee DROP COLUMN EMPName

The above statement will drop the column "EMPName" from Employee table.

Read More

ManiRekha 1:37 AM  1 comment

Deleting duplicates in SQL Server table

Delete duplicate rows in SQL Table:

There are many situations where we need to delete duplicate rows in SQL tables. Infact, there are many ways to do it but one of the finest and simplest way is below.

I am providing the below example using temp tables. Replace the temp table with the tablename where you want to delete duplicate rows. In the below example I am using ROW_NUMBER() and Partition By keywords.

The beauty of the below process is this will remove duplicate with a single query.


If Exists(Select * From tempdb.Information_Schema.Tables Where Table_Name Like '#DuplicateRows%')
Drop Table #DuplicateRows

Create Table #DuplicateRows ([Id] int, [Name] varchar(50), [Age] int, [Sex] bit default 1)
Go

Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(4,'John',26,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Abraham',28,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Lincoln',30,default)

Delete T From
(Select Row_Number() Over(Partition By [ID],[Name],[Age],[Sex] order By [ID]) As RowNumber,* From #DuplicateRows)T
Where T.RowNumber > 1

Select * From #DuplicateRows

Read More

ManiRekha 6:56 AM  No comment

Understanding IDENTITY_INSERT

Understanding IDENTITY_INSERT in SQL Server

Many of us will get a basic question on whether the IDENTITY_INSERT is ON or OFF on a SQL Table. We also have some confusion between IDENTITY and IDENTITY_INSERT.

In this article i will explain the basic use of IDENTITY_INSERT and how it exactly works.

Before understanding the IDENTITY_INSERT we need to know what is an identity column. It is column which keeps on incrementing without supplying the value explicitly during insertion.

In the below table EID is an identity column which will be incremented by 1 when a new record is inserted.

 

Now, for example let say I have a requirement to insert a EID with value as ‘10’, just below of this value. You can insert a custom value in two ways:

1. Either you turn off the Identity property by going into design, and opting for column properties, i.e., using GUI. After inserting that value, again making it ON, so that it will continue incrementing it.
2. Or using T-SQL code, as shown below:

SET IDENTITY_INSERT dbo.Customers ON;

INSERT INTO dbo.Employee VALUES (10, 'EmpName10') you will get an error,

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table ‘dbo.Employee’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

so you need to mention the Columns explicitly during insert as show below:

INSERT INTO dbo.Employee(EID,ENAME) VALUES (10, 'EmpName10')
Now, if you do a select * from Employee you will get the result as shown below,

And now if you want to continue to insert the next identity numbers you shouldn't forget to make the IDENTITY_INSERT Property to OFF on that table,

SET IDENTITY_INSERT dbo.Customers OFF;

In Simple words,

If you make IDENTITY_INSERT ON on a table you have to insert value in the identity column explicitly during insertion. If the IDENTITY_INSERT is OFF on a table it mean the value in the identity column will be auto generated.

In case if you are trying to insert some custom values in identity column make sure you set the IDENTITY_INSERT property to OFF once you have done with your insertion else the values in the identity column will not be auto generated.

Happy Coding!

Read More

Venkat 10:27 AM  No comment

Generating Identity Column in SELECT

Generating Identity Column in SELECT statements at runtime

There are situations where we need to generate an identity column [run time] seeding 1 when inserting records into staging or another tables. For example, you have a table where you have a primary key and identity has not been set on this primary key column.

If you want to insert records into this table on daily basis you need to generate the value for identity column when you are trying to insert records using select statements.

There are two easy ways to generate an primary identity column under select statements:

1. If you are inserting values using INTO statement you can use IDENTITY(INT,1,1) function in your select statement to generate unique values as shown below:

SELECT
 IDENTITY(INT, 1,1) AS EmployeeID,
 VALUE INTO #Ranks
FROM YourTable
WHERE 1=0

INSERT INTO #Ranks SELECT SomeColumn FROM YourTable ORDER BY SomeColumn

SELECT * FROM #Ranks Order By Ranks

The EmployeeID will be generated with unique identity number.

2. Another way of doing this is using; ROW_NUMBER() function,

SELECT ROW_NUMBER() OVER (ORDER BY colA) AS Row, cola
                FROM table1
This will also generates column with unique value seeded by 1.

Hope this helps!

Read More

Venkat 1:21 AM  No comment