Query to Select a Random Record from a Table in SQL Server

Here is a quick and easy to way to select a random records from a table using T-SQL.

Create a Temp Table using the following query:

CREATE TABLE #TempTable (
IntValue INT NOT NULL,
StrValue NVARCHAR(20) NOT NULL)
GO
Now insert some sample data into the Temp Table using the following query:

INSERT INTO #TempTable (IntValue,StrValue)
SELECT IntValue, StrValue
FROM (SELECT 1 AS IntValue, 'String Value 1' AS StrValue
UNION ALL
SELECT 2 AS IntValue, 'String Value 2' AS StrValue
UNION ALL
SELECT 3 AS IntValue, 'String Value 3' AS StrValue
UNION ALL
SELECT 4 AS IntValue, 'String Value 4' AS StrValue
UNION ALL
SELECT 5 AS IntValue, 'String Value 5' AS StrValue
UNION ALL
SELECT 6 AS IntValue, 'String Value 6' AS StrValue
UNION ALL
SELECT 7 AS IntValue, 'String Value 7' AS StrValue
UNION ALL
SELECT 8 AS IntValue, 'String Value 8' AS StrValue
UNION ALL
SELECT 9 AS IntValue, 'String Value 9' AS StrValue
UNION ALL
SELECT 10 AS IntValue, 'String Value 10' AS StrValue) StaticData
GO

Now run the following queries to see how 2 RANDOM rows are selected from Temp Table every time you run these queries.

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

Here is the output of the above queries. Run these queries a couple times to see the difference in the number of selected rows every time the above queries are run.

Reference: Dattatrey Sindol (http://mytechnobook.blogspot.com/2010/03/query-to-select-random-record-from.html)

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .