Feeds:
Posts
Comments

Archive for the ‘DML Statements’ Category

Paging became quite simpler & easy to script and manage by using OFFSET & FETCH NEXT keywords in SQL Server 2012 & above. I have written quite a detailed article earlier about it and implemented it in my most of the solutions wherever required. However, when you implement/use paging in your script, you face a big challenge, that is, to find the total number of records in that particular result-set.

Given below are the three methods which you can use to get the total row count from OFFSET / FETCH NEXT.
Before proceeding with the solutions, let me create a sample.

Sample :

USE AdventureWorks2014
GO
-- Create Sample Table Table
CREATE TABLE [dbo].[SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

-- Insert bulk data into sample table
-- It may take few minutes depends upon the server performance
INSERT INTO [dbo].[SalesOrderDetail]
SELECT * FROM [SALES].[SalesOrderDetail]
GO 100

-- Verfiy the data
Select * from [dbo].[SalesOrderDetail]
GO

Method 1 : Using COUNT(*) OVER()

USE AdventureWorks2014
GO
DECLARE
  @PageSize INT = 10,
  @PageNum  INT = 1;

SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]= COUNT(*) OVER()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;
GO
--OUTPUT

row count using Offset 1.1

Method 2 : Using Common Table Expression

USE AdventureWorks2014
GO
DECLARE
    @PageSize INT = 10,
    @PageNum  INT = 1;

;WITH Main_CTE AS(
	SELECT [SalesOrderID]
	, [SalesOrderDetailID]
	, [CarrierTrackingNumber]
	, [OrderQty]
	, [ProductID]
	, [SpecialOfferID]
	FROM [dbo].[SalesOrderDetail]
)
, Count_CTE AS (
	SELECT COUNT(*) AS [TotalCount]
	FROM Main_CTE
)
SELECT *
FROM Main_CTE, Count_CTE
ORDER BY Main_CTE.SalesOrderID
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

Method 3 : Using Cross Apply

USE AdventureWorks2014
GO
DECLARE @PageSize INT = 10,
        @PageNum  INT = 1;

 SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]
FROM [dbo].[SalesOrderDetail]

CROSS APPLY (SELECT COUNT(*) TotalCount
FROM [dbo].[SalesOrderDetail] ) [Count]
ORDER BY SalesOrderID
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

All of the above methods give you the same result-sets. Lets view their performance given below.

S.No

Method

CPU Time

Elapsed Time

1

COUNT(*) OVER()

30654 ms

40372 ms

2

Common Table Expression

11762 ms

7665 ms

3

Cross Apply

11794 ms

7373 ms

Conclusion :
On the basis of above results, I would recommend that you either use Common Table Expression or Cross Apply to get the faster results.

Note : The above queries have been tested on ~12 Million records.

Read Full Post »

In SQL Server 2012, Microsoft has introduced a lot of new T-SQL features and enhancements and one of the best features is Paging. We have been doing this data paging in previous versions of SQL server by writing a stored procedure or a complex query.
Here is a sample, how we were using data paging in SQL Server 2005/ 2008.

USE AdventureWorks
GO
SELECT *
FROM   (SELECT ROW_NUMBER() OVER(ORDER BY EmployeeID) AS
       rownumber, [FirstName], [LastName],[JobTitle] FROM HumanResources.vEmployee) AS Salaries1
WHERE  rownumber >= 10 AND rownumber = 10 AND rownumber 

But, now it is very easy to use and implement paging in SQL Server 2012. We only need to set two keywords (OFFSET, FETCH NEXT) with Order By Clause and we can get our required records.

Lets Proceed first with each keyword.

Order By Offset :

USE AdventureWorks2012
GO
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
OFFSET 10 ROWS

If we use offset with order by clause, the query excludes the number of records we mentioned in OFFSET n Rows. In the above example, we used OFFSET 10 ROWS so, SQL will exclude first 10 records from the result and display the rest of all records in the defined order.

Order By Offset With FETCH NEXT :

USE AdventureWorks2012
GO
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID

OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

If we use offset with fetch next, we can define how many records we need to exclude. Also we can define that after exclusion how many records we need to pick up. In the above example, SQL excludes first 10 records and will pick up 10 records afterwards.
In other words, we can say that whenever we need to do paging we need 2 things. 1st, the page no. and 2nd the no. of records in each page. Here OFFSET is used for page number and FETCH NEXT is the number of records in each page.

Order By Fetch Next Rows Only:
If, we use Fetch Next with order by clause only without Offset, SQL will generate an error. We cannot use Fetch Next without Offset.

USE AdventureWorks2012
go
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
--OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

Given below is a simple stored procedure to perform paging in SQL server 2012.

USE AdventureWorks2012
go
Create Procedure dbo.Sp_Data_Paging
@PageNo int,
@RecordsPerPage int
AS

Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
OFFSET (@PageNo-1)*@RecordsPerPage ROWS
FETCH NEXT @RecordsPerPage ROWS ONLY
GO
Sp_Data_Paging 1,10 --First Page
GO
--Result

Sp_Data_Paging 2,10 --2nd Page
--Result
GO

Sp_Data_Paging 3,10 --3rd Page
--Result
GO

Conclusion :

These two keywords OFFSET and FETCH NEXT clause give boost to data pagination in SQL server 2012.
It also improves performance (because it picks up only certain records from the database) and reduces the number of codes and effort. Now the developers can do the data paging not only from front end (. NET) but also from the back end.

Read Full Post »