SQL Server – Transact SQL – NewSequentialID

Background

Found myself reviewing a Stored Procedure that uses NEWID to generate a uniqueIdentifier.

Knowing that random keys can cause page splits, I wanted to see how much effort it will take to use the NewSequentialID function.

 

Implementation

DDL

Create Schema


if schema_id('datatypeUniqueIdentifier') is null
begin
exec('create schema [datatypeUniqueIdentifier] authorization [dbo]')
end

NewID


use [tempdb]
go

if object_id('[datatypeUniqueIdentifier].[newid]') is null
begin

create table [datatypeUniqueIdentifier].[newid]
(

[id] uniqueIdentifier not null

, [dateAdded] datetime
constraint DatatypeUniqueIdentifier_NEWID_DataAdded
default getdate()

, constraint [PK_DatatypeUniqueIdentifier_NEWID]
primary key
(
[id]
)

)

end

 

NewSequentialID


create table [datatypeUniqueIdentifier].[newSequentialID]
(
 
   [id] uniqueIdentifier not null
     constraint DatatypeUniqueIdentifier_NEWSequentialID_ID
        DEFAULT NEWSEQUENTIALID()

, [dateAdded] datetime not null

constraint DatatypeUniqueIdentifier_NEWSequentialID_DataAdded
default getdate()

, constraint [PK_DatatypeUniqueIdentifier_NewSequentialID]
primary key
(
[id]
)
)

DML

Let us add data and get the assigned unique Identifier

NewID

We will issue NEWID and insert the returned ID into our table.


set nocount on
go

use [tempdb]
go

truncate table [datatypeUniqueIdentifier].[newid]
go

declare @id uniqueidentifier

--Get NEWID
set @id = NEWID()

--Use NEWID
insert into [datatypeUniqueIdentifier].[newid]
([id])
values
(@id)

--Output NEWID
print '@id : ' + cast(@id as varchar(60))

NewSequentialID

We can not issue NewSequentialID and so to get the assigned ID, we will insert data and use “Output Inserted” as a feedback mechanism.


set nocount on
go

use [tempdb]
go

truncate table [datatypeUniqueIdentifier].[newSequentialID]
go

declare @id uniqueidentifier
DECLARE @tblValue TABLE
(
ID uniqueidentifier
);

--Add Record
--And, placed resultant ID into @tblValue.ID
insert into [datatypeUniqueIdentifier].[newSequentialID]
Output Inserted.ID
Into @tblValue
default values;

select @id = [ID]
from @tblValue

--Output NEWID
print '@id : ' + cast(@id as varchar(60))

 

Query Plans – Individual

NEWID

Query Plan

NEWID

 

New Sequential ID

Query Plan

NewUniqueIdentifier

 

Query Plans – Compare

Code


set statistics io on;
set nocount on;
go

use [tempdb]
go

truncate table [datatypeUniqueIdentifier].[newSequentialID]
go

truncate table [datatypeUniqueIdentifier].[newSequentialID]
go

declare @id uniqueidentifier

--Get NEWID
set @id = NEWID()

--Use NEWID
insert into [datatypeUniqueIdentifier].[newid]
([id])
values
(@id)
go

print replicate('*', 120)

declare @id uniqueidentifier
DECLARE @tblValue TABLE
(
ID uniqueidentifier
);

--Add Record
--And, placed resultant ID into @tblValue.ID
insert into [datatypeUniqueIdentifier].[newSequentialID]
Output Inserted.ID
Into @tblValue
default values;

select @id = [ID]
from @tblValue

 

Query Plans

compareQueryPlans

 

Statistics IO

StatisticsIO

Explanation

  1. Query Plan
    • NEWID is at 30%
    • NewSequentialID is 70%
  2. Statistics IO
    • Single IO record
    • Multiple IO records
      • Adding record into Temp table
      • Adding record into actual table
      • Reading record from Temp table

 

Summary

From our quick study, we see that if we need to know the value we are entering into the destination table, NEWID is a lot less expensive than the correspondent NewSequentialID.

 

References

Q/A

  1. SQL Sever Scopr Identity for GUIDs
    http://stackoverflow.com/questions/1509947/sql-server-scope-identity-for-guids
  2. Return NEWSEQUENTIALID() as an output parameter
    http://stackoverflow.com/questions/3335014/return-newsequentialid-as-an-output-parameter
  3. Adding non-nullable NEWSEQUENTIALID() column to existing table
    http://dba.stackexchange.com/questions/72604/adding-non-nullable-newsequentialid-column-to-existing-table
  4. Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
    https://www.mssqltips.com/sqlservertip/1600/auto-generated-sql-server-keys-with-the-uniqueidentifier-or-identity/

One thought on “SQL Server – Transact SQL – NewSequentialID

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s