SQL Server Global Unique Identifier (GUIDs)

Hello Folks,

You might have heard about the Surrogate Primary Keys. It means an artificial, computer-generated value that is being used to uniquely identify the row.

There are basically two types of Surrogate Keys-

  • Identity Columns
  • GUIDs

In the Identity Columns, SQL Server automatically generates incrementing integers for identity columns at the time of the insert and SQL INSERT statement normally can’t interfere with that process by supplying a value for the identity column.

Now I am going to deal with GUID and will give you some heads up:

  • Globally Unique Identifier’s are sometimes used as Primary Keys.
  • GUIDs are in-fact the best choice in replicated scenarios, when you have to generate unique values at different locations.
  • GUIDs are being generated by the SQL code or by a column default, rather than automatically generated as in Identity Column.
  • So the Developer has more control over the GUID creation than on the Identity Columns.

There are basically five ways to generate GUID primary key values when inserting new rows:

  1. NEWID () function can create the GUID in T-SQL code prior to the INSERT.
  2. NEWID () function can create the GUID in client code prior to the INSERT.
  3. NEWID () function can create the GUID in an expression in the INSERT command.
  4. NEWID () function can create the GUID in a column default.
  5. NEWSQUENTIALID () function can create the GUID in a column default. This is the only method that avoids the page splits performance issues with GUIDs.

All these five ways will be clearer to you if you will read further.

  • This Query will simply tests the NEWID () function:
USE TEST
SELECT NEWID();

1_SQL_Server_Globally_Unique_Identifiers(GUIDs)

For the following query, I am creating a table “Emp” from “TEST” database, which can be seen as-

CREATE TABLE Emp
(
E_Id int NOT NULL,
P_Id varchar(50) DEFAULT NEWID(),
E_Name varchar(40)
)
  • This Query will create the GUID in a column where column default is NEWID () function:
INSERT Emp(E_Id,P_Id,E_Name)
VALUES (1,DEFAULT,'Piyush');

The result can be seen as:

2_SQL_Server_Globally_Unique_Identifiers(GUIDs)

  • This Query will directly insert the NEWID() function value:
INSERT Emp(E_Id,P_Id,E_Name)
VALUES (2,NEWID(),'Harsh');

The result can be seen as:

3_SQL_Server_Globally_Unique_Identifiers(GUIDs)

  • This Query will generate the NEWID() function values with the help of a variable:
DECLARE @NewGUID UniqueIdentifier;
SET @NewGUID = NEWID();
 
INSERT Emp(E_Id,P_Id,E_Name)
VALUES (3,@NewGUID,'Raman');

The result can be seen as:

4_SQL_Server_Globally_Unique_Identifiers(GUIDs)

NEWSEQUENTIALID ():

  • It creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.
  • After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function.
  • This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
  • Its return type is Unique Identifier.
  • The NEWSEQUENTIALID () can only be used with DEFAULT constraints on table columns of type UNIQUEIDENTIFIER.
  • NEWSEQUENTIALID () cannot be referenced in queries.
  • As mentioned earlier use NEWSEQUENTIALID () to generate GUIDs to reduce page contention at the leaf level of indexes.
  • Each GUID generated by using NEWSEQUENTIALID () is unique on that computer.
  • This will become more clear to you if you see following example:

Now, we are creating a table “mytable” under the “TEST” database;

CREATE TABLE myTable 
(
GUIDtableID UNIQUEIDENTIFIER 
NOT NULL DEFAULT (NEWSEQUENTIALID()) PRIMARY KEY,
Name VARCHAR(30) 
)

So the value can be inserted as;

INSERT myTable(Name) 
VALUES ('Piyush'),('Amit'),('Sarab')

The result can be seen as:

5_SQL_Server_Globally_Unique_Identifiers(GUIDs)

Well, this was all about GUIDs.

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “SQL Server Global Unique Identifier (GUIDs)”

  1. It was very descriptive.

    I want to add a point to this article:

    1. GUIDs are being considered as Primary Keys.

    a. But i think they should not be given preference until and unless there is no other option to choose from, because if you make GUIDs primary key this will increase the size of clustering key and index pages will also increase resulting in a performance hit.

    Question:

    Article says NewSequentialID() is the one which avoids page splits. Another points mentioned that “After restarting Windows, the GUID can start again from a lower range, but is still globally unique.”

    After considering both these points i think this might result into page splits, though this will be very minimal and only after SQL Services are bounced but i think using NewSequentialID () function can never avoid it permanently.

    Whats your take on this?

    These are just my thoughts and not been tested, will test it soon and let you know.

    Thanks,
    Sarabpreet Singh

  2. Obviously dude…GUIDs are not meant to give the preference over Primary Key, as i have never said so… Infact they are basically being used in the cases when we require more flexibility over the column, because its the unique key for the Single computer. GUID are being used in replicated scenarios, when you have to identify a row, where the simple Primary Key might not solve the problem.

    Yes article says “After restarting Windows, the GUID can start again …..”, its completely true, you won’t find the same GUID value again.

    As for the Page Splits, NewSequentialID () is better than NewID () as the GUID are sequentially arranged, this tries to fill the data pages and indexes, which reduces the chance of occuring Page Splits magnificantly. Although it might not done it completely, but can try to reach that point.

Leave a Reply

Your email address will not be published. Required fields are marked *