January 15, 2015 at 4:12 am
Hey,
Since SQL2012 MS changed something with the identity column; after a restart the value jumps with 1000 (for INT identity column). This is an undesired "feature" for us.
One solution I have read is using the startup parameter -T272. Is this a good solution?
What is the best way to overcome this?
Thanks,
January 15, 2015 at 4:31 am
Has the column IDENTITY() definition changed in the table's DDL?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 15, 2015 at 4:40 am
It's a known issue.
The TF resolves it, but IMHO a gap or jump in identity column should not be a big deal. Is this really an issue for you?
If you are exposing your identity value to the end users, you're looking for trouble. Identity is a surrogate key, and as a surrogate it should never be shown to end users in any way. Use business (natural) keys for this.
-- Gianluca Sartori
January 15, 2015 at 4:54 am
Raymond van Laake (1/15/2015)
Hey,Since SQL2012 MS changed something with the identity column; after a restart the value jumps with 1000 (for INT identity column). This is an undesired "feature" for us.
One solution I have read is using the startup parameter -T272. Is this a good solution?
I guess that has to do with SQL Server putting identity values in cache.
You cannot expect from IDENTITY to not have gaps. It can happen.
For example, if you start a transaction that inserts data into the table but it rolls back, then there will be a gap in the sequence.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2015 at 5:16 am
Thx for the replies. I understand that there can be gaps indeed, but suddenly a gap of 1000? It was never an issue before in SQL, it started in SQL2012. And why 1000, why not 1250 or 833? It seems so arbitrary.
We use the auto increment field in several ways, amongst others:
* New employees get a employee number. It was never a problem if a number was skipped, but now my customers are confronted with the 1000 gap.
* We have dynamic tables, when a new campaign is created a table like C000123 is created based on the auto increment field . So, the system can hold up to one million campaigns. Or 1000 restarts of SQL (that causes a 1000-gap).....
You can argue that there are better ways to design the above solutions. But it has served our needs for years and worked well until SQL2012. The auto increment field has a seed an an increment, but what use does that have if MS self doesn't obey that? I have no doubt that MS implemented the "feature" 1000-gap for a reason, but it does put me in an uncomfortable situation.
Still the question remains: is the -T272 startup parameter a good option to go by?
Thansk
January 15, 2015 at 6:03 am
There are a number of workarounds posted here at the Connect item. There's also a discussion around why it's occurring. Short answer, it's because they actually preallocate identity values as a speed boost. There's discussion around it, just scroll down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2017 at 8:34 pm
Short answer is that microsoft programmers do not care if they break users applications or not. Yes break. If You have sequence with 99% numbers and 1% of holes and all of sudden the sequence starts to contain 2% numbers and 98% holes, all optimalizations on code using this sequence go to hell. The sequence can get restored on server startup, You can even write script to do so, there is NO reason not to do that. But they do not care and they would not bother to do that.
March 6, 2017 at 9:12 pm
spaghettidba - Thursday, January 15, 2015 4:40 AMIt's a known issue. The TF resolves it, but IMHO a gap or jump in identity column should not be a big deal. Is this really an issue for you? If you are exposing your identity value to the end users, you're looking for trouble. Identity is a surrogate key, and as a surrogate it should never be shown to end users in any way. Use business (natural) keys for this.
I have to challenge that advice, ol' friend. What "natural" key would you use for a Customer, Employee table, or Invoice table, just to name a few?
And, yeah, I know it's an old post but it was just necro'd and this is the first time I've seen it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2017 at 12:15 am
Jeff Moden - Monday, March 6, 2017 9:12 PMspaghettidba - Thursday, January 15, 2015 4:40 AMIt's a known issue. The TF resolves it, but IMHO a gap or jump in identity column should not be a big deal. Is this really an issue for you? If you are exposing your identity value to the end users, you're looking for trouble. Identity is a surrogate key, and as a surrogate it should never be shown to end users in any way. Use business (natural) keys for this.I have to challenge that advice, ol' friend. What "natural" key would you use for a Customer, Employee table, or Invoice table, just to name a few?
And, yeah, I know it's an old post but it was just necro'd and this is the first time I've seen it.
Customer Nbr, Employee Nbr and Invoice Nbr (especially the last one must exist and must be unique otherwise the accounting department isn't doing their job).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 7, 2017 at 12:31 am
Jeff Moden - Monday, March 6, 2017 9:12 PMI have to challenge that advice, ol' friend. What "natural" key would you use for a Customer, Employee table, or Invoice table, just to name a few?
He's not saying to use natural keys as the primary key, but to not to expose the identity column to users and have them attach meaning to it.
So what do I show on the customer screen? What my client wants to see, what's meaningful to them (not the auto-generated meaningless number SQL generates)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2017 at 2:15 am
Jeff Moden - Monday, March 6, 2017 9:12 PMspaghettidba - Thursday, January 15, 2015 4:40 AMIt's a known issue. The TF resolves it, but IMHO a gap or jump in identity column should not be a big deal. Is this really an issue for you? If you are exposing your identity value to the end users, you're looking for trouble. Identity is a surrogate key, and as a surrogate it should never be shown to end users in any way. Use business (natural) keys for this.I have to challenge that advice, ol' friend. What "natural" key would you use for a Customer, Employee table, or Invoice table, just to name a few?
And, yeah, I know it's an old post but it was just necro'd and this is the first time I've seen it.
You're still going to want some kind of constraint on the table on top of an identity column (presumably as the pk) to ensure business level uniqueness. This is regardless of if you expose an identity to users or not. Even if you do expose those numbers to the users, does it matter if there are gaps?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2017 at 2:27 am
For me it matters that there are gaps.... in our setup the identity is used as the employee number, and the employees use it to log in. Because MS suddenly introduced gaps I get questions from my customers what is going on. So it is not a showstopper, but an ugly "feature". It would have been nice if MS had come up with someting else.
March 7, 2017 at 2:34 am
Raymond van Laake - Tuesday, March 7, 2017 2:27 AMFor me it matters that there are gaps.... in our setup the identity is used as the employee number, and the employees use it to log in. Because MS suddenly introduced gaps I get questions from my customers what is going on. So it is not a showstopper, but an ugly "feature". It would have been nice if MS had come up with someting else.
The didn't 'suddenly introduce gaps', there have always been gaps in identities, they've just been small.
There are manual ways to do gap-less sequences, they just tend to be more expensive because ensuring no gaps and no duplicates requires locks and concurrency checks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2017 at 2:52 am
Raymond van Laake - Tuesday, March 7, 2017 2:27 AMFor me it matters that there are gaps.... in our setup the identity is used as the employee number, and the employees use it to log in. Because MS suddenly introduced gaps I get questions from my customers what is going on. So it is not a showstopper, but an ugly "feature". It would have been nice if MS had come up with someting else.
I still don't see why that's a problem. Indeed, I'd go as far as to say that having gaps is desirable, since you probably don't want employee numbers to be guessable.
John
March 7, 2017 at 6:29 am
Jumping into the fray here, one of my customers came to me a while back with this issue. His end users do attach significance to the identity key (which is exposed in the application to them,) and they get rather upset when the number jumps significantly.
Small gaps, such as from rollbacks don't bother them, but suddenly going from 4567 to 5567 gets them upset.
We worked around the issue initially with a manual process (reseed the identity use dbcc checkident) which has since been automated with an Agent job (with the customers understanding that if the SQL Agent service restarts but SQL doesn't, it could cause problems.)
Not the best solution, but a workable solution that doesn't involve massive re-writes of the application.
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply