Return just a sample of the code
-
I'm in the process of running a very large join, and I'm not sure if this will run the way I want it to. But before I run this t-sql statement I would like to see a sample what what my code will return to me (maybe the first 10 lines). I would assume that TOP would only give me whatever percent or top 10 that I'm asking for. All I really want to do is make sure that what I have written is truly what I want? Can you please help and show some type of example or point me in the right direction for BOL.. TIA
-
select top 10
will give you the first 10 rows (and the specific rows which are returned depend on any ORDER BY clause in your query).
Eg, perhaps you want to see the 10 most recent records added to a table:
select top 10 *
from table
order by ID desc
will work if the table.ID field is an incrementing field.
select top 10 percent *
from table
order by ID desc
works in a similar way, except the number of rows returned is 10% of the total in the underlying recordset. I've found this to be a useful way of sampling large volumes of data - in conjunction with putting something (quasi) random in the ORDER BY clause.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
true - but - does that not still go out and scan the whole table or should I assume w/TOP the scan will only exe the syntax on the first ten records or 10 percent. Which would work best TOP or ROWCOUNT() thoughts...
-
Please explain!
-
BOL suggests that TOP should be used:
"It is recommended that DELETE, INSERT, and UPDATE statements currently using SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see DELETE, INSERT, or UPDATE."
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
"BOL suggests that TOP should be used:"
"It is recommended that DELETE, INSERT, and UPDATE statements currently using SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see DELETE, INSERT, or UPDATE."
SELECT is not there
So, it is safe to use rowcount for read only operations.
* Noel -
Jeez, perhaps I had one-too-many beers last night. Well spotted and I think I'll keep quiet for a while!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply