Skip to content

NEWID System Function

The NEWID() function in SQL Server creates a unique value of type uniqueidentifier. One use of the NEWID() function is in generating random rows from a table.

SELECT TOP 20 [LottoNumber]
FROM [dbo].[LottoParticipants]
ORDER BY NEWID()

Another use of the NEWID() function is in generating random numbers. The uniqueidentifier generated by the NEWID() can be converted to VARBINARY which in turn can be converted to an integer number. The random number generated will include negative numbers. If only positive numbers are desired, simply get the absolute number of the result:

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

If there’s a maximum number required for the random number generated, simply use the MODULO operator to limit the numbers generated. The following query generates a random number between 1 and 100:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1 AS [RandomNumber]