:::: MENU ::::

Select random records from a table by using newID()

  • Apr 23 / 2009
  • 0
dbDigger, System Functions, T-SQL Interview Questions, T-SQL Scripts

Select random records from a table by using newID()

Normally SELECT TOP 1000 type approach is used while retrieving some sample data from a table with large number of records. But records retrieved through this approach are not from all over the table, so they can not be considered a random sample of data from that table. A simple approach to get sample data from all over the table will be to use newID() in following syntax

USE AdventureWorks
GO

SELECT TOP 100 FirstName, LastName, EmailAddress, Phone
FROM Person.Contact
ORDER BY NEWID()
GO

Above script will generate a random sample of data from Person.Contact table. You may also use where clause to further filter the records if required.
Also i have noted that using order by NewID() costs same as using order by for any other column. However the difference is there in case of clustered index. Order by any clustered index column is more economical than order by NewID().
Above was one of many uses of NewID(). We will look at other uses of NewID in some coming posts later.

Consult us to explore the Databases. Contact us