How to get n random number of records from table in Sql Server

Introduction: In this article i am going to share how to select n number of rows randomly from sql server database table.

In previous articles i explained How to generate row number/serial number without ordering by any columns and Using merge in sql server to insert, update and delete in single statement and Sql statement equivalent to ternary/conditional operator and  Get created or modified date of tables,stored procedures, views and functions and  Auto generate auto incremented alphanumeric sequential code in sql server

Description: While working with sql server database we may require selecting some records randomly every time we fetch records from the table. suppose we have employee table having 100 records and we want to select any 5 records randomly from the table in each call.  This could be done easily using sql server's inbuilt function NEWID() in order by clause in the select query.

The NEWID() function in SQL Server creates a unique value of type uniqueidentifier.  One use of the NEWID() function is in generating random rows from a table as we are going to use.

Implementation: Let's see by suitable example:

--Create employee table using below script.

CREATE TABLE  dbo.tbEmployeeMaster

(

    EmployeeId     INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

    EmployeeName   NVARCHAR(50),

    Gender         NVARCHAR(10),

    Department     NVARCHAR(50)

) 

--Add some dummy data into this table

GO

INSERT INTO dbo.tbEmployeeMaster(EmployeeName,Gender,Department)

VALUES

('Arjun','Male','Administration'),

('Rohan','Male','Sales'),

('Ishita','Female','HRM'),

('Aadi','Male','Sales'),

('Preetam','Male','HRM'),

('Anjan','Male','Administration'),

('Rajesh','Male','HRM'),

('Ankur','Male','HRM'),

('Robin','Male','Finance'),

('Mayank','Male','Sales'),

('Manisha','Female','HRM'),

('Sonam','Female','HRM'),

('Rajan','Male','HRM'),

('Kapil','Male','Sales'),

('Ritika','Female','HRM'),

('Akshay','Male','Finance'),

('Aryan','Male','HRM'),

('Anju','Female','Finance'),

('Sapna','Female','Finance'),

('Ruhi','Female','Finance'),

('Robin','Male','Sales'),

('Neelam','Female','HRM'),

('Rajni','Female','Administration'),

('Sonakshi','Female','Finance');

 GO

Check data in table

SELECT *  FROM tbEmployeeMaster

Output will be as:

EmployeeId	EmployeeName	Gender	Department
1	Arjun	Male	Administration
2	Rohan	Male	Sales
3	Ishita	Female	HRM
4	Aadi	Male	Sales
5	Preetam	Male	HRM
6	Anjan	Male	Administration
7	Rajesh	Male	HRM
8	Ankur	Male	HRM
9	Robin	Male	Finance
10	Mayank	Male	Sales
11	Manisha	Female	HRM
12	Sonam	Female	HRM
13	Rajan	Male	HRM
14	Kapil	Male	Sales
15	Ritika	Female	HRM
16	Akshay	Male	Finance
17	Aryan	Male	HRM
18	Anju	Female	Finance
19	Sapna	Female	Finance
20	Ruhi	Female	Finance
21	Robin	Male	Sales
22	Neelam	Female	HRM
23	Rajni	Female	Administration
24	Sonakshi	Female	Finance

Now lets write the query to get 5 random records on every run.

First run:

SELECT  TOP (5) *  FROM tbEmployeeMaster ORDER BY NEWID()

Output could be as:

EmployeeId	EmployeeName	Gender	Department
22	Neelam	Female	HRM
6	Anjan	Male	Administration
19	Sapna	Female	Finance
15	Ritika	Female	HRM
24	Sonakshi	Female	Finance

Second run:

SELECT  TOP (5) *  FROM tbEmployeeMaster ORDER BY NEWID()

Output could be as:

EmployeeId	EmployeeName	Gender	Department
22	Neelam	Female	HRM
2	Rohan	Male	Sales
12	Sonam	Female	HRM
15	Ritika	Female	HRM
7	Rajesh	Male	HRM

Third run:

SELECT  TOP (5) *  FROM tbEmployeeMaster ORDER BY NEWID()

Output could be as:

EmployeeId	EmployeeName	Gender	Department
7	Rajesh	Male	HRM
6	Anjan	Male	Administration
12	Sonam	Female	HRM
11	Manisha	Female	HRM
2	Rohan	Male	Sales

Conclusion: As we can see , we get random 5 records in each run. They can be same or different in each run.We can't predict which records will be fetched.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post