SET IDENTITY_INSERT little bug(?)

manowar — Wed, 30 Sep 2009 05:52:22 GMT

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

Search results matching tags 't-sql', 'Business Intelligence', and 'SQL Server'

SET IDENTITY_INSERT little bug(?)