THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform


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)

instead of having the correct sequence

0 (Forced Identity Value)

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:

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



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


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement