Just noticed this twice in March. Apparently it is designed behaviour. Unfortunately, I use this for an orderID value that gets shown to the customer and suppliers. Has this been noticed as an issue at DASP? There is a flag to turn it off on a server. Does anyone know how to stop this behaviour on my DB only? http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is Thanks Nigel
I am not sure if we can put in the traceflag because it has performance ramification. Have you considered solution 1 described in this article?
This would be a big change that I'm not sure how to do as the column orderID is a primary key to quite a few tables.
Just checked and I would have to drop and recreate my orders table. That would be a nightmare. FrankC, Could the traceflag option be explored a little please?
I have managed to implement solution 1 on a test database so it looks I will go this way. I created a create script and then I dropped my relationships, renamed tblOrder to tblOrderOld. Recreated tblOrders with out the identity, just Int. Copied over the data, recreated the relationships, created the sequence and modified my insert order procedures. Did some testing. Now to download a bak and do the mods for upload and replace db.
