CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldatabase-design
Fastidious
Fastidious

Reputation: 1319

Does manipulating NEWID() still make it unique?

I've read quite a few articles and SO posts on NEWID() when used for Unique ID's. I'm trying to find out if the function is good for my records when creating a composite primary key.

One's that caught my eye were the ones that manipulate NEWID() in some way to generate an Unique ID. However, the question comes to mine. When you manipulate NEWID() in the example below, is the ID still following the same principles of being unique or just random?

RIGHT('000000000' + CAST(ABS(CHECKSUM(NEWID())) % 999999999 AS VARCHAR(9)), 9)

The purpose of this is to combine the results of the above with a Customer ID in a table with duplicate Customer ID's and records that have no Customer ID's (i.e.: 0 as there ID). For example, the above code in action with the Customer ID:

New ID - 49302304954983 (Customer with 49302 ID)
New ID - 0480384348 (Customer with 0 ID)

That way I can quickly identify customers with a original ID and those with no ID. Then I can use the new composite key as a primary key that is unique to the record.

Thanks for your assistance. I still have a great deal to learn about proper database development and I'm trying to keep to good practices that are both simple and sound. Please don't hesitate to give your input on this approach to assigning a new primary key. I welcome all feedback good or bad on top of an answer to my question if possible. :)

Upvotes: 0

Views: 394

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The NEWID() will be unique, but the result will not be. The NEWID() is 36 characters. This is using the checksum, which is a value up to about 4 billion and then converting it back to a sequence of numbers.

There are way more NEWID() values than possible values for this expression. By something called the pigeonhole principle, there will be duplicated values.

Upvotes: 3

Aheho
Aheho

Reputation: 12821

You have CHECKSUM(NEWID())) in your expression.

NewID() by itself is guaranteed to be unique. However, once you take a CheckSum of it, you can no longer guarantee uniqueness.

The Checksum is a hash function. Hash functions have a low probability of collisions, but do not guarantee uniqueness.

Upvotes: 0

Related Questions