Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the NEWID Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

 

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]

Back to Tip of the Day List Next Tip