Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unique Identifier: Usage and Limitations Expand / Collapse
Author
Message
Posted Monday, June 14, 2004 11:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 26, 2011 11:13 AM
Points: 62, Visits: 2
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum


Post #120938
Posted Thursday, June 24, 2004 3:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174

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 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #122711
Posted Thursday, June 24, 2004 5:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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!




Post #122734
Posted Thursday, June 24, 2004 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 8:49 AM
Points: 14, Visits: 3

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.

 

Post #122735
Posted Thursday, June 24, 2004 9:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:14 PM
Points: 231, Visits: 276

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.

Post #122801
Posted Thursday, June 24, 2004 10:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:29 PM
Points: 192, Visits: 130

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





Post #122831
Posted Tuesday, January 25, 2005 1:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
Post #157606
Posted Friday, June 24, 2005 8:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
natural key anyone?
Post #193933
Posted Monday, June 7, 2010 6:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:55 PM
Points: 3,084, Visits: 1,431
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
Post #933481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse