Is the column with uniqueidentifier data type unique?

Question

Is the column with uniqueidentifier data type unique?

m--S3qU3L

Hall of Fame

Points: 3706
More actions
April 5, 2010 at 10:36 am

#143908

Hi all,
Is the column with uniqueidentifier data type unique?
CREATE TABLE TestTable7(
col1 int,
col2 UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID())
INSERT INTO TestTable7 VALUES (13,'21903FF9-CD40-DF11-95A7-00248C11F753')
INSERT INTO TestTable7 VALUES (13,'21903FF9-CD40-DF11-95A7-00248C11F753')
INSERT INTO TestTable7 VALUES (13,'21903FF9-CD40-DF11-95A7-00248C11F753')
INSERT INTO TestTable7 VALUES (13,'21903FF9-CD40-DF11-95A7-00248C11F753')
I could insert same value more than one time.
So, my question is, if uniqueidentifier column is not unique, then how tables with uniquidentifier columns can be working in merge replication.
In merge replication, it compares the row from 2 tables taking part in replication with the help of this unique id and makes sure that both rows are equal in the 2 tables.
If more than 1 row in a table can have same ID as above, then how the comparing works.
Any idea?
Thanks
John

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

The column that contains the unique identifier datatype does not enforce uniqueness. Just like an identity column, you need to put a constraint on it in the form of a PK or Unique Index.

NEWID() however, is both unique and quite random.

--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)

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 2

So, does this mean NEWSEQUENTIALID() cannot be used in tables undertaking in merge replication?

If so, we have to enforce the unique constraint explicitly for the proper working of merge replication?

John

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 3

Please ignore this post. Misunderstood.

---------------------------------------------

In fact, NEWID() is also not throwing error while doing the multiple inserts of the same record

CREATE TABLE TestTable8(

col1 int,

col2 UNIQUEIDENTIFIER DEFAULT NEWID())

INSERT INTO TestTable8 VALUES (13,'2A756065-4822-4C91-BE94-E08FB553544D')

select * from TestTable8

I think this is also not unique by default.

--------------------------------------------------------------

Thanks

John

Paul White SSC Guru Points: 150468 More actions · Answer 4

Creating a column of type UNIQUEIDENTIFIER just defines the data type of values that can be stored in it. It does not enforce uniqueness.

NEWID and NEWSEQUENTIALID generate values of the UNIQUEIDENTIFIER type. Technically, neither of these functions are guaranteed to never produce a duplicate - but it is incredibly unlikely.

If you want to guarantee uniqueness of values stored in a column, you must always create a unique index (which includes unique constraints of course) on that column.

NEWSEQUENTIALID can only be used as a DEFAULT constraint on a column - you cannot call it directly to generate a value. It was created to make merge replication (and a variant of transactional replication) more efficient. So, it is designed to be used with merge replication.

With merge replication, you have a choice: either create a column of the UNIQUEIDENTIFIER type, mark it with the ROWGUIDCOL property, create a unique index on that column, and create a DEFAULT constraint to provide the GUID value; OR merge replication will do it for you when you publish the table.

If you specify that only 2005 and 2008 servers will use the article, merge replication uses NEWSEQUENTIALID for performance - otherwise it uses NEWID, for compatibility.

Personally, I prefer to do it myself. An example of a table and index definition that meets the requirements for merge replication follows:

CREATE TABLE dbo.Article

(

col1 INTEGER NOT NULL,

merge_rowguid UNIQUEIDENTIFIER

ROWGUIDCOL

UNIQUE

DEFAULT NEWSEQUENTIALID()

);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 5

m--S3qU3L

Hall of Fame

Points: 3706

April 5, 2010 at 12:07 pm

#1145922

Many thanks for that guys..

John

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 6

Paul White NZ (4/5/2010)
Technically, neither of these functions are guaranteed to never produce a duplicate

Actually, they are. From Books Online:

If an application must generate an identifier column that [font="Arial Black"]is unique across the entire database, or every database on every networked computer in the world[/font], use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function.

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 7

Jeff Moden (4/5/2010)
Paul White NZ (4/5/2010)
Technically, neither of these functions are guaranteed to never produce a duplicate
Actually, they are. From Books Online:
If an application must generate an identifier column that [font="Arial Black"]is unique across the entire database, or every database on every networked computer in the world[/font], use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function.

They're really not absolutely guaranteed never to produce a duplicate...

The Windows API calls used by SQL Server (UuidCreateSequential and UuidCreate) can both return RPC_S_UUID_LOCAL_ONLY, indicating the the value is only unique to the local machine.

Even if the return code is RPC_S_OK, there is still that vanishingly small chance of a duplicate. UuidCreateSequential also uses version 1 of the standard algorithm, so there is a potential issue with MAC address uniqueness. UuidCreate uses version 4, which does not depend on the machine having a network card.

Globally Unique Identifier

"The total number of unique keys (2¹²⁸ or 3.4×10³⁸) is so large that the probability of the same number being generated twice is extremely small, and certain techniques have been developed to help ensure that numbers are not duplicated."

The probability is mind-bendingly small, but it exists. A unique index is always required to guarantee uniqueness.

Paul

edit: superscripts

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 8

Thanks for the links, Paul. From what I've read in those links, it looks like SQL Server is using type 4, the random based values.

The confusing part here is that you quote in blue, above, seems to indicate that steps have indeed been taken to ensure they're somehow unique worldwide. First, I wonder how the hell they do that (haven't read that link yet) and second, doesn't that contradict what you said? Could it be that they've actually implemented that safe guard to make what is stated in Books Online true for all instances of SQL Server?

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 9

Jeff Moden (4/6/2010)
Thanks for the links, Paul. From what I've read in those links, it looks like SQL Server is using type 4, the random based values.

NEWID uses version 4; NEWSEQUENTIALID uses version 1.

NEWID calls UuidCreate - NEWSEQUENTIALID calls UuidCreateSequential.

I did try to make that distinction in my last post, but obviously failed 😀

The confusing part here is that you quote in blue, above, seems to indicate that steps have indeed been taken to ensure they're somehow unique worldwide. First, I wonder how the hell they do that (haven't read that link yet) and second, doesn't that contradict what you said? Could it be that they've actually implemented that safe guard to make what is stated in Books Online true for all instances of SQL Server?

I have emphasised the phrase 'to ensure' above, to contrast with 'to help ensure' as in the quote 😛

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi