SQL Server Random Sorted Result Set

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


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.


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.


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.


Review the data

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 

FROM TestData 

Undesired query results

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

Incompatible data type error

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.

FROM TestData 
ORDER BY SortOrder

Correct query results using RAND() as a column value

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.

FROM TestData 

FROM TestData 

Correct query results using NEWID() in the ORDER BY clause
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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dallas Snider 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

Comments For This Article

Tuesday, March 24, 2015 - 5:03:31 PM - George Back To Top (36670)

I recently came across an example using CAST(NEWID() AS VarBinary), instead of CHECKSUM(NEWID())


Which method do you think would typically be more efficient?


Excellent tip, thank you.

Tuesday, March 10, 2015 - 4:30:49 AM - Satnam Singh Back To Top (36475)

Great Article, very well appreciated.



Thursday, February 26, 2015 - 4:10:07 AM - Hardik Back To Top (36355)

Very help full article for all SQL Developer.



get free sql tips
agree to terms