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