In my previous article, I explained how to use offset rows and fetch next statement. In this article, I will explain Dont's for Offset Rows and Fetch Next.
Let me explain it with simple examples.
Example 1 : (Fetch clause must be greater than zero)
USE AdventureWorks2012 GO SELECT * FROM [HumanResources].[vEmployee] ORDER BY [JobTitle] OFFSET 10 ROWS FETCH NEXT 0 ROWS ONLY --OUTPUT
Msg 10744, Level 15, State 1, Line 4
The number of rows provided for a FETCH clause must be greater then zero.
Example 2 : (Offset clause must have integer only)
USE AdventureWorks2012 GO SELECT * FROM [HumanResources].[vEmployee] ORDER BY [JobTitle] OFFSET 1.1 ROWS FETCH NEXT 10 ROWS ONLY --OUTPUT
Msg 10743, Level 15, State 1, Line 3
The number of rows provided for an OFFSET clause must be an integer.
Example 3 : (Offset clause may not be negative)
USE AdventureWorks2012 GO SELECT * FROM [HumanResources].[vEmployee] ORDER BY [JobTitle] OFFSET -10 ROWS FETCH NEXT 10 ROWS ONLY --OUTPUT
Msg 10742, Level 15, State 1, Line 3
The offset specified in an OFFSET clause may not be negative.
Example 4 : (A Top cannot be used in the same query or sub query as an offset.)
USE AdventureWorks2012 GO SELECT Top 5 * FROM [HumanResources].[vEmployee] ORDER BY [JobTitle] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY --OUTPUT
Msg 10741, Level 15, State 2, Line 2
A TOP cannot be used in the same query or sub-query as an OFFSET.
If you know any other Dont’s, do share.
Reference : MSDN
SQL Server Portal 