Need to return X rows but do NOT need them ordered: alternatives to TOP(X)?

  • Maxer

    SSCrazy Eights

    Points: 8948

    Are there any MORE EFFICIENT alternatives to using SELECT TOP(X) FROM TABLE_NAME?

     

    I don't need them ordered, I just want to get 100 or 1000 or whatever rows back.

    There was discussion about using rowcount or offset fetch but I could not find anything saying that TOP was LESS performant in those scenarios.  *Other than of course paging through rows of result sets where you wanted to get the next X after the first 500 or whatever.

     

     

  • jonathan.crawford

    SSCertifiable

    Points: 6346

    SELECT TOP is not ordered, it's just limiting the number of results. If you have an ORDER BY clause, that's ordering. What are you trying to accomplish, that you don't care what you get as long as you know how many you get?

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • DesNorton

    SSC-Insane

    Points: 22542

    You cold always try TABLESAMPLE

    SELECT *
    FROM SchemaName.TableName TABLESAMPLE (100000 ROWS)
  • jonathan.crawford

    SSCertifiable

    Points: 6346

    Not suggesting Des is wrong, but something to take into consideration:

    To use TABLESAMPLE, this clause is put after the FROM clause as follows:

    ...FROM tableName TABLESAMPLE (10 PERCENT)

    ...FROM tableName TABLESAMPLE (1000 ROWS)

    You would think that the 1000 ROWS option would return 1000 rows, but what happens is that this number is converted to a percent prior to execution based on the number you specified and the approximate number of rows in the table.  So once again you are not guaranteed an exact number of rows.

    from https://www.mssqltips.com/sqlservertip/1308/retrieving-random-data-from-sql-server-with-tablesample/

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Phil Parkin

    SSC Guru

    Points: 243474

    SET ROWCOUNT 10

    SELECT ...

    SET ROWCOUNT 0

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Maxer

    SSCrazy Eights

    Points: 8948

    Mostly just talking with other TSQL folks :). We had a friendly water-cooler discussion about how to "best" look at the contents of a table.

     

    Not to get a proper random sampling or data distribution just right click a table and do SELECT TOP 1000 from SSMS.

    In our case we don't need any specific rows just some rows to look at.

     

    However, some tables are of course very large so to do SELECT TOP 1000 FROM VERY-LARGE-TABLE with an ORDER BY statement would be resource intensive.

     

    The discussion was that omitting ORDER BY would allow the database to return "whatever" 1000 rows it has and not require an expensive sort operator.

     

    Additionally, using options such as SET ROW COUNT are session wide and prone to unintentional mistakes if it is left active.  Additionally, if you are just playing in SSMS to look at tables you cant write additional SELECT statements without writing additional SET ROW COUNT statements to keep adjusting how many rows return.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88020

    SSMS already has an option to right-click and SELECT TOP 1000 (which is configurable).  As stated earlier - TOP does not utilize a sort unless there is an ORDER BY...

    SQL Server is going to use the best index for the query - and since you are looking at all columns it will use a clustered index scan.  That is about as efficient as it comes...the only way to be more efficient is to only return columns available in a non-clustered index so you get a scan of that index only.

    Anything else is going to have additional operations (sorts, filter, etc...).

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Phil Parkin

    SSC Guru

    Points: 243474

    Maxer wrote:

    Additionally, using options such as SET ROW COUNT are session wide and prone to unintentional mistakes if it is left active.  Additionally, if you are just playing in SSMS to look at tables you cant write additional SELECT statements without writing additional SET ROW COUNT statements to keep adjusting how many rows return.

    None of this detail was suggested by your original post.

    Perhaps, in future, you could take the time to state your requirements in detail up front, to avoid people wasting their time suggesting solutions that are inappropriate.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply