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.