Generate a unique number in Sql Server 2005
Posted by ramanisandeep on October 14, 2008
We often find ourselves in the need of generating unique numbers in our database applications.
Let us quickly take a tour of how to do that using Sql server 2005.
SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :
Generate GUID’s :
UniqueIndentifiers are also knows as GUID’s. To generate a GUID use :
SELECT NEWID() as GuidNo
generates FBF2D8E9-F8BE-4F0B-9D49-7CA7C2E3F22C on my machine
Generate only digits :
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo
generates 427357674589 on my machine
Generate fixed digit unique numbers :
At times, we may also need to generate fixed digit numbers. You can do that in the following manner :
SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)
generates 470370453006 on my machine
Random in SQL - SQL For Programmers said
[...] Generate a unique number in Sql Server 2005 – Let us quickly take a tour of how to do that using Sql server 2005. SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique … [...]
Mehaboob Basha said
it’s greate idea!!! using NewID() and ABS cast.
It helps me lot.. thank you very much.