THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

SET IDENTITY_INSERT little bug(?)

While I was working on a package to incrementally load a dimension in the DWH I’m developing for a customer, I stumbled upon a tricky little bug (I think, I still have to have a response from MS) that had – and will have – a little impact on how I create my packages.

I’ve found that, when working on an empty table that has an identity column, if you insert a value forcing the identity value, the next time SQL Server will generate the identity value, it will generate it missing one number. So, for example, you’ll have

0 (Forced Identity Value)
2  
3  

instead of having the correct sequence

0 (Forced Identity Value)
1  
2  

Not a huge problem as you can see, but I’d like to see it fixed, since can cause little troubles if your forgot about it and you expect to have predictable identity values. I know that identity values are usually not 100% predictable, but I find them useful to speed-up SSIS package development for loading dimensions tables while keeping package complexity low. Since I apply Agile Principles to BI, simplicity is fundamental, since it allows me to change package quickly, easily embracing changes that comes from refactoring or from customer new needs.

You can find the connect item to vote here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=492452

Published Wednesday, September 30, 2009 8:52 AM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul Nielsen said:

Your identity values have meaning such that a skipped number causes grief? why?

September 30, 2009 11:53 AM
 

Adam Machanic said:

Why don't you just script the values you expect for your dimensions and turn off IDENTITY altogether? That's what I do, when I want predictability...

September 30, 2009 12:06 PM
 

Davide Mauri said:

Hi guys, your question was expected :-)

I discovered this bug just by chance. I have a dimension that gets populated from SAP. Some SAP numeric codes doesn't have any lookup table so we have external tables that allows us to map, say, code 100 to "Company group 1" (for example). So this dimension is created joining SAP tables with our lookup table.

During development, to speed up the first release of a cube so that people can start to validate it, I inserted the rows in the lookup table by hand, setting code 100 to id 1.

Each time a person reported the he has validated his portion of data, we wrote unit test to automatize testing to validate all future changes. One of the test checks that a specific company is put into a specific company group.

In that test we used the ID of the company group, which I inserted by hand, to check that values are correct.

Now, since all dimensions are incrementally populated (SCD 1 or 2), I don't expect IDs to change, so I was confident that I can use them for testing purposes, instead of having to join all the tables to check for the correct values.

When I created a SSIS package to load the values automatically and then I deployed everything in a new enviroment with empty dimension tables, I found this little problem.

As already said, this is a very small bug, but can give you small headaches to understand what's happened, so I filed a connect bug to keep track of it.

That's it :-)

September 30, 2009 12:48 PM
 

Ben Thul said:

I'm not saying that the behavior exhibited by SQL Server is correct, but why not create the table with the identity seed that you want and call it a day?  

October 1, 2009 7:47 AM
 

Davide Mauri said:

@Ben: thanks for the suggestion, but I've already found a workaround. I've posted it on connect too.

October 2, 2009 1:34 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement