SQL with Manoj

As per MS BOL, the new Denali’s OFFSET-FETCH Clause provides an option to fetch only a window or page of a fix set of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

This was the most awated feature for the frontend/GUI developers to display volumnous data in a small grid, page by page. Prior to this they used to devise not so complex but a bit complex SQL logic to display records page by page. Introduction of this feature has limited the complex logic to a few lines of single SQL statement which is much more optimized.

Let’s see how can we use this feature:

--// Example #1:

-- This will skip first 100 records and show next 20 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 100 ROWS
FETCH NEXT 20 ROWS ONLY;
GO

--// Example #2:

-- Start from first record and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Skip first 10 records and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 10 ROWS -- To show next page of 10 records, just change the offset by adding the page size, i.e 10.
FETCH NEXT 10 ROWS ONLY;
GO

--// Example #3:

-- To use this in front-end, the above logic can be made dynamic by using a few variables, like:
DECLARE @StartRec INT
DECLARE @PageSize INT
DECLARE @RecordEnd INT

SET @StartRec = 0
SET @PageSize = 10
SET @RecordEnd = @PageSize

WHILE @RecordEnd <> 0 -- I'm using WHILE loop to simulate it here
BEGIN
	SELECT BusinessEntityID,  LoginID, JobTitle
	FROM HumanResources.Employee
	ORDER BY BusinessEntityID
	OFFSET @StartRec ROWS
	FETCH NEXT @PageSize ROWS ONLY

	SET @RecordEnd = @@ROWCOUNT -- Exit loop at 0

	SET @StartRec = @StartRec + @PageSize
END
GO

Rules to use OFFSET FETCH (via MSDN):
1. ORDER BY is mandatory to use OFFSET and FETCH clause.
2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
3. TOP cannot be combined with OFFSET and FETCH in the same query expression.
4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

More on MSDN, link: http://msdn.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx