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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

4 comments:

Anonymous 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?