OFFSET & FETCH options in SQL 2012

by Valmik 26. April 2012 10:32
SQL 2012 has a new T-SQL that helps your SELCT query to implement the page solution. We have used combination of TOP and ORDER BY in the past to handle this, However, OFFSET & FETCH clauses has more benefits.
  
OFFSET: Allows you to set the record pointer to a specific row in a table
FETCH: Allows you to select number of rows you request in Fetch.
 
For OFFSET you will specify starting row and page size will be specified to FETCH.
Example:
  
SELECT * FROM TableName
ORDER BY Table_ID
OFFSET 3 ROWS
FETCH NEXT 5 ROWS ONLY
 
FETCH and OFFSET can be used with variables as well.
Below is example:
DECLARE @OFFSET INT=3, @FETCH INT=5
SELECT * FROM Table_Name
ORDER BY Table_ID
OFFSET @OFFSET ROWS
FETCH NEXT @FETCH ROWS ONLY

Tags:

Root Cause behind DBProject Error 628 TSD01261

by Valmik 4. November 2011 08:43

Most often when we deal with the multiple .sql files to be included in post-deployment script in a MS database project, We may encounter this problem and hence the project build gets broken. The error text reads something like:

Error 628 TSD01261: The syntax check failed 'Incorrect syntax near USE.' in the batch near 'USE [StagingData]
' .  0 0 StagingData

 

After some googling and some thinking(:)), I found the root cause. It errors out because it expects some separator between two files that are specified in post deployment script list specified with :r .\blah.sql

So each of the script file needs to end with the statement GO and it works perfectly fine.

Tags:

EOMONTH () Function in SQL Server Denali

by Amol 22. September 2011 09:52

SQL Server Denali introduced new function EOMONTH() to calculate End Of Month date

SELECT EOMONTH (GETDATE()) LastDayOfMonth  -- Will return last day of the month

LastDayOfMonth
-------------------------
2011-09-30 00:00:00.000

In earlier version we need to many date time function to the last day of the month.

Tags: , ,

General

Category

Recent Posts

Tag cloud