Skip to content
Donner's Daily Dose of Drama
Donner's Daily Dose of Drama
  • The Good
    • Blogging
    • Consumer Protection
    • Environment
    • Ethics
    • Geek’s Home
    • Lisa Lanett
    • Medfield
    • Music
    • Parenting and Technology
    • Travel
    • wow
  • The Bad
    • Business
    • Ebay
    • Investment
    • Job search
    • Personal Finance
    • Politics
  • The Ugly
    • Information Technology
      • Business Intelligence
      • Content Management
      • Free Software
      • I18N and L10N
      • Java
      • Open Source
      • Mobile Devices
      • Open Source Business Intelligence
      • OSBI
      • SDA
      • Security
      • Smartphone
      • Software Best Practices
      • Software Engineering
      • SQL Server
      • Streaming Media
      • Web
    • Austria
    • Fiction
    • Hardware
    • iPod
    • Miscellaneous
    • Uncategorized
    • Video
    • Weekend Warrior
Donner's Daily Dose of Drama

Different seed after IDENTITY INSERT in SQL Server 2012 and SQL Server 2008

Christian Donner, May 21, 2015May 21, 2015

Once something takes more than an hour to figure out, it is always a good candidate for a new blog post. This one definitely took longer than an hour.

I am supporting a data mart with compensation data for an international company. There is a currency dimension table that gets populated from a view in an operational database. The currency key is an IDENTITY column and gets populated with sequential values, starting at 1 with an increment of 1.

The reports have regional specialties and a report template typically only works with one of the currencies. Because the list of currencies never changed, some report developer decided to hard-code the currency keys into the stored procedures that produce the data sets for the compensation reports. For example, I found joins like this one:

			LEFT JOIN [CMP_vw_Exchange_Rate] cc
				ON aa.[Salary_Date_Key] = cc.Date_Key
				AND cc.[Currency_Key] = 5		-- for Euro

When the data mart was migrated from SQL Server 2008 to SQL Server 2012, some reports started to show compensation figures that were widely inaccurate. I eventually found out that the wrong exchange rates were applied, but why? Nothing had changed in the code or the data. When I dug in further, I noticed that the dimension keys were different.

Currency dimension in SQL 2008
Currency dimension in SQL 2008
Currency dimension in SQL 2012
Currency dimension in SQL 2012

As you can see, the AUD used to have key 2 in 2008, now it has key 1. Why is that?

To find an explanation, I looked at the SSIS package that populates the dimension table. The package does three separate things with the table:

  1. It deletes the old values (TRUNCATE)
  2. It inserts the “NA” row explicitly with the key -1, by wrapping the insert statement in a SET IDENTITY_INSERT ON statement
  3. It inserts the results of the currency view from the operational system

I was able to show that these 3 steps produce different results between SQL 2008 and SQL 2012 with the following script:

create table ##test 
(
 currency_key int identity(1,1),
 currency_code char(10)
);

set identity_insert ##test on
insert ##test (currency_key, currency_code) values (-1, 'NA')
set identity_insert ##test off

insert into ##test values ('AUD');
insert into ##test values ('CAD');

select * from ##test;

drop table ##test;

In SQL 2008, AUD has key 2, in SQL 2012 it has key 1. The reason is that the IDENTITY_INSERT leaves a gap in SQL 2008 when it probably shouldn’t. This was “fixed” in 2012.

Because legacy systems can contain countless traps like this one that are impossible to detect and fix, there is a substantial risk that other things are broken. I got my hopes up about a broad safety measure when I read about Trace Flag 272 here and here. When I applied it to my development instance of SQL 2012, my keys did not revert back to the SQL 2008 values, unfortunately. Either I did not make the configuration change correctly, or it has no influence on this particular issue.

As a crude workaround I am now inserting a dummy value after the IDENTITY_INSERT OFF operation, like so:

insert ##test values ('dummy')
delete from ##test where currency_code = 'dummy'

This increments the seed by 1 and the remaining rows have the same keys as in the 2008 instance. However, other dimensions may have the same issue that may not surface for months or years. Fingers crossed. Thanks, Microsoft.

Related Posts:

  • OpenVPN
  • The Voip.ms SMS Integration for Home Assistant
  • Computer Build 2025
  • Enphase Envoy Local Access
  • SUTAB Scam?

Business Intelligence SQL Server

Post navigation

Previous post
Next post

Leave a Reply

Your email address will not be published. Required fields are marked *

Pages

  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements

Recent Comments

  • Christian Donner on Sealing a leaky cast-iron fireplace chimney damper
  • Eric on Sealing a leaky cast-iron fireplace chimney damper
  • Christian Donner on Contact Christian Donner
  • Max on Contact Christian Donner
  • Christian Donner on Contact Christian Donner

Tags

AHCI Amazon Android ASP.Net AT&T Droid Drupal email Error failure featured firmware Garmin Godaddy Google honda Internet Explorer 8 iPhone Lenovo Lisa Lanett Modules NAS Nexus One Paypal Performance Privacy QNAP raid RS-407 sauna Security spam SQL SR3600 Synology T-Mobile T430s transmission tylö Verizon Virus VMWare Windows 7 windows 8.1 Windows Mobile
  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements
©2025 Donner's Daily Dose of Drama | WordPress Theme by SuperbThemes