Miles Neale (9/19/2012)
Thus can you depend on it to be consistent? If in fact "The query will return zero or one row" how can it be consistent? And lack of consistency then will erode the confidence in a product.
The TABLESAMPLE clause is intended (and documented) to return a semi-random* sampling of the rows in the data. If a query that is intended to return a semi-random result returns consistently the same, something is wrong. For this kind of functionality (which includes the RAND function as well), inconsistent results SHOULD be returned.
* I use the term "semi-random" because there is some random factor involved, but it is not a true, statistically sound random sampling algorithm.
One of the catches you must be aware of when using TABLESAMPLE (and this catch is explicitly documented, so it does not in any way impact my confidence) is that TABLESAMPLE works on a page by page base. So if you for instance have a table with 40 rows, evenly divided over 2 pages, using TABLESAMPLE with nothing else to limit the results and with any percentage (except 0 and 100) will result in 0, 20, or 40 rows being returned, but never 15, 31, or whatever other number.