"LIMIT" in SQL Server


I’ve recently come across a number of folks in different contexts who were trying to figure out how to acheive the equivalent of MySQL’s “LIMIT” clause in SQL Server.  The basic scenario is that you want to return a subset of the results in a query from row number X to row number Y.


The good news is that SQL Server 2005 makes this really easy.  We introduced a new set of ranking functions into the T-SQL language that let you accomplish the basic LIMIT semantics and much more if you feel like getting fancy.


Quick example:



SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10


The query above returns rows 6 through 10 from sys.databases as ordered by the “name” column.  ROW_NUMBER() is the key function we’re using here.  It’s one of a set of ranking functions introduced in 2005.  Note that it’s always accompanied by an OVER clause that specifies the ordering that the row_number should be based on. 


For details on the ROW_NUMBER() function and its use, see: http://msdn2.microsoft.com/en-us/library/ms186734.aspx


For info on other ranking functions in SQL Server 2005, see: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Comments (47)

  1. Or used with a CTE like in

    WITH TblDatabases  AS

    (

    SELECT *, ROW_NUMBER() OVER (ORDER BY Name) as Row FROM sys.databases

    )

    SELECT * FROM TblDatabases WHERE Row>5 and Row<10

  2. Lloyd says:

    Wouldn’t it just be better to implement a LIMIT clause into the next version of SQL Server?!?

  3. scott says:

    no kidding.. LIMIT and OFFSET are so useful

  4. stefo says:

    I also agree with you Lloyd. What if I have to use SqlServer 2000?

  5. sa says:

    ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss””””’jklklklklklklklklklklklklklklklklklklklklklklklkl

  6. Raj says:

    How to use Limit Function in sql, give me a some sample codes

    Thank u

  7. Hector Torres says:

    MySQL

    SELECT emp_id,lname,fname FROM employee LIMIT 20,10

    SQL Server

    select * from (

    select top 10 emp_id,lname,fname from (

       select top 30 emp_id,lname,fname

       from employee

      order by lname asc

    ) as newtbl order by lname desc

    ) as newtbl2 order by lname asc

    from http://www.fluzo.org/post/300

  8. CHARLIE says:

    i am trying to get to ADVFN.CO.UK THIS IS WHAT I AM GETTING ANY ONE HELP

    Bad Request

    Your browser sent a request that this server could not understand.

    Size of a request header field exceeds server limit.

    Cookie: OASISID=41e97894d34f5; OASISCAP=a%3A76%3A%7Bi%3A111%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1105819796%3B%7Di%3A292%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1105819990%3B%7Di%3A293%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1105820109%3B%7Di%3A228%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1105915958%3B%7Di%3A295%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1105878340%3B%7Di%3A294%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1105878349%3B%7Di%3A596%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1113210421%3B%7Di%3A499%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1113338548%3B%7Di%3A777%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1113507993%3B%7Di%3A653%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1113507285%3B%7Di%3A1340%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132218022%3B%7Di%3A1313%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1123483384%3B%7Di%3A592%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1136333534%3B%7Di%3A1353%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1124981335%3B%7Di%3A1352%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1124925630%3B%7Di%3A999%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1130149848%3B%7Di%3A1387%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1124323003%3B%7Di%3A1462%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1124697623%3B%7Di%3A1464%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1124805524%3B%7Di%3A1518%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1124622118%3B%7Di%3A1526%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1125994451%3B%7Di%3A1549%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1127716094%3B%7Di%3A1600%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1127467667%3B%7Di%3A1613%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1127596628%3B%7Di%3A1611%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132736661%3B%7Di%3A1631%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1130494103%3B%7Di%3A1693%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1130137887%3B%7Di%3A1700%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1128366827%3B%7Di%3A1675%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1127459598%3B%7Di%3A1706%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1133103476%3B%7Di%3A1620%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1129324271%3B%7Di%3A1718%3Ba%3A2%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1131903530%3B%7Di%3A1557%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1128327771%3B%7Di%3A1715%3Ba%3A2%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1132046466%3B%7Di%3A1712%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1131460778%3B%7Di%3A1713%3Ba%3A2%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1131455553%3B%7Di%3A1719%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1131903439%3B%7Di%3A1717%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132172986%3B%7Di%3A1805%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1136368420%3B%7Di%3A1818%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1131038632%3B%7Di%3A1292%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1129742974%3B%7Di%3A1831%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1131702896%3B%7Di%3A1893%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1130343325%3B%7Di%3A1882%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137402563%3B%7Di%3A1895%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1135120170%3B%7Di%3A1896%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1136049880%3B%7Di%3A1922%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1130780340%3B%7Di%3A1904%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1132671946%3B%7Di%3A1914%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1133125008%3B%7Di%3A1916%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1131646851%3B%7Di%3A1918%3Ba%3A2%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1134555264%3B%7Di%3A1990%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1131013123%3B%7Di%3A1915%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1131640389%3B%7Di%3A1919%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1134573717%3B%7Di%3A2042%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1135243452%3B%7Di%3A1917%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1134570653%3B%7Di%3A2056%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132241096%3B%7Di%3A2038%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132270088%3B%7Di%3A2074%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132567134%3B%7Di%3A2089%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1132671661%3B%7Di%3A2091%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1134573685%3B%7Di%3A2104%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137762380%3B%7Di%3A1899%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1133120482%3B%7Di%3A2071%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1138612197%3B%7Di%3A2052%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137762506%3B%7Di%3A2110%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1138539393%3B%7Di%3A2122%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1133864395%3B%7Di%3A2130%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1135517299%3B%7Di%3A2128%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1134420749%3B%7Di%3A2153%3Ba%3A2%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1138564911%3B%7Di%3A2156%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137764178%3B%7Di%3A2199%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137575568%3B%7Di%3A2237%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1138582000%3B%7Di%3A2266%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137486087%3B%7Di%3A2270%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137321799%3B%7Di%3A2281%3Ba%3A2%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1137421424%3B%7D%7D; 6c442c17fb29fa01949d07b741a5fb83=bd9f9cf44eb7220d23f4c845e4e6b5ea; nav_version=2; ADVFNUID=rOHbf9Wh; pf2=0; pf1=0; c7c247059e6d64a9a6aa89d62e58c3e2=51f9c14def051119868132110c73b3b0; OASISCAP_2=a%3A29%3A%7Bi%3A4686%3Ba%3A3%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1185777351%3Bi%3A2%3Bi%3A1188196551%3B%7Di%3A6493%3Ba%3A3%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1185949679%3Bi%3A2%3Bi%3A1217399279%3B%7Di%3A6618%3Ba%3A3%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1187096925%3Bi%3A2%3Bi%3A1187701725%3B%7Di%3A6619%3Ba%3A3%3A%7Bi%3A0%3Bi%3A5%3Bi%3A1%3Bi%3A1186527832%3Bi%3A2%3Bi%3A1188947032%3B%7Di%3A6446%3Ba%3A3%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1186529170%3Bi%3A2%3Bi%3A1187738770%3B%7Di%3A6599%3Ba%3A3%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1187126153%3Bi%3A2%3Bi%3A1187212553%3B%7Di%3A6665%3Ba%3A3%3A%7Bi%3A0%3Bi%3A4%3Bi%3A1%3Bi%3A1187126186%3Bi%3A2%3Bi%3A1187212586%3B%7Di%3A5878%3Ba%3A3%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1187133061%3Bi%3A2%3Bi%3A1187219461%3B%7Di%3A6489%3Ba%3A3%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1187133415%3Bi%3A2%3Bi%3A1187219815%3B%7Di%3A6596%3Ba%3A3%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1187138316%3Bi%3A2%3Bi%3A1187224716%3B%7Di%3A6445%3Ba%3A3%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1187164607%3Bi%3A2%3Bi%3A1187251007%3B%7Di%3A6560%3Ba%3A3%3A%7Bi%3A0%3Bi%3A3%3Bi%3A1%3Bi%3A1187165011%3Bi%3A2%3Bi%3A1187251411%3B%7Di%3A6497%3Ba%3A3%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1187165308%3Bi%3A2%3Bi%3A1187251708%3B%7Di%3A6490%3Ba%3A3%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1187167028%3Bi%3A2%3Bi%3A1187253428%3B%7Di%3A6584%3Ba%3A3%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1187167212%3Bi%3A2%3Bi%3A1187253612%3B%7Di%3A5972%3Ba%3A3%3A%7Bi%3A0%3Bi%3A2%3Bi%3A1%3Bi%3A1187169163%3Bi%3A2%3Bi%3A1187255563%3B%7Di%3A6642%3Ba%3A3%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1187170572%3Bi%3A2%3Bi%3A1187256972%3B%7Di%3A5883%3Ba%3A3%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1187171228%3Bi%3A2%3Bi%3A1187257628%3B%7Di%3A6643%3Ba%3A3%3A%7Bi%3A0%3Bi%3A1%3Bi%3A1%3Bi%3A1187172060%

  9. Massimo says:

    Thank you! Your example was the most "clean" and easy all around he pages that I’ve checked!

    To Peter Schneider, CTE is good and clean… but is not working over ASP pages 🙂

  10. Perrin says:

    Are there any intentions of implementing a real LIMIT statement into SQL Server? It is an SQL standard after all.

  11. Mel says:

    I really hope SQL Server adds LIMIT and OFFSET

  12. Steven says:

    > The good news is that SQL Server 2005 makes this really easy

    No it doesn’t.  Implement the LIMIT statement.

  13. Ryan Bastic says:

    Yeah, frankly, compared to MySQL here:

    SELECT * from bar WHERE blah LIMIT 10,20 — gets records 10-20

    SQL Server is DISGUSTING and horribly deficient.

  14. Dirk says:

    @Ryan: we don’t think sql server is disgusting. The other way round! For our emailmarketing solution we switched from MySql to SqlServer 2005 because was MUCH more stable (our windows services can now run without having to restart it every 3 days) and performant.

  15. A.C. says:

    Dirk: Your comment really doesnt have anything to do with fact that Sql Server "trick" for LIMIT is disguisting. Btw, if you were forced to restart MySql every 3 days, problem is probably in your application, not the DB engine itself.

  16. Dirk(another one) says:

    This might look nice and seems to work well, however it does involve a full table scan, which in my case means traversing over a billion records. As I can remember mysql limit does not need a FTS, so with a lot of rows, mysql performs better.

    Which off course brings back the question already asked: Why not implement limit into SQL server?

  17. Xigga says:

    LIMIT is so much easier and cleaner.  We should send emails to SQL Server feedback team about it.

  18. murat yaman says:

    Rather than changing the sql2005, they added a new function row_number() apparently, and supposedly it involves many changes in the SQL interpretation side to add LIMIT clause..

    Stupidity arises when you have 1million records!

    (Because m$ sql has only use of TOP keyword)

    if you have to select a 1000 records in the middle of the recordset, you have to select half a million records in ASC order, then reverse the subquery using DESC and you get your 1000 records.

    They simply didn’t implement the db engine performance in mind, they must be using very old source code and they don’t want to change loads of stuff because of "our silly little "LIMIT" clause as users.

    I’m sure they’ll implement it when THEY need it or feel like that!!

    Good luck! 🙁 I use m$ sql but try to avoid it where possible.

    Give way to real databases such as PostgreSQL and Firebird! :)))

  19. Viktor says:

    Why just not implement LIMIT? I’ve been working with MySQL for a few years now, and the only reason I’m using SQL Server now is because we use it at work. I tried to run some SQL queries written for MySQL, but it keeps complaining on my LIMIT clauses, which is very annoying and disturbing.

  20. Anderson Fortaleza says:

    "The good news is that SQL Server 2005 makes this really easy"

    Really easy compared to SQL Server 2000, which is a nightmare to make a simple LIMIT query. If ROW_NUMBER() is wider than LIMIT, it’s much more complex to use and LIMIT solves 99% of our daily problems, why bother with ROW_NUMBER() ?

    I’m starting to regreat using MSSQL simply because you didn’t implement something so OBVIOUS as LIMIT. I’ll probably move to MySQL because of this. I have lots of code to write on paging and MSSQL is just horrable for this OBVIOUS task.

    What’s wrong with you guys ?

  21. Mental Wanderer says:

    Thanks for the example.  I will be using it.

    As it is, I am sympathetic with the angst in this thread: even SQLite has LIMIT and OFFSET.  Your links are not entirely unhelpful, but I would be grateful for a straightforward cookbook of SQL Server "quirk-arounds" built for folk like me who lack the motivation, time, or the chops, (or all of the above), to become M$SS gurus.

  22. Christopher Hagar says:

    He has the gall to call that inverted, ten-times-longer solution "really easy".

  23. DL says:

    I my experience with MySQL, there is a decay in performance when using LIMIT to skip over large record sets; so I wouldn’t use MySQL’s implementation of LIMIT as the gold standard.  Nevertheless, the ease of use of LIMIT is enviable.  I try, if possible, to use a BETWEEN on a primary key.  It’s faster on large record sets.

  24. Thank you for this awesome article.

    You have answered a very confusing question, in a very understanding manner…

    Thanx

  25. Yahav says:

    Thanks Dan, good to learn some new things.

    Microsoft always have to do things in their own way, never follow standards: that’s the motto, always was and always will be.

    It’s not always bad, but it surely explains why there will never be real "LIMIT" in SQL Server. 😉

  26. DHINESH BABU says:

    HEY , IT IS VERY USEFUL TO MY PROJECT AND MY KNOWLEDGE.. THANKS GUY

  27. Chris says:

    Most of you who have commented have no idea what you are talking about. There’s a reason why MS SQL is so commonly used in the corporate world. It’s stable, and very efficient. It doesn’t have LIMIT? Who cares, as pointed out, it’s fairly easy to get around. And LIMIT in MySQL would still have to go over every record anyways.

    Also, LIMIT is not a standard, last I heard. As a matter of fact, SQL:2008 has three ways of limiting results, FETCH FIRST, a Window function (ROW_NUMBER, for example), and a cursor. The OFFSET keyword is standardised in SQL:2008, however SQL Server 2005, and 2008 were both released before said standard was made. Actually, speaking of SQL standards, MySQL is terrible at following said standards. MS SQL, and Oracle for that matter, both implement standard SQL, and their own (T-SQL, and PL/SQL respectively).

    For those of you who think that MySQL is in any way better than MS SQL Server, then I honestly feel sorry for you. I know hundreds of professional developers, and while they agree that MySQL is a nice DB for home use, it’s terrible for any corporate uses. Anyways, my point is this: If you don’t know what you’re talking about, don’t say anything, you’re just going to sound dumb. Also, this isn’t a place to complain about how crappy MS products are. Grow up, seriously.

    To the author: Thanks for the info. I used this about a year ago, and it works perfectly, and very quickly on the table I was using (which, by the way, had 200,000,000+ records in it).

  28. Michael says:

    Chris:

    There’s no reason for personal attacks and it’s up to you if you want to feel sorry for people who are perfectly happy with a solution that works for them.

    Limit in MySQL doesn’t have to go through every record.  That’s kind of the point of using it, to save your database some work and improve performance.

    And it’s frustrating for users coming from other database systems, not just MySQL – PostgreSQL is matches every stability argument for MsSQL and supports limiting queries in a simple syntax.

    Not supporting a limiting clause slows down database queries, and doesn’t run as efficiently for your application.  Inefficiencies like this mean you have to have a lot more memory than your system really ought to be able to run on.

    This is a perfectly reasonable request for users to have as many other databases, (even SQLite as mentioned above) support limit.

  29. alejo jm says:

    very useful this post… but if you need ROW_NUMBER()

    inside a joined tables see this

    http://www.cto247.com/blog/post/2009/03/02/Using-Row_Number()-in-SQL-2005-When-Joining-Multiple-Tables.aspx

    PARTITION BY awesome

    thanks for this post

    very usefull

  30. In writing ORM middleware(s) to interact with multiple databases, symmetry in code helps to reduce complexity in the ware(s).

    Microsoft has the resources to provide the convenience (and easy of adaptability) that developers need when working with multiple databases, and has the responsibiity to make sure that the implementation is efficient.

    So, the SLQ Server Stratey team should implement the LIMIT clause (or an equivalent if there is an IP issue) and do so in a way that will make everyone happy.

  31. epiece says:

    This mssql is like ie6 that give problems to developers when it came to compatibility with other softwares. Hard to belive that Microsoft let us hard to adjust for thier products.

  32. epiece says:

    This mssql is like ie6 that give problems to developers when it came to compatibility with other softwares. Hard to belive that Microsoft let us hard to adjust for thier products.

  33. Brian says:

    Thanks, danwinn. I used this code and it worked perfectly for me.

    Dumb, and probably unrelated question, though – I noticed that it doesn’t work at all without the alias ("a" in the example), even though it’s not referenced anywhere else. Anyone know why this is?

  34. epiece says:

    MySql limit is useless for dynamic limit values.

    example is:

    select * from licenses

    limit @limit.

    better to create your own way to select records with limits.

    MSSQL allow dynamic Top values

    Example is:

    select top (@limit) * from licenses

  35. Pavel says:

    You can use dynamic limit values in MySql. It’s just not that straightforward:

    PREPARE stmt FROM ‘SELECT * FROM licenses LIMIT ?’;

    EXECUTE stmt USING @limit;

    Thanks to the author. As a workaround it works fairly well. Though it would be good to have more eficient solution.

  36. epiece says:

    Hi , Limit is not useful in stored procedures, You cannot assign dynamic limit range on MySQL unlike The equivalent of it in MsSQL. I created a sample of MsSQL here http://epiece.net/article/MSSQL_Limit_equivalent_to_MYSQL_limit_function_.html . You can use it as reference.

    Thanks.

    PREPARE stmt FROM ‘SELECT * FROM licenses LIMIT ?’; will not be useful. We should avoid practicing that way when we prioritize speed.

  37. Mike Fulton says:

    MySQL and MS SQL both have their good points and bad, but which is the "better" database system isn’t really the question here.  The question is the utility of the "LIMIT" clause in a SELECT query.

    My own rule of thumb has always been that the more simple the syntax, the easier it is to use and the easier it is to avoid mistakes.  This is ESPECIALLY TRUE when you’re building a query dynamically in PHP or other server-side code.  And the bottom line is that the LIMIT syntax as implemented by MySQL is more simple and straightforward than the equivalent selection syntax with Microsoft’s T-SQL.

    @DL – Any decay in performance when using LIMIT is undoubtedly due to the underlying requirements of the selection, not the specific syntax of the SQL used to make the selection.

    @Chris – The "LIMIT" syntax may not be "standard" but what difference does that really make?  There isn’t an SQL engine vendor anywhere that uses the "standard" for anything more than a starting point, including Microsoft.  

    @epiece – The way that LIMIT works (or doesn’t work) with dynamic values with MySQL doesn’t have anything whatsoever to do with how it *COULD* work with MS SQL if Microsoft added support for it.   Whatever limitations it may have with MySQL would not necessarily apply.

    Aside from that, while I haven’t tried it the way you show in your example, I do note that you’re using a reserved keyword "limit" as a variable name.  Always a bad idea even if it does not cause a syntax error.

  38. epiece says:

    @Mike Fulton  

    Thanks… I’ve just shared what I’m thinking.

    Based on my experience using MSSQL and MYSQL, LIMIT is more easier to use than that of ROW_NUMBER() since you need to define all order column. But ROW_NUMBER() is more powerfull 🙂 . Programmer love challenging syntax …

  39. rbarreto says:

    Here is another example i posted on http://epiece.net/article/MSSQL_Limit_equivalent_to_MYSQL_limit_function_.html

    declare @tbl as table(

    idx int identity(1,1),

    name varchar(10)

    )

    insert into @tbl

    SELECT name FROM sys.databases

    select * from  @tbl where idx between 10 and 20

    this may help for small resultset.

  40. Stefan says:

    I must but laugh. That is an ugly hack.

  41. Brad says:

    Don't be afraid of a few lines of simple code!

  42. Gryp says:

    I made a testing comment before this one so don't think I am a spammer cause I have never posted here before. anyway as a suggest I would say don't use limit instead use this.

    int i=0;

    Where(reader.read())

    {

     if(i == 5)

    {

    break;

    }

    }

    this would make your work easy. infact it doesn't take much time cause the read() method (in C#) read one by one record (that means read next record and so fourth) so when it reaches the i==5 condition you can break the reading loop. and close the connection. it's much easier than this tutorial.

  43. Rezor says:

    This would be easy enough to understand

    Worked on SQL Server 2005< i dunno about 2000

    WITH LIMIT AS(

    SELECT *,ROW_NUMBER() OVER(ORDER BY brandName) AS rowNum FROM brandTable)

    SELECT * FROM LIMIT WHERE 0<rowNum AND rowNum<=3;

    understand the logic first of all

    the WITH can be used to categorize the area from where you get the records

    it likes FROM  tableName

    and  in here

    SELECT *,ROW_NUMBER() OVER(ORDER BY brandName) AS rowNum

    SELECT returns both record and the row number, but remember in this time u sort out the table by brandName in Ascending order. cause Ascending is the default. it can be even Descending.

    ORDER BY just does sort out the output records

    and OVER can be used to manipulate that.

    ROW_NUMBER() function returns that sorted out records ROW NUMBER from 1 to <

    so

    SELECT * FROM LIMIT

    when u use this like that this would return two things

    first the records, the record number

    remember you use something called rowNum which is called an Alias and can be give to any statement to make it short

    AS rowNum

    obviously then you can manipulate the condition after WHERE.

    you can be even use BETWEEN 1 AND 2

    then it would be like this

    WITH LIMIT AS(

    SELECT *,ROW_NUMBER() OVER(ORDER BY brandName) AS rowNum FROM brandTable)

    SELECT * FROM LIMIT WHERE rowNum BETWEEN 0 AND 3;

    you see it's easy.!!

  44. MikeW says:

    You can use eg. SELECT TOP 5 … to return only 5 results, if you must do that.

  45. Arun Raj says:

    Try this link: http://www.arunraj.co.in/index.php

    You'll find 4 types of paging in SQL Server and a C# function which will allow you to convert any query to a paging query. You just have to pass the start and end values.

  46. Josh A says:

    This is hilarious. Thanks for making it 'easy'