Paul White (10/26/2009)
I do agree with Mr Guru there about the ORDER BY thing, but as a matter of curiosity, I think the following also guarantees a repeatable order:
WITH TS AS (SELECT * FROM dbo.Test TABLESAMPLE (100 PERCENT) REPEATABLE (91256))SELECT TOP (10) * FROM TS;
Well, so long as no changes are made to the table anyway...:-)
I believe that even that is subject to non-repeatability since there is no explicit order by (that I could see) in the query plan. At a minimum I think the following could lead to non-repeatability:
1) anytime the engine determines it can do an allocation-order scan (such as being under READ UNCOMMITTED isolation level)
2) partitioned table
3) merry-go-round scan
4) partitioned view
5) not sure about this one, but since BOL states "Rows on individual pages of the table are not correlated with other rows on the same page." I wonder if that could affect this outcome, despite it being 100 PERCENT sample.
Actually, I just did this type of query against a table with no clustered index and the plan was a table scan with ORDERED = FALSE. I think that at least implies the potential for non-repeatability.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service