SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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()

sort records in random order in SQL Server using NEWID() function

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()

select 10 random rows in SQL Server

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()

sort rows in random order in SQL Server

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems