December 20, 2007

Generate a unique number in Sql Server 2005

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


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

4 comments:

tchr said...

Thanx for help

Anonymous said...

http://wunumber.org

Anonymous said...

Is conversion from NEWID to BGINT generate unique number,
Can we use it in real time?

nellyihu said...

after a while, it just generates "********" why does this happen?