SQL Server Random Sorted Result Set

By: Dallas Snider   |   Comments (3)   |   Related: > TSQL

Problem

How can I write a T-SQL query that will return rows sorted randomly?  Are there any SQL Server System Functions that are available or do I need to write custom code?  Check out this tip to learn more.

Solution

In this tip, we will show two ways to write a T-SQL SELECT query that returns rows that are sorted by a random number.

Let's begin by creating a table using the T-SQL CREATE TABLE statement below.

USE MSSQLTips
GO

create table TestData 
(
  pkID int identity(1,1) PRIMARY KEY,
  testNumber int not null,
  testCharacter varchar(1) not null
)

Next, we will populate our table with the T-SQL below.

insert into TestData values ( 100, 'a')
insert into TestData values ( 200, 'b')
insert into TestData values ( 300, 'c')
insert into TestData values ( 400, 'd')
insert into TestData values ( 500, 'e')
insert into TestData values ( 600, 'f')
insert into TestData values ( 700, 'g')
insert into TestData values ( 800, 'h')
insert into TestData values ( 900, 'i')
insert into TestData values (1000, 'j')

After inserting the records, let's review the data to make sure it is correct.

SELECT * FROM TestData

Our first instinct might be to use the RAND() function without a seed value as a column or in the ORDER BY clause. As we see below, the unseeded RAND() function does not produce the desired results. The data returned by the query is still ordered by the primary key. Using the unseeded RAND() function as a column in the SELECT statement returns the same value on every row as shown in the SortOrder column.

SELECT RAND() as SortOrder, * 
FROM TestData 
ORDER BY SortOrder 

SELECT * 
FROM TestData 
ORDER BY RAND()

To solve this problem, we need to pass a changing seed value to the RAND() function for every row to ensure that new random numbers are generated for each row. Furthermore, the seed value must be an integer expression. The NEWID() function will return a unique identifier for each row, but the data type returned is unique identifier which is not an integer expression. This will result in the error we see in the next image.

SELECT RAND(NEWID()) as SortOrder, * 
FROM TestData 
ORDER BY SortOrder

To eliminate the data type incompatibility, we can pass the NEWID() value to the CHECKSUM() function. The CHECKSUM() function returns an integer data type, which can be passed to the RAND() function. We can nest these functions together to simplify our T-SQL code. In the next image, we can see the results when we use the seeded RAND() function as a column value and then ordering by this value.

SELECT RAND(CHECKSUM(NEWID())) as SortOrder, * 
FROM TestData 
ORDER BY SortOrder

The example above works well if the random value column is required to be present in the rows returned by the query. There is a second approach we can take for returning rows that are sorted by a random value when we don't need the random value in our result set. This approach uses the NEWID() function alone in the ORDER BY clause as shown below.

SELECT * 
FROM TestData 
ORDER BY NEWID()

SELECT TOP 6 * 
FROM TestData 
ORDER BY NEWID()

Next Steps

Notice that each time the query is called, the order of the returned rows changes. We can also use this type of query to get a random sampling of our data by specifying the TOP number of rows.

Check out these other tips and tutorials on T-SQL and the RAND() function on MSSQLTips.com:

• Different ways to get random data for SQL Server data sampling
• Create Your Own RANDBETWEEN Function in T-SQL
• Generating Random Numbers in SQL Server Without Collisions
• Our complete tutorial list

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips