In this post we can compare the performance of ID as INT, GUID & Sequential GUID
Advantages of GUID Columns as Primary Key
- Makes them Globally Unique
- Can backup/restore/sync to another DB without breaking Primary Keys & Foreign Keys
- No Performance Impact (proven here)
Note
Sequential GUID preferred over GUID to avoid pagination issues
PROOF – No Performance Issues on GUID
Create Table – INT Primary AutoIncrement
CREATE TABLE [dbo].[IDTest_INT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_INT] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
Create Table – GUID Primary –
CREATE TABLE [dbo].[IDTest_GUID](
[ID] uniqueidentifier DEFAULT NEWID(),
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_GUID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
Create Table – GUID Primary – Sequential GUID
CREATE TABLE [dbo].[IDTest_SequentialGUID](
[ID] uniqueidentifier DEFAULT NEWSEQUENTIALID(),
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_SequentialGUID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
INSERT TEST – 1 LAKH RECORDS
INT 13 seconds
GUID 12 seconds
Sequential GUID 11 seconds
INFERENCE No Impact on Performance for Insert
Here are the queries for same:
declare @count int
select @count = 1
while @count <= 100000
begin
insert into IDTest_INT (name) values(‘Name’ + STR(@count))
insert into IDTest_GUID (name) values(‘Name’ + STR(@count))
insert into IDTest_SequentialGUID (name) values(‘Name’ + STR(@count))
select @count = @count + 1
end
SELECT TEST – ON ID
INT 0seconds
GUID 0seconds
Sequential GUID 0seconds
Here are the queries for same:
SELECT * FROM IDTest_INT WHERE ID = 1000
SELECT * FROM IDTest_GUID WHERE ID = ‘1EDE341C-7692-4D7C-A99D-000C387337DE’
SELECT * FROM IDTest_SequentialGUID WHERE ID = ’84a4d912-5448-ea11-8740-48f17ffd0966′
INFERENCE No Impact on Performance for Select
More Test
Conducted the Test with 20 Lakh records & Full Scan shown NO DIFFERENCE as well.
Summary
Performance of INT vs GUID vs SEQUENTIAL GUID on Primary Key are same.
One thought on “SQL Server–Compare Performance of INT, GUID, Sequential GUID”