Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2012 New Spatial Instantiable Data Types
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