Selecting random row in a small table in sql server can be achived by NEWID() function with much affect in performance.
SELECT TOP 10 * FROM Table ORDER BY NEWID()
NEWID() generates a globally unique identifier (GUID) in memory for each row. so, when we sort by that GUID with the ORDER BY clause random row are being selected.
But when it comes to large tables where million of records are stored selecting random records for sampling affect performance very much.
Best alternative way is of using "BINARY_CHECKSUM" function.
SELECT TOP 10 * FROM Table WHERE (ABS(CAST((BINARY_CHECKSUM(*) *RAND()) as int)) % 100) < 10
The BINARY_CHECKSUM function generates a checksum value that is based on the values of the columns that you specify.
If two rows are different, they typically will generate different checksum numbers.
The BINARY_CHECKSUM function generally is used to verify whether any of the columns in a row in a table have changed.
There-by when we added the RAND function to the BINARY_CHECKSUM(*) function would generate a random number for each rows as column values for each row would vastly differ.
(ABS(CAST((BINARY_CHECKSUM(*) *RAND()) as int)) % 100) would generate a random number between 0 and 99 for each row in the table and then choose all of those rows whose random number is less than the value of the specified percent (in this case, it is 10)
