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

Azure Data Migration

In this article we can explore the Data Migration Assistant Tool of Microsoft. It helps in Migrating your Local SQL Server database to SQL Azure.

Data Migration Assistant

Data Migration Assistant is a Great Tool from Microsoft providing:

· Assessment checks on Compatibility of Source Database

· Actual Migration of Schema & Data

Download

You can download the tool from:

https://www.microsoft.com/en-us/download/details.aspx?id=53595

Running the Tool

After installation, the tool one execution looks like below:

image

We can try Migration using the + button from the left.

Enter the Source & Target database types.

image

image

Connect to the Source server.

image

image

On running the checks, you can see the results below.

image

Actual Migration

Now let us try performing the actual migration.

image

image

image

Click on the Deploy Schema button now.

image

Click on the Start data migration button now.

image

image

You can see the Migration is complete without any errors.

Summary

In this article we have explored using the Data Migration Tool from Microsoft.