DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
Issue:
Understanding GUID VS IDENTITY in SQL Server
In our normal environment, auto-increment is very common and necessary when we create a table.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
We used the following SQL statement to create a table named "Person" and defined the "Personid" column to be an auto-increment primary key field.
CREATE TABLE Person (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
To insert new records into the "Person" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically).
INSERT INTO Person(FirstName,LastName,Age) VALUES
('Lars','Monsen',18),
('Mary','Green',19);
The SQL statement above would insert two new records into the "Person" table. The "Personid" column would be assigned unique values(1 and 2).
Now let’s create a new table “Student” that contains the union of all the records from the “Person” table and “Person2” table.
CREATE TABLE Students
(
Personid int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
)
GO
INSERT INTO Students
SELECT * FROM Person
UNION ALL
SELECT * FROM Person2
But we got one error after executing above.
This error is due to both “Person” and “Person2” tables having the same values for the Id column which is also the primary key column for the newly created Students table. Therefore, when we try to insert the union of the records from “Person” and “Person2” tables, the “Violation of PRIMARY KEY constraint” error occurs. This is when we need to use the GUID data type.GUID is a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers. The term GUID stands for Globally Unique Identifier and it is used interchangeably with UNIQUEIDENTIFIER. To create a GUID in SQL Server, the NEWID() function is used as shown below
SELECT NEWID()
Above returns a GUID like ‘FB4F6574-D29D-43C6-A797-6096EC74169F’ and execute above SQL multiple times and you will see a different value every time. This is because the NEWID() function generates a unique value whenever you execute it.
To declare a variable of type GUID, the keyword used is UNIQUEIDENTIFIER as mentioned in the script below:
DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = NEWID()
SELECT @GUID
Above returns as ‘0332C398-193E-4295-8323-15BF3DDD6702’.So GUID values are unique across tables, databases, and servers. GUIDs can be considered as global primary keys. Local primary keys are used to uniquely identify records within a table.
Let’s create new tables Person3 and Person4 but this time we change the data type of the Id column from INT to UNIQUEIDENTIFIER.
To set a default value for the column we will use the default keyword and set the default value as the value returned by the ‘NEWID()’ function.
CREATE TABLE Person3 (
Personid UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int);
INSERT INTO Person3(FirstName,LastName,Age) VALUES
('Lars','Monsen',18),
('Mary','Green',19);
CREATE TABLE Person4 (
Personid UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int);
INSERT INTO Person4(FirstName,LastName,Age) VALUES
('Tom','White',20),
('Jerry','Red',17);
select * from Person3
select * from Person4
Now if you select all the records from Person3 and Person4 tables, you will a result that looks like this:
Now we have globally unique values in the Id columns of both Person3 and Person4 tables.
Let’s create a new Table named Students2 and just as we did before, try to insert the union of the records from Person3 and Person4 tables.
CREATE TABLE Students2
(
Personid UNIQUEIDENTIFIER PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int);
GO
INSERT INTO Students2
SELECT * FROM Person3
UNION ALL
SELECT * FROM Person4
This time we will see that there will be no “Violation of PRIMARY KEY constraint” error, since the values in the Id column of both Person3 and Person4 tables are unique.
Besides, you could also explicitly use NEWID() when inserting the data as below:
CREATE TABLE Customer
(
CustomerId uniqueidentifier NOT NULL,
CustomerName varchar(70) NOT NULL,
);
INSERT Customer (CustomerId, CustomerName)
VALUES
(NEWID(), 'Ann'),
(NEWID(), 'Tom');
SELECT * FROM Customer;
Result:
DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 88%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EWM%3C/text%3E%3C/svg%3E)
