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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

4 Responses to "Generate a unique number in Sql Server 2005"
  1. tchr said...
    August 14, 2008 at 4:31 AM

    Thanx for help

  2. Anonymous said...
    February 5, 2009 at 3:37 PM

    http://wunumber.org

  3. Anonymous said...
    July 28, 2009 at 9:58 AM

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

  4. nellyihu said...
    November 15, 2009 at 9:15 AM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions