How to randomize a result set in SQL Server

In today’s very short blog posting I want to show you a simple way to randomize a result set from SQL Server. Imagine you have a task table, and you want to hand out tasks in a random fashion to your employees. The easiest way to randomize the result set is to perform an ORDER BY NEWID().

SELECT * FROM Person.Person
ORDER BY NEWID()
GO

This will introduce a new column of the UNIQUEIDENTIFIER data type and SQL Server will perform a physical sort operation on that column.

Sorting on a UNIQUEIDENTIFIER value

But the column itself is not actually returned in the result, because the ORDER BY clause happens logically after the SELECT part of the query. Therefore this approach will also not change the shape of your result set.

A very simple but still a very powerful way to randomize result sets in SQL Server 🙂

Thanks for your time,

-Klaus

Leave a Comment

Your email address will not be published. Required fields are marked *