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();

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:

• 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:

• 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:

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:

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