Other than the example given in the answer, where the set rowcount is turned on, what would be the situation where the top 1000 in a 10000 row table would not be returned? If I understand correctly, Microsoft has allowed someone to set rowcount and has no way to turn it off other than set rowcount off?

Thus using the familiar method, to guarantee 1000 records the statement would always need to be

set rowcount 0

go

select top 1000 col1 from mytable

Yes? Somehow this seems convoluted. I suspect we need to learn the tablesample method and forget the word TOP exists? Why does this seem convoluted?

Gotta say I learned something else with this question as well as the above. I've been having trouble using order by in a sub-query. Apparently, the set rowcount will allow me to order the sub-query prior to grabbing it. Not sure how much it helps as it is hard to know how many rows you will grab before you grab them.