SQL SERVER – Introduction to GUIDs – SQL Queries 2012 Joes 2 Pros Volume 3 – Advanced Query Tools and Techniques for SQL Server 2012

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

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'


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


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)