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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s