This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 3.
Book On Amazon | Book On Flipkart
Kit on Amazon | Kit on Flipkart
Why buy this Book: We often learn good practices from the people that come before us. Sometimes we later learn why those practices make sense. But there are so many exceptions to these rules. Instead of memorizing the exceptions you can learn the techniques of what is really going on with SQL performance and storage. It’s actually a few simple parts that you can test
What will I learn after reading this book: How data types are stored, spatial data types, Indexes and performance, GUIDs, Transactions and Locks, Isolation levels and database concurrency.
All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 3 in the file SQLQueries2012Vol3Chapter10.1Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”
Introduction to GUIDs
Numbers make great ID fields and for the longest time the integer was the only king of fulfilling this requirement. We often start counting from one when populating data in a table. If we buy one of our competitors sometime in the future, we might want to place their data into the database at our headquarters. If the company that we purchased also started their tables with the number one, how can we merge our tables together and maintain uniqueness?
In hindsight, it would have been nice if the company we purchased had even numbered identity fields and we had all odd numbered identity fields. Well, that might work for two businesses merging. What if a third company was purchased and we needed to add their database to ours as well? There is no telling how big any new project will scale over the next 10 or 20 years.
Here comes the GUID data type to save the day! If our ID field is using the GUID data type and another table we want to merge into a data warehouse is also using this data type, it is guaranteed they will all be unique from as many computers as we are gathering data from.
GUID is an acronym for Global Unique ID(entifier) and is a unique 16 byte number. The term GUID and UNIQUEIDENTIFIER are often interchangeable within the SQL Server community.
Recap of Identity Integers
The UNIQUEIDENTIFIER data type was new to SQL Server 7.0 and often is used with auto generated fields similar to the use of an INT in an IDENTITY column.
The IDENTITY property is going to insert values in an incremental order, unlike the random order produced by a UNIQUEIDENTIFIER data type using the NEWID() function.
Attempting to merge two tables generated by an IDENTITY column into a single table is very likely to end up with gaps, or conflicting values (Duplicates). Let’s create a very simple table and insert five rows of test data into the table to review how this works. The results are seen here
CREATE TABLE TestInt (
IntID INT IDENTITY(1,1),
IntName VARCHAR(25))
GO
INSERT INTO TestInt VALUES
('One'), ('Two'), ('Three'), ('Four'), ('Five')
SELECT * FROM TestInt
IntID | IntName | |
1 | 1 | One |
2 | 2 | Two |
3 | 3 | Three |
4 | 4 | Four |
5 | 5 | Five |
|
| 5 rows |
Introduction of UNIQUEIDENTIFIER Data Type
Like an integer, the GUID is a number. However, instead of being a base 10 number like the integer, it is a hexadecimal number (Base 16). All GUIDs have a format of 8hex-4hex-4hex-4hex-12hex as shown here: B8DC0F5E-E4EF-4EA4-BC39-40721AFE680D
A big advantage of the GUID data type over an IDENTITY column of INT, is they are unique across all tables, databases, and computers, over any foreseeable time period (Hundreds of years or more). This is very useful when combining records from multiple SQL Servers into a single data warehouse, as it will certainly avoid any conflicting entries based on this field.
We can quickly see exactly what a GUID looks like and how to easily generate this unique hexadecimal number by using the following code sample. When viewing the results notice how the same code generates a different value each time the NEWID() function is called.
SELECT NEWID() AS 'GUID-A'
SELECT NEWID() AS 'GUID-B'
SELECT NEWID() AS 'GUID-C'
GUID-A | |
1 | E005915A-6A34-45E9-A57A-DF638BED1A18 |
GUID-B | |
1 | 952E6FD0-6B14-48BE-BA2B-685B7CDDC5E2 |
GUID-C | |
1 | 4B73C91D-AE84-41B2-97D2-233860A78032 |
| 3 rows |
Whenever the NEWID() function is called, it will return a value of the UNIQUEIDENTIFIER data type (e.g., a GUID). Now let’s create another simple table using a UNIQUEIDENTIFIER (GUID) instead of an IDENTITY column, and then insert the same VARCHAR() values in each of the five rows of test data that we used for the TestInt table. It is important to remember that GUIDs are not generated automatically by SQL Server and it is recommended to use the DEFAULT keyword both when creating and inserting data into the table.
CREATE TABLE TestGuid (
GuidID UNIQUEIDENTIFIER DEFAULT NEWID(),
IntName VARCHAR(25))
GO
INSERT INTO TestGuid VALUES
(DEFAULT, 'One'), (DEFAULT, 'Two'), (DEFAULT, 'Three'), (DEFAULT, 'Four'), (DEFAULT, 'Five')
SELECT * FROM TestGuid
GuidID | IntName | |
1 | 6F6EAB2C-FF82-4057-BF9F-ECC3BA62CF6C | One |
2 | BBC1B87E-0E0B-4D55-ABB9-0D585653CA7F | Two |
3 | CB8379E0-AB76-4AC4-B5E0-24DE9C3DFD1F | Three |
4 | B97F7594-3E3E-42B7-B268-A0CAD0F30914 | Four |
5 | 8DD0310C-7844-4C08-9B0D-A266F3F3A11F | Five |
|
| 5 rows |
When comparing the results of the TestInt table values to the TestGuid table values, we can see that the first column in both tables contain unique values, they share a common field name for the second column, in addition to having duplicate values for each respective row in the table.
Book On Amazon | Book On Flipkart
Kit on Amazon | Kit on Flipkart
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Can you Please, elaborate on how GUID’s help in combining two different databases.
For Example :
Company A has Database_A and TableGuid_A
Company B has Database_B and TableGuid_B
Now Company A acquired Company B and we need to merge Databases. How can the two different Guid’s generated at two different instances would be different.
Pinal,
One of the conveniences of using an IDENTITY property is that we can sort by it to get a clear idea of the insert order. How would we get such convenience when using GUIDs? Would we need to have a separate DATETIME field to achieve this?
Thanks
Hi Pinal,
Nice to meet you virtually,
Thanks for the great concept in sql server like GUID’s .
But as I gone through some sides and books, i have learned ,we can use the unique identifier same as identity column in sql server, the advantage of GUID over identity is that, it is unique across tables,databases,servers,computers.but here I am little bit confused ,I am agree for , the GUID values will be unique across tables and databases but how it will be unique across servers and computers. how it will recognize with different servers and computers. would you please explain so that I will be clear for the same.
Also As per theory we can perform addition on alpha numeric numbers in sql server.
whenever I am trying to add two hexadecimal values(GUID) ,I am getting success for this.
select ‘7AC82E45-47A3-4054-999E-AC114F172CD3’+’98339F47-E2E2-4FB7-AC77-3F9830FDEE70′
but whenever I am trying to add two same numbers by converting them into uniqueidentifier ,I am getting fail.
select CONVERT(uniqueidentifier,’7AC82E45-47A3-4054-999E-AC114F172CD3′)+convert(uniqueidentifier,’98339F47-E2E2-4FB7-AC77-3F9830FDEE70’)
why this?…………
IF GUID’s are being created by several SQL databases at different locations on different locations, is there a chance that a GUID will be duplicated somewhere?.. The SQL databases are independent of one another and therefore do not know about each other so the randomness of creating a duplicate GUID must be possible!! In my opinion – Ed 22/4/2015
Will you please answer this question as I notice that the 2 questions above do ask this but there is no clear answer
Thankyou
Edwin
Well a suitable answer could be:-
128-bits is big enough and the generation algorithm is unique enough that if 1,000,000,000 GUIDs per second were generated for 1 year the probability of a duplicate would be only 50%. Or if every human on Earth generated 600,000,000 GUIDs there would only be a 50% probability of a duplicate (from the internet)
Enjoy
Hi there all,
How can i select only the last GUID that was inserted into a table?
Hello,
how to use @@IDENTITY method for uniqueidentifier?
I not found :(
sorry for bad English.
Is there a way to generate the new UID on the basis of the user’s last name, first initial and value of another column? How would this statement be written or any tool for this?
Does the UID contain the date / time created? If so, how to extract?
No it is nothing to do with datetime