Creating A Unique Id In SQL 2005

There are times when for whatever reason a unique value cannot be created on row creation or we need to create arbitrary numbers because we don’t want to conflict with old data. Here are a few scenarios that can help out. I’m sure you’ve seen this posted elsewhere, but the last part is my addition.

To generate a new GUID:

SELECT NEWID() as GuidNo

Which will generate something like: FBF2D8E9-F8BE-4F0B-9D49-7CA7C2E3F22C

To generate only numbers:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo

Which will generate something like: 427357674589

To generate a fixed number:

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

This will generate a 12 digit number. However, if you wish to generate smaller numbers say 10 or less be sure to lower the number within the binary() parameter after you cast to a smaller length. If you don’t you will generate numbers larger than the cast which will result in ***** being placed into the field you are trying to generate.