Results 1 to 8 of 8
  1. #1
    Join Date
    May 2001
    Posts
    51

    Unanswered: Impliment MySQL LIMIT command on SQL Server

    Hi there, it seems the search feature refuses to work so i am posting a question which I am sure has been answered

    What is the best way to get the LIMIT command functionality in SQL SERVER?

    I am making an ASP.NET page, and am unsure how to go about tabbing through 100s of results.

    Many thanks,
    Taz

  2. #2
    Join Date
    Feb 2006
    Posts
    8
    Sql Server does not have a LIMIT functionality. You can either use TOP to only select the TOP n records, or implement your own paging.

  3. #3
    Join Date
    Feb 2006
    Posts
    8
    BTW: In .NET, you can implement paging through a .NET control. Performance isn't as good as building your own paging, but if you don't want to go through the trouble, it's worth looking into.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use SQL Server 2005? The LIMIT clause shouldn't be implimented in a relational database, even though it is quite convenient. In SQL 2005, Microsoft chose to implement this functionality due to user demand in spite of the fact that it limits the design of the database engine in other ways.

    -PatP

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    the SQL server equivalent of MySql LIMIT is TOP

    MySQL
    select * from MyTable LIMIT 10

    SQL Server
    select TOP 10 * from MyTable

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by upalsen
    the SQL server equivalent of MySql LIMIT is TOP
    So, where does the comma go?

    While you are correct that the TOP predicate implements part of what LIMIT does in MySQL (i.e. the first parameter), it does not handle the paging portion (when there are two parameters). Paging result sets has no set based solution (because it is both stateful and sequence dependant), so it really should be implemented via an N-tier solution.

    -PatP

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Right Pat. TOP is only a closest equivalent in Sql2K and having Limitations compared to MySql LIMIT.

  8. #8
    Join Date
    Jan 2006
    Posts
    27
    Please remember that both limit and top are dangerous if not paired with some order by's. Without that control, a relational DB is free to return records in any order it feels like.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •