(SQL SERVER) WHAT IS NEWID()? USE OF NEWID()?

SQL NEWID() is a T-SQL function that returns type “uniqueidentifier”. It is very useful in generating random numbers and random records from a sql table.

To generate random numbers,

DECLARE @myid uniqueidentifier

SET @myid = NEWID()

PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

OUTPUT

Value of @myid is: E05F972A-51E3-41AF-AC08-C8009FC0BF5F

The above sql statement results in a random uniqueidentifier value whereas the sql statement,

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [NewID]

OUTPUT

NEWID

1737359308

Results in an integer value (note that the values will be different everytime the query is executed). To generate values with the desired no. of digits, we can use the RAND() function with the NEWID() as the seed parameter.

SELECT CAST(RAND(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))) * 100 AS INT) AS [Rand]

OUTPUT

Rand

33

To generate random records,

Random records can be retrieved from a sql table by including the NEWID() function in the ORDER BY clause in the select statement.

SELECT * FROM TABLENAME ORDER BY NEWID()

To make use of the function further, we can retrieve desired number of random records by including the TOP clause.

SELECT TOP 5 * FROM TABLENAME ORDER BY NEWID()