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),13),0,0)

generates 470370453006 on my machine

Advertisements

7 thoughts on “Generate a unique number in Sql Server 2005

  1. This technique of yours is buggy

    SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)

    It generates *********** sometimes, why?

    Thanks,
    -DS.

    1. Hi Sami,

      Can u please help to find out how it is giving *******
      because i haven’t faced such problem yet..

      if it is giving any bugs than we can find out some other solutions also

      Sandy

      1. When you change the 12 to another number as below, it gives the ***** answer

        SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),9),0,0)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s