There may be occasions during testing that you want to test against a random record, the following statement will return a random record form the Cars table.
SELECT TOP 1 intID, strName FROM [dbo].[tblCars] ORDER BY NewID()
This would return the following:
intID strName ---------------------------------- 17 VW Golf
It works by sorting the records based on the NewID() value, this is a globally unique identifier value. Because NewID() changes based on the time it is called, this will change on every run and will never return a the same code twice.
You can also use the ORDER BY NewID() it to annonymise data by returning random data from random records to create a record that doesn’t actually relate to one particular record. The example below shows how to do this against a table called tblPersons.
Declare @FirstName varchar(20), @LastName varchar(20), @DoB datetime SELECT TOP 1 @FirstName = strFirstName AS [strFirstName] FROM [dbo].[tblPersons] ORDER BY NEWID() SELECT TOP 1 @LastName = strLastName AS [strLastName] FROM [dbo].[tblPersons] ORDER BY NEWID() SELECT TOP 1 @DoB = dtmDateOfBirth AS [dtmDateOfBirth] FROM [dbo].[tblPersons] ORDER BY NEWID() SELECT @FirstName, @LastName, @DoB
This would return the following:
strFirstName strLastName dtmDateOfBirth -------------------------------------------------------------- Francis Higginbotham 1956-09-12 00:00:00.000
The above example is ideal if you are working on a large dataset, over a hundred records, I wouldn’t recommend it if you have much less than this as the random factor gets less random. You could expand the above statement to include addresses, phone number, but remember that a phone number is and a post code can actually identify someone, so again I wouldn’t recommend doing this.
If you have any questions on the above or would like more examples, then please ad a comment to the post.
Taylor's Tutorials 