Microsoft – SQL Server – Indexes – Changing Definition (in high availability environments)

Microsoft – SQL Server – Indexes – Changing Definition (in high availability environments)

In one of our ETL Applications we have a snippet of code that resembles the following:



     INSERT INTO dbo.supportTableRegion
     (
       [Name]
     )		
     select distinct tblSales.region
     from   dbo.stagingTblSales tblSales (NOLOCK)
     where  not exists ( 
			  select 1 
			  from   dbo.supportTableRegion tblRegion (NOLOCK)
			  where  tblSales.[region] = tblRegion.[Name]
		       )

Upon reviewing job history log this morning, I am seeing that we sometimes fail along the lines of:



Executed as user: salesLoader. Cannot insert duplicate key row in object 'dbo.supportTableRegion' with unique index 'index_Unique_Name'. 
The duplicate key value is (<failing-key-value>). [SQLSTATE 23000] 
(Error 2601).  
The step failed.

And, I am thinking SQL Server, please work with me here.

I think one possible fix is to ignore_dup_key.  But, to do so, I might have to drop and recreate the index.

The script for doing is pasted below:


drop index dbo.[supportTableRegion].index_Unique_Name;
create unique index index_Unique_Name
   on dbo.[supportTableRegion]
     ([Name])
   with
     (ignore_dup_key)
;

But, I am thinking as the table\index are heavily used, I might not want to drop the unique index, as duplicate entries might sneak in.



declare @indexName sysname
declare @isUnique bit
declare @isIgnoreDupKey bit

select 
          @indexName = tblIndex.name
        , @isUnique = tblIndex.is_unique
        , @isIgnoreDupKey = tblIndex.ignore_dup_key 
from   sys.indexes tblIndex
where  object_id = object_id('dbo.supportTableRegion')
and    name = 'index_Unique_Name'

print
   'dbo.supportTableRegion:' + char(13) + char(10)
     + char(9) + '@indexName :-' + @indexName
     + char(13) + char(10)
     + char(9) 
     + ' -- @isUnique :-' + cast(@isUnique as varchar(30))
     + char(13) + char(10)
     + char(9) 
     + ' -- @isIgnoreDupKey :-' + cast( @isIgnoreDupKey as varchar(30)) 
     + char(13) + char(10)

if (@indexName is null)
begin

    print 'Creating Index dbo.supportTableRegion - index_Unique_Name ...'

    create unique index index_Unique_Name
    on dbo.supportTableRegion
    (
	[Name]
    )
    with 
    (
            ignore_dup_key = ON
          , online = ON		          
          , drop_existing = on
    )   		
    ;

    print 'Created Index dbo.supportTableRegion - idx_Unique_Name'

end			
else if (
                  (@isUnique = 0)
               or (@isIgnoreDupKey = 0)
        )    
begin

    print 'Altering Index dbo.supportTableRegion - index_Unique_Name ...'

    create unique index index_Unique_Name
    on dbo.supportTableRegion
    (
	[Name]
    )
    with (
              ignore_dup_key = ON
            , online = ON		          
            , drop_existing = on
	 )   		
	;
    print 'Altered Index dbo.supportTableRegion - index_Unique_Name'

end	

go

Explanation

  • Added ignore_dup_key to silently ignore dup key

References:

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