NEWID() structure

Question

Post reply

NEWID() structure

Carl B.

SSCertifiable

Points: 7931
More actions
October 12, 2007 at 9:38 am

#182650

Hello everyone,

Here is my need:

I have unique constraint on each table in our application. This unique constraint is on a column of datatype binary(8).

In the application there is a complex function that calculate this hexadecimal value and ensure it is unique.

The problem is that sometimes we want to generate this number within a conversion script using Transact-SQL code.

Since our column have only 16 hexadecimal numbers (binary(8)) and looks like this:

0x0013020061675035

What I would try is to use the NEWID() function to get our value.

From what I read about NEWID(), the value generated is derived from the network card identification number and from a unique number from the CPU clock.

A NEWID() value look like this:

6A3321CF-A237-469F-8EE2-936E7E9770AE

It is 32 hexadecimal numbers.

Is there a way I could take only a substring (16 from the 32 hexadecimal numbers) that would minimize the risk of collisions putting these manipulated values into our binary(8) primary key column?

Regards.

Carl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

NEWID() won't guarantee it... it's only guaranteed at the full 32 characters.

What's wrong with this?

SELECT CAST(CAST(0x0013020061675035 AS BIGINT)+1 AS VARBINARY)

If you find the largest item in the column, just add 1 to it. BIGINT is a must for this size number.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Hello Jeff,

What I was looking for is if someone was aware of which part of the NEWID() value is the result of the CPU clock and which part is the result of the network card identification number.

But as you wrote, the evidence is : If we want to use NEWID() values for our binary(8) column we must first modify at least the length of our primary keys - to ensure uniqueness of these values.

Best regards,

Carl

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Correct... and it's not a good idea to base things on the system clock... SQL Server only has a resolution of 3.3 milliseconds... a lot can happen in that small bit of time...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Carl - my understanding is that it's some more complex version of one being multiplied by the other. so - both factors are "spread out throughout the NEWID()".

So - you won't have a MS guarantee of uniqueness - but it's a fairly strong likelihood (1 out of 2^32 isn't too shabby).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Carl B. SSCertifiable Points: 7931 More actions · Answer 5

Carl B.

SSCertifiable

Points: 7931

October 15, 2007 at 11:28 am

#742079

Hello Matt,

thanks a lot for these precisions.

Carl