July 12, 2019 at 6:38 pm
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.
July 12, 2019 at 6:56 pm
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
July 12, 2019 at 7:09 pm
You cold always try TABLESAMPLE
SELECT *
FROM SchemaName.TableName TABLESAMPLE (100000 ROWS)
July 12, 2019 at 7:20 pm
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
July 12, 2019 at 8:13 pm
SET ROWCOUNT 10
SELECT ...
SET ROWCOUNT 0
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 12, 2019 at 10:19 pm
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.
July 14, 2019 at 3:13 pm
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...).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2019 at 1:03 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply