Thursday, June 4, 2009

NEWID () function in Microsoft SQL

Select random records in MS SQL bit difficult. But we can do it in easily using NEWID () function. This function selects random values in SQL data sets.

Example:
Using Customers table in the Northwind database. To randomly SELECT 5 customers from the table, we simply have to add the ORDER BY NEWID() in the SELECT statement. Since we are only concerned with 5 records, we also have to include the TOP 5 clause in the statement. The SELECT statement will look as follows:

SELECT TOP 5 [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers]
ORDER BY NEWID()


Result is as follows.

CustomerID CompanyName            ContactName                    
---------- ---------------------- ------------------------------ 
WANDK      Die Wandernde Kuh      Rita Müller
BERGS      Berglunds snabbköp     Christina Berglund
ROMEY      Romero y tomillo       Alejandra Camino
OTTIK      Ottilies Käseladen     Henriette Pfalzheim
AROUT      Around the Horn        Thomas Hardy


Then we execute the same query again the result is different. That means random result.

CustomerID CompanyName ContactName
---------- ------------------------ ------------------------------ 
SIMOB      Simons bistro            Jytte Petersen
QUEDE      Que Delícia              Bernardo Batista
RICAR      Ricardo Adocicados       Janete Limeira
VINET      Vins et alcools          Paul Henriot
LAMAI      La maison d'Asie         Annette Roulet


No comments:

Post a Comment