Ramani Sandeep – ASP.NET

Help for .NET Developer

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

October 14, 2008 - Posted by ramanisandeep | SQL Server | , , | 2 Comments

2 Comments »

  1. [...] 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 … [...]

    Pingback by Random in SQL - SQL For Programmers | November 4, 2008 | Reply

  2. it’s greate idea!!! using NewID() and ABS cast.
    It helps me lot.. thank you very much.

    Comment by Mehaboob Basha | November 6, 2008 | Reply


Leave a comment

« Previous | Next »