LIMIT N vs TOP N in SQL Server

Question

I am starting to use SQL Server. The following works:

SELECT TOP 100 * 
FROM SalesRawData 
WHERE Title = N'Sriracha'

But the following causes an error:

SELECT * 
FROM SalesRawData 
WHERE Title = N'Sriracha' 
LIMIT 100

Why can't I use the "normal" limit syntax at the end here?

@Shadow I see -- so actually there's no such thing as a limit in sql-server. Correct? — David542, Jun 22 '18 at 23:32
Because tsql does not support the limit clsuse, it supports the top clause. — Shadow, Jun 22 '18 at 23:33
`LIMIT` isn't the "normal" way of doing this - it's the **MySQL** (and Postgres) **specific** way of handling getting only a few rows. Sybase chose to use `SELECT TOP...` in T-SQL instead. — marc_s, Jun 23 '18 at 06:03

score 4 · Answer 1 · answered Jun 22 '18 at 23:44

SQL Server supports two ways to limit the number of results returned from a query. The simplest is TOP, which you have already discovered. The more advanced is FETCH, which also allows for specifying an offset to implement paging. Written using the FETCH method, your query would be SELECT * FROM SalesRawData where Title = N'Sriracha' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY. You can find the documentation for TOP here and the documentation for FETCH here.

score 4 · Accepted Answer · answered Jun 22 '18 at 23:47

DMBS vendors have proprietary syntax to provide LIMIT functionality. A draft of the ANSI/ISO standards allows a OFFSET and FETCH clauses, which is also implemented in SQL Server. ORDER BY is required (and with TOP also) to provide predictable results.

Below is an alternative:

SELECT * FROM SalesRawData
WHERE Title = N'Sriracha' 
ORDER BY Title OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY;

LIMIT N vs TOP N in SQL Server

2 Answers2