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. Pingback: Random in SQL - SQL For Programmers
  2. 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.

    • 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

      • 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