Sort Records in Random order using SQL NEWID() Function in SQL Server
SQL NEWID() function can be used to sort records in random order in SQL Server. SQL Server NewId() generates a random GUID or unique identifier which can be used to return randomized rows from a SELECT query.
T-SQL developers will realize that the return list of a SQL SELECT query is sorted randomly when they place "NEWID() function in the "ORDER BY" clause of the SELECT statement. Using NEWID() function in Order By clause in SQL statement is easiest way to provide SQL Order By Random sorting among return result set.
I've created sql sample queries on AdventureWorks sample database to demonstrate random sorting in SQL Server 2008 R2. SQL programmers and database administrators can execute the following SELECT statement to sort records in random order as an example query.
SELECT
FirstName + ' ' + LastName AS Employee
FROM Person.Person ORDER BY NEWID()
If you want to select 10 random records from a table or sql view, the above NEWID() random order query can be used with TOP n clause as follows:
SELECT TOP 10 Name
FROM Production.Product ORDER BY NEWID()
If you wonder and add the NEWID() in the SELECT field list, you may expect to see the NEWID values sorted in order. But since each time SQL NewID() function is called, a new random uniqueidentifier is generated, the random values are different in the select list than the random values in Order By clause.
SELECT
NAME,
NEWID() [NEWID Random Value]
FROM Production.Location ORDER BY NEWID()
If your requirements for a real Random generator, you can use RAND() function or CHECKSUM() function in combination with NEWID() function instead of NEWID() which uses a standard algorythm for guid generation. The following complex sql codes will definetely result in a better randomized sort order in SQL Server database.
SELECT FirstName, LastName
FROM Person.Person
ORDER BY RAND(CONVERT(varbinary(4), NEWID()))
-- or
SELECT FirstName, LastName
FROM Person.Person
ORDER BY CHECKSUM(NEWID())
For related SQL tutorials and topics, please refer to Use SQL NEWID in SQL Functions as SQL Random Generator or sql tutorial SELECT TOP Random n Rows From a Table For Each Category