0

What's wrong with the following statement? I'm using SQL Server 2008.

use Demo;

SELECT * FROM users
limit 0 , 30

I got: 

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '0'.

That's really weird. I tried Google but didn't find much info.

Wander Nauta
  • 18,832
  • 1
  • 45
  • 62
daisy
  • 22,498
  • 29
  • 129
  • 265

3 Answers3

5

LIMIT is a MySQL keyword. Use the TOP or ROWCOUNT keywords in MS SQL Server.

Note that TOP can accept a variable, e.g. SELECT TOP( @NumberOfRows ) * FROM Foo;

See: How to use LIMIT keyword in SQL Server 2005? (also valid for 2008)

Depending on how LIMIT is used, there is an important difference between LIMIT and TOP (ranges/pages of data versus just capping the number of results). In that case, the MS SQL syntax is more verbose; usually the ROW_NUMBER() function does the trick combined with some simple logic to calculate the values which are valid for the desired page.

Simple Range Selection Example

SELECT * FROM
(
    SELECT 
        ROW_NUMBER() OVER( ORDER BY SomeColumn ASC ) AS RowNumber, 
        AnotherColumn 
    FROM dbo.MyTable
) Q

WHERE RowNumber BETWEEN 20 AND 30; -- these integers can be variables
Community
  • 1
  • 1
Tim M.
  • 53,671
  • 14
  • 120
  • 163
4
select top 30 * from users

SQL Server uses that syntax rather than the limit form.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

SQL Server doesn't support the limit clause (that is the MySQL and PostgreSQL syntax). You should use top like this:

select top 30 * from users
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123