SQL Server Portal

In this article we will discuss about a new error message (A TOP cannot be used in the same query or sub-query as an OFFSET) introduced in SQL SERVER 2012.

Let’s discuss it in detail:

Message Number: 10741

Severity : 15

Error Message: “A TOP cannot be used in the same query or sub-query as an OFFSET.”

Error Generation:
Let me create an example to generate this error:

USE AdventureWorks2012
GO
Select top 5 BusinessEntityID,[FirstName]
, [LastName],[JobTitle] as [JT]
from HumanResources.vEmployee A
Order By [JobTitle]
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
--OUTPUT

Msg 10741, Level 15, State 2, Line 4
A TOP cannot be used in the same query or sub-query as an OFFSET.

Resolution:

In the above example, you can see, I upgraded my query from sql server 2008 to 2012 and used new data paging technique introduced in SQL Server 2012, but I did not (deliberately) remove TOP Keyword from the query. Remember, whenever you use data paging  (OFFSET ROWS), make sure you don’t use TOP keyword in the query to avoid this error. According to MSDN : TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope).

Reference : MSDN