Introduction to GUIDs: Merge Tables Without Key Clashes

An introduction to GUIDs starts with one problem: two tables that both count from 1. A GUID is a 16 byte value that SQL Server can create without asking any other server. This post merges two small tables, first with INT keys and then with GUID keys.

Two Counters, One Table

Two shops each number their orders from 1. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE ShopA (OrderID INT IDENTITY(1,1) PRIMARY KEY, Item VARCHAR(20) NOT NULL);
CREATE TABLE ShopB (OrderID INT IDENTITY(1,1) PRIMARY KEY, Item VARCHAR(20) NOT NULL);
GO

INSERT INTO ShopA (Item) VALUES ('Tea'), ('Notebook'), ('Kite');
INSERT INTO ShopB (Item) VALUES ('Seeds'), ('Lamp');
GO

Now the head office merges both shops into one table. OrderID is the primary key, so each number can appear only once.

CREATE TABLE AllOrders (OrderID INT PRIMARY KEY, Item VARCHAR(20) NOT NULL);
GO

INSERT INTO AllOrders (OrderID, Item) SELECT OrderID, Item FROM ShopA;
INSERT INTO AllOrders (OrderID, Item) SELECT OrderID, Item FROM ShopB;

The first insert copies 3 rows. The second fails with Msg 2627, because Shop B also has an order 1. A count shows that only the 3 rows from Shop A arrived.

SELECT COUNT(*) AS RowsMerged FROM AllOrders;

You could renumber one shop, but a third shop would clash again. A GUID avoids the renumbering.

What a GUID Looks Like

GUID stands for globally unique identifier. In SQL Server the data type is UNIQUEIDENTIFIER, and the NEWID() function creates a value. Every call returns a new one.

SELECT NEWID() AS GuidA, NEWID() AS GuidB;

A GUID is written as 32 hex digits in groups of 8, 4, 4, 4 and 12. NEWID() also returns a new value for every row of a query.

SELECT n, NEWID() AS RowGuid FROM (VALUES (1), (2), (3)) AS v(n);

The three rows have three different GUIDs. A GUID is 16 bytes long, and its text form is 36 characters. An INT takes 4 bytes.

SELECT DATALENGTH(NEWID()) AS GuidBytes, DATALENGTH(CAST(1 AS INT)) AS IntBytes, LEN(CONVERT(CHAR(36), NEWID())) AS GuidCharacters;

Let a Default Create the Value

SQL Server doesn’t fill a GUID column for you. Add DEFAULT NEWID() to the column. Without it, an insert that skips the column fails.

CREATE TABLE NoDefault (OrderID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, Item VARCHAR(20) NOT NULL);
GO

INSERT INTO NoDefault (Item) VALUES ('Tea');

That’s Msg 515, because the column doesn’t allow NULL. These tables have the default, and each shop gets its own GUIDs.

CREATE TABLE ShopAGuid (OrderID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY, Item VARCHAR(20) NOT NULL);
CREATE TABLE ShopBGuid (OrderID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY, Item VARCHAR(20) NOT NULL);
GO

INSERT INTO ShopAGuid (Item) VALUES ('Tea'), ('Notebook'), ('Kite');
INSERT INTO ShopBGuid (Item) VALUES ('Seeds'), ('Lamp');
GO

Merge Without a Clash

Now merge again. This time the target table also records which shop each row came from.

CREATE TABLE AllGuidOrders (OrderID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, Shop CHAR(1) NOT NULL, Item VARCHAR(20) NOT NULL);
GO

INSERT INTO AllGuidOrders (OrderID, Shop, Item) SELECT OrderID, 'A', Item FROM ShopAGuid;
INSERT INTO AllGuidOrders (OrderID, Shop, Item) SELECT OrderID, 'B', Item FROM ShopBGuid;
GO

SELECT OrderID, Shop, Item FROM AllGuidOrders ORDER BY Shop, Item;

All five rows arrive, with no renumbering. Neither shop needed to know about the other. NEWID() makes random 16 byte values, so a clash isn’t a practical worry.

Which Row Came Last?

A GUID has no order, and it holds no date. It’s random, so a sort by GUID returns the rows in an arbitrary order. A datetime column helps less than you’d expect, because rows added in the same millisecond tie. This table keeps an identity column and a time beside the GUID.

CREATE TABLE Parcels (ParcelID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY, Item VARCHAR(20) NOT NULL, Seq INT IDENTITY(1,1) NOT NULL, AddedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME());
GO

INSERT INTO Parcels (Item) VALUES ('Tea');
INSERT INTO Parcels (Item) VALUES ('Notebook');
INSERT INTO Parcels (Item) VALUES ('Kite');
INSERT INTO Parcels (Item) VALUES ('Seeds');
INSERT INTO Parcels (Item) VALUES ('Lamp');
GO

SELECT Item, Seq FROM Parcels ORDER BY ParcelID;

SELECT Item, Seq, CONVERT(VARCHAR(23), AddedAt, 121) AS AddedAt FROM Parcels ORDER BY AddedAt;

SELECT Item, Seq FROM Parcels ORDER BY Seq;

The first query shuffles the five parcels. The second puts Seeds before Kite, although Kite went in first, because the two share a time. The third returns Tea, Notebook, Kite, Seeds and Lamp, the order they went in. An identity column beside the GUID makes the newest row easy to find.

SELECT TOP (1) ParcelID, Item FROM Parcels ORDER BY Seq DESC;

Reading the New Key Back

To read the GUID you inserted, use the OUTPUT clause. Output Clause in Simple Examples shows more examples.

INSERT INTO ShopAGuid (Item) OUTPUT inserted.OrderID VALUES ('Gloves');

SCOPE_IDENTITY() tracks identity columns only. A table with a GUID key and no identity column returns NULL.

INSERT INTO ShopAGuid (Item) VALUES ('Scarf');
SELECT SCOPE_IDENTITY() AS ScopeIdentity;

Can You Add Two GUIDs?

No. SQL Server refuses to add two UNIQUEIDENTIFIER values.

SELECT CONVERT(UNIQUEIDENTIFIER, '7AC82E45-47A3-4054-999E-AC114F172CD3') + CONVERT(UNIQUEIDENTIFIER, '98339F47-E2E2-4FB7-AC77-3F9830FDEE70') AS Total;

That’s Msg 8117. Two GUIDs written as plain text do add up, but the plus sign joins them into one string. The query below returns the length of that string.

SELECT LEN('7AC82E45-47A3-4054-999E-AC114F172CD3' + '98339F47-E2E2-4FB7-AC77-3F9830FDEE70') AS JoinedLength;

The length is 72, twice the 36 characters of one GUID.

A GUID From Your Own Values

NEWID() takes no input, so it can’t use a last name. If you want the same key for the same input, hash the values and keep 16 bytes of the hash. Put a separator between the values, so two different inputs can’t join into the same text.

SELECT CAST(SUBSTRING(HASHBYTES('SHA2_256', 'Rao|A|2026'), 1, 16) AS UNIQUEIDENTIFIER) AS FirstTime,
  CAST(SUBSTRING(HASHBYTES('SHA2_256', 'Rao|A|2026'), 1, 16) AS UNIQUEIDENTIFIER) AS SecondTime,
  CAST(SUBSTRING(HASHBYTES('SHA2_256', 'Rao|B|2026'), 1, 16) AS UNIQUEIDENTIFIER) AS OtherInput;

The first two columns match, because the input matches. The third differs. This key is repeatable, not random, so use it only where you want repeatable.

The Price: 16 Bytes

A GUID takes four times the space of an INT in every row, and in every index that holds it. Random values also scatter new rows across a clustered index. The posts below measure that cost.

Clean Up

Remove the sample tables when you finish.

DROP TABLE Parcels;
DROP TABLE NoDefault;
DROP TABLE AllGuidOrders;
DROP TABLE ShopBGuid;
DROP TABLE ShopAGuid;
DROP TABLE AllOrders;
DROP TABLE ShopB;
DROP TABLE ShopA;

What to Remember

Use a GUID when separate systems create keys and the rows meet later. Add DEFAULT NEWID() to the column. Keep an identity column or a time column if you need the order. Read a new key back with OUTPUT.

To go further, read NEWID vs NEWSEQUENTIALID: What Each Does to Your Index and When a GUID Clustered Primary Key Gets Expensive.

A GUID is not a counter you share, it is a value nobody needs to coordinate.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.