SQL Server 2012 TSQL enhancements – Part 2


In my previous article SQL Server 2012 TSQL enhancements – Part 1 we went through String function – CONCAT and FORMAT introduced in SQL Server 2012. In this article we will see new feature of SQL 2012 that helps to implement Paging logic in Query without using any ranking function called “Ad-hoc query paging implementation”.

Developer usually used ROW_NUMBER() function to handle paging functionality in SQL query. For example…

SELECT *
FROM( SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS sequencenumber, * FROM Customers
) ASTempTable
WHERE sequencenumber > 10 and sequencenumber <= 20

The Ad-Hoc Paging feature of SQL 2012 allows you to returns a block data. For example, the first execution of the query may return first 10 records, the next execution returns the next 10 records and so on. We all are familiar with ORDER BY clause – returns sorted data. Microsoft has extended ad-hoc paging feature to existing ORDER BY clause by adding two arguments – OFFSET and FETCH Next.

OFFSET                 – Specifies the number of rows to skip before it starts to return rows from the query

FETCH NEXT    – Specifies the number of rows to return after the OFFSET clause has been processed. FETCH NEXT is compatible only with OFFSET clause.

Syntax-

ORDER BY order_by_expression  [ ASC | DESC ]

[OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }]

[FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]

 

Code Snippets:

declare @tblSampleData TABLE(Id int identity(1, 1)primary key, Value varchar(10))
insert @tblSampleData(Value)
values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p')
--Example 1
select * from @tblSampleData order by Id
--Example 2
select * from @tblSampleData order by Id offset 5 rows
--Example 3
select * from @tblSampleData order by Id offset 0 rows fetch next 5 rows only
--Example 4
select * from @tblSampleData order by Id offset 5 rows fetch next 5 rows only
--Example 5
DECLARE @PageNumber INT = 1, @RowsCountPerPage INT = 5;
SELECT * FROM @tblSampleData
ORDER BY ID
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS only
SET @PageNumber = 2; SET @RowsCountPerPage = 5;
SELECT * FROM @tblSampleData
ORDER BY ID
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS only

The above code snippets specifies the use of OFFSET and FETCH clause.

Example 1 – Return all rows sorted by ID column.

Example 2 – Uses the OFFSET clause to skip first 5 rows and return remaining rows.

Example 3 – Uses the OFFSET clause to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

Example 4 – Uses the OFFSET clause  to skip first 5 row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

Example 5 – This example shows how to implement paging logic using variables.

Try above code snippets in query analyzer.

Advertisements

One thought on “SQL Server 2012 TSQL enhancements – Part 2

  1. Pingback: SQL Server 2012 TSQL enhancements – Part 3 | SQL Server Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s