Select random records in SQL Server 2008

We often need to return few random records from our table, we may use it for online test for any subject and it will return random question set for each user giving exam concurrently. There is lot more use than just test but online examination is just an example.

There are quite a few ways to do so but I have one easy way to do so. Let us see how to do it.

--creating demo table

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

--INSERT records

INSERT INTO emps

SELECT 'Ritesh','MIS','echem' UNION ALL

SELECT 'Bihag', 'MIS', 'CT' UNION ALL

SELECT 'Rajan', 'account','Marwadi' UNION ALL

SELECT 'Alka','account','tata' UNION ALL

SELECT 'Alpesh','Chemical','echem'

GO

--select top 2 random row by NEWID() function.

Select Top 2 * from emps order by NEWID()

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com