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

  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

Click to Download Scripts

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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.

  2. 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

  3. 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?…………

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s