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.
 

 

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on March 7, 2010, in Code Snippets, Interview Questions, SQL Server, T-SQL, T-SQL Questions, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged , , , , . Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: