Unique Identifier: Usage and Limitations
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum
-
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
-
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!
-
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.
-
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.
-
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
-
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
whoisactive -
natural key anyone?
-
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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply