SQL Server–Compare Performance of INT, GUID, Sequential GUID

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.