Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SQL Server 2011 (SQL11 / Denali) – TSQL Enhancements to the ORDER BY clause

Nov 15 2010 11:44PM by Jacob Sebastian   

In the previous post, we saw the new TSQL syntax introduced in SQL Server 2011 to support server side paging. We saw the new TSQL keywords OFFSET and FETCH NEXT in the previous post. These new keywords are introduced as an extension to the ORDER BY clause. Let us examine them in detail.

OFFSET

OFFSET specifies the number of rows to skip before start retrieving the rows. This is evaluated after sorting the result set using the columns specified in the ORDER BY clause.

What is very interesting about OFFSET is that, you can specify a variable along with it. This is helpful to write generic handlers where the page size and current page number are passed as parameters.

DECLARE @offset INT
SELECT @offset = 10
SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET @offset ROWS 
FETCH NEXT 10 ROWS ONLY;

It can also be a function, subquery or a valid TSQL expression that returns 0 or a positive integer value. If the expression returns a negative value or NULL, the query will fail with an error.

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET (SELECT offset FROM sometable) ROWS 
FETCH NEXT 10 ROWS ONLY;

OFFSET is followed by ROWS or ROW. You can use either one of them.

FETCH NEXT

FETCH can be used with either FIRST or NEXT and they have the same meaning. NEXT or FIRST is followed by the number of rows to retrieve. The number can be a constant value, result of a query or an expression. Here is an example that uses a sub-query to specify the number of rows to retrieve.

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET (SELECT offset FROM setuptable) ROWS 
FETCH NEXT (SELECT pagesize FROM setuptable) ROWS ONLY;

The expression (or sub-query) should return a positive integer value that is greater than 0. If 0, NULL or a negative value is specified the query will fail with an error.

Tags: TSQL, BRH, SQLSERVER 2011, DENALI, SQL11, order by, offset, fetch next,


Jacob Sebastian
1 · 100% · 32235
1 Readers Liked this
Alpesh Patel Liked this on 12/5/2011 10:28:00 PM
Profile
1
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • I've not installed Denali yet. Is there any support for running totals with the OVER clause yet? It didn't seem like it from reading Books On Line.

    commented on Dec 8 2010 9:38PM
    Martin Smith
    654 · 0% · 52
  • AFAIK, it is not in this CTP.

    commented on Dec 9 2010 3:19AM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

"SQL Server 2011 (SQL11 / Denali) – TSQL Enhancements to the ORDER BY clause" rated 5 out of 5 by 1 readers
SQL Server 2011 (SQL11 / Denali) – TSQL Enhancements to the ORDER BY clause , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]