SET IDENTITY_INSERT little bug(?)

re: SET IDENTITY_INSERT little bug(?)

Paul Nielsen — Wed, 30 Sep 2009 15:53:46 GMT

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

re: SET IDENTITY_INSERT little bug(?)

Adam Machanic — Wed, 30 Sep 2009 16:06:19 GMT

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...

re: SET IDENTITY_INSERT little bug(?)

Davide Mauri — Wed, 30 Sep 2009 16:48:43 GMT

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 :-)

re: SET IDENTITY_INSERT little bug(?)

Ben Thul — Thu, 01 Oct 2009 11:47:36 GMT

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?

re: SET IDENTITY_INSERT little bug(?)

Davide Mauri — Fri, 02 Oct 2009 05:34:58 GMT

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