Technical: Microsoft – SQL Server – Execution Plan – Index / Ignore-Dup-Key

Technical: Microsoft – SQL Server – Execution Plan – Index / Ignore-Dup-Key

Here is a quick exercise to determine the ramifications of opting to use the “ignore dup key” clause when creating a unique index.

Keep in mind that there are a couple of ways to ensure column value uniqueness:

  • Unique Constraint
  • Unique Index

If column value uniqueness is being guaranteed  via Unique Constraint one is unable to override the DML failing on duplicate entries.

On the other hand, if uniqueness is being defined via Unique Index, one is able to opt to suppress duplicate entry errors encountered during DML.

SQL Syntax

SQL Syntax – Constraint – Unique

  Syntax:
          <column-name> <datatype> 
             constraint <constraint-name>  unique

  Sample:
          productType varchar(100)
             constraint contraintUniqueProductType unique

SQL Syntax – Constraint – Unique

  Syntax:
          create unique index <index-name>
            on <table-name>
             (<column-name-1>, <column-name-2>, ... <column-name-n>)

  Sample:
           create unique index idx_ProductType
           on [dbo].[product]
               ([productType])

SQL Syntax – Constraint – Unique (ignore_dup_key)

  Syntax:
          create unique index <index-name>
            on <table-name>
             (<column-name-1>, <column-name-2>, ... <column-name-n>)
            with ignore_dup_key
            ;

  Sample:
           create unique index idx_ProductType
           on [dbo].[product]
               ([productType])
           with ignore_dup_key
           ;

Create Table



use [tempdb]
go

/*
    drop table [dbo].[customer]
    drop table [dbo].[product]
*/

if OBJECT_ID('dbo.customer') is null
begin

    create table dbo.[customer]
    (
          [id] bigint not null identity(1,1)
        , [Name] sysname not null 
                constraint constraintUnique__Customer__name unique

        , [customerCode] sysname not null

        , [addedBy] sysname not null 
             constraint 
               constraintDefault__Customer__AddedBy default SYSTEM_USER

        , [dateAdded] datetime not null 
             constraint 
              constraintDefault__Customer__DateAdded default getutcdate()    

        , constraint PK_Customer primary key ([id])
    )

    create unique index idx_CustomerCode
    on [dbo].[customer]
    ([customerCode])
    with ignore_dup_key
    ;

end
go

if OBJECT_ID('dbo.product') is null
begin

    create table dbo.[product]
    (
          [id] int not null identity(1,1)

        , [Name] sysname not null 
               constraint constraintUnique__Product__name unique

        , [productCode] sysname not null

        , [addedBy] sysname not null 
               constraint 
                  constraintDefault__Product__AddedBy default SYSTEM_USER

        , [dateAdded] datetime not null 
               constraint 
                 constrDefault__Product__DateAdded default getutcdate()

        , constraint PK_Product primary key ([id]) 

    )

    create unique index idx_ProductCode
    on [dbo].[product]
    ([productCode])
    --with ignore_dup_key
    ;

end
go

Populate Table



use [tempdb]
go

set nocount on;

begin tran

    BEGIN TRY

        delete from [dbo].[customer];
        delete from [dbo].[product];

        dbcc checkident ('dbo.customer', reseed, 0);
        dbcc checkident ('dbo.product', reseed, 0);

        insert into dbo.[customer] ([Name],[customerCode]) 
        values ('AT & T', '1111');

        insert into dbo.[product] ([Name], [productCode]) 
        values ('Cellphone - Kyocera', 'A1');

    END TRY
    BEGIN CATCH

       select 'In Exception Handling' as [staging] 
       select XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT'

    END CATCH

rollback tran

Execution Plan

Execution Plan - Index - IgnoreDupKey

Execution Plan – Explanation

The execution plan for the unique index\ignore dup key is versely different than the execution plan of unique index without the ignore_dup_key suppression.

Here are the differences:

  • The main crust is that the Index Maintenance are different – When ignore_dup_key is effected, the ignore_dup_key is performed even before the clustered index; whereas when ignore_dup_key is not effected, all the index maintenance operator is performed within the Clustered Index Maintenance 
  • When ignore_duo_key is in play the ignore_dup_key indexes are processed and the  result is used thereafter.  Note that there is quite a bit of immunity that is built around maintaining the uniqueness and suppressing any errors
  • Some of that immunity is exposed via a few operators; such as “Nested Loops\ Left Semi Join”, Assert, Top

So next time you think about using the ignore_dup_key think how the SQL Engine maintains uniqueness and how it silences errors.

Like any other engine, part of the internal details of SQL Server’s technology is the orchestration of different instructions and components; and sometimes one needs to be aware of where specific events and actions are being handled — Are they being handled in the Storage or Query Engine.

Microsoft Connect Items

As a follow-up there are a couple of Connect Entries that are relevant.  Consider voting for the ones that you consider germane.

Reference

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