This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.
Sometimes you want the returned rows of your query to be randomized. That could be when generating test data.
The trick is to use
ORDER BY NEWID(). The
NEWID() function will generate a new random guid for each row and when sorted the rows will be randomized.
This is not something you should put into your production code unless you have no other way of randomizing you rows, because Sql Server has no way of optimizing this kind of query so if the trick is used on a large table it will take time and computing resources.
DECLARE @names TABLE ([Name] VARCHAR(50)) INSERT INTO @names (Name) VALUES ('Joe'), ('Bob'), ('Anne'), ('Jane') SELECT Name FROM @names ORDER BY NEWID() -- By ordering by NEWID() the rows will be randomized