quote:

---

B. Selecting a percentage of rows with a seed value
The following statement returns the same set of rows every time that it is executed. The seed value of 205 was chosen arbitrarily.

Copy Code
USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;

---

quote:

---

Important:
The TABLESAMPLE SYSTEM clause should be used with some caution, and with some understanding of some of the implications of using sampling. For example, a join of two tables is likely to return a match for each row in both tables; however, if TABLESAMPLE SYSTEM is specified for either of the two tables, some rows returned from the unsampled table are unlikely to have a matching row in the sampled table. This behavior might lead you to suspect that a data consistency problem exists in the underlying tables, when the data is actually valid. Similarly, if TABLESAMPLE SYSTEM is specified for both tables that are joined, the perceived problem could be even worse.

---

quote:

---

Important:
If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE. For example, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table:

SELECT * FROM Sales.SalesOrderDetail

WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)

/ CAST (0x7fffffff AS int)

The SalesOrderID column is included in the CHECKSUM expression so that NEWID() evaluates once per row to achieve sampling on a per-row basis. The expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 1.

---

quote:

---

Originally posted by Microsoft

SELECT * FROM Sales.SalesOrderDetail 
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)

SELECT * FROM Sales.SalesOrderDetail 
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
/*	
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(1249 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 1238, physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
*/

---------------------------------------------------------------------------------------------------------
SELECT TOP 1 PERCENT * 
FROM	Sales.SalesOrderDetail 
ORDER BY NEWID()
/*
SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 148 ms.

(1214 row(s) affected)
Table 'SalesOrderDetail'. Scan count 3, logical reads 1356, physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 346929, 
physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/