SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unique Identifier: Usage and Limitations


Unique Identifier: Usage and Limitations

Author
Message
sxnandwa
sxnandwa
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 2
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum



EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13147 Visits: 3893

Are there any cirsumstances where 2 GUIDS could be created with the same value?

If one of the factors in GUID creation is time, then the granularity of the time factor received by the GUID generation process will depend on the hardware involved. The time returned by the clock processor is often at a coarser granularity than the CPU speed. This is further compounded when multiple CPUs exist in a single server. The outcome of this is that many records could be created in the database that have the same clock processor timestamp.

How does the GUID creation process cope with this?

The other main aspect with GUID creation is network address. There is another potential problem here, as not every network card has a unique network address. This issue probably will not affect server hardware, as reputable manufacturers ensure their network cards have a unique address. However, for low level machines that may easily be remote clients, the situation could be more problematic. If you have multiple cheap desktops or PDAs that have the same MAC address, it is possible that two records could be produced at the same time by different devices, potentially leading to a duplicate GUID.

Although it could be argued that 2 devices should never have the same MAC address, computer systems have to cope with the real world, where these things happen. It is potentially important that where an application uses a primary key of GUID and collects data from multiple low-end devices, it may be appropriate to include some other distinguishing value from the device along with the GUID to form the primary key.



Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Ian Yates
Ian Yates
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4374 Visits: 445

That may well be true, but I've read "somewhere" (might've even be a SQL server book) that you could make something like 10million GUIDs a second before they would repeat on a PC - now that I think about it, that sounds rather remarkable, but that is the figure that I clearly remember.

I have been using GUIDs as my primary keys in my databases for quite a while now. I found them an advantage for much the same reason as the remote PDA scenario - in one particular application with a backend COM-based API, I could nicely create GUIDs on the client side so that each object had a unique identifier for the local caches even if they had not yet been comitted to the server.

This gave me several advantages
1. I could assign an ID without making a row in the server
2. For 3rd party end users who were driving the API via script rather than my GUI it meant that they could treat editing an object in the same way as adding a new object as an ID was assigned in both cases. I had originally tried to make an ID by writing a row to a table with an identity column and having a "saved" field - but this was very messy.
3. Some others as well

I found that they took a bit of getting used to, and whilst my development environment (Delphi!) has reasonable support for them, there are still some slight drawbacks to their use.

Give them a try - disk space is cheap!





Denny Figuerres
Denny Figuerres
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 5

If I recall correctly there are a few things going on aside from the nic's mac addr and the time.

I know that MS has been long aware that for example a PC with a Modem gets a "bogus" mac for example...

also I am sure they are using some of the same logic that a good random() gen uses to balance the numbers created, things like ticks + time + vectors such as a hardware port peek

also you can use id data from the PC BIOS and stuff like the hard disk serial number.

and I think there is a way that they mix and match the different parts of the data that adds to the uniqe ness.... I know that there are a few nibbles that are const for all GUID's

(Version ID for example)

I may be in error but this is what I seem to have read when I looked at GUID stuff in the past.


PaulR-122207
PaulR-122207
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 292

Interesting. According to BOL, in the topic "Using uniqueidentifier Data":

The main advantage of the uniqueidentifier data type is that the values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world.

If they "guarantee" uniqueness, and someone can demonstrate a reproduction of the same GUID, will they reimburse that someone for all their development time, software & hardware costs, etc...?

I doubt it highly.


David Lu
David Lu
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 135

I agree with Grasshopper's opinion that "Guarantee" issue. I had faced more than once of the duplicates on identifier field and which is also a PK field! But, we have no other choice to still use it....

Is there a way to turn off the identity of this field via t/sql.

I am not talking about "SET IDENTITY_INSERT tableABC ON", I want permanantly to take off this identity without through E.M. to manually do it. And I might have up to 8000 tables need to do so.

p.s. It will be meaningless of a unique identity field without a unique constraint on another field. Since you could have all the duplicate content rows with unique indentity field.

thanks

-D






Adam Machanic
Adam Machanic
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2887 Visits: 714
David,

Off-topic for this thread, but assuming that OldCol is your IDENTITY column:


ALTER TABLE YourTable
ADD NewCol INT NULL
GO

UPDATE YourTable
SET NewCol = OldCol
GO

ALTER TABLE YourTable
ALTER COLUMN NewCol INT NOT NULL
GO

ALTER TABLE YourTable
DROP COLUMN OldCol
GO

EXEC sp_rename 'YourTable.NewCol', 'OldCol'
GO

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
einman33
einman33
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1011 Visits: 512
natural key anyone?
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6268 Visits: 1439
I will like to know if there is any chance that changing the time on a server will affect the unique id's. Is there any chance to get a duplicate id?




My blog

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search