How To Return Random Records From A Table

Let us see how to return random 'n' records from a table

-- SAMPLE SCRIPT

CREATE TABLE #Customers (id integer, cname varchar(20), pincode int)

-- INSERT SAMPLE RECORDS

INSERT INTO #Customers VALUES (1, 'Jack',45454 )
INSERT INTO #Customers VALUES (2, 'Jill', 43453)
INSERT INTO #Customers VALUES (3, 'Tom', 43453)
INSERT INTO #Customers VALUES (4, 'Kathy', 223434)
INSERT INTO #Customers VALUES (5, 'David', 65443)
INSERT INTO #Customers VALUES (6, 'Kathy', 456556)
INSERT INTO #Customers VALUES (7, 'Kim', 65443)

-- Return 3 random records from the #Customers table

SELECT TOP 3 cname,pincode FROM #Customers ORDER BY NEWID()

So what happens over here is that the NEWID() is used to generate a unique value of type uniqueidentifier. So ordering the results by this ID every time gives us random rows. Cool!!