July 22, 2013 at 11:15 pm
Hi friends,
I've a problem in sql server 2012, like i've one table, table name is "tblA".
In that Identity field Name is ID bigint not null identity[1,1].
Now i'm making few records entry like from 1 to 5.
Then i've close the sql server 2012. Then i've restarting the system.
Again i'm making another record entry. Here it should come ID value is 6.
But it has come like
10001.
Then again u make entry its coming correct 10002, 10003,10004 etc...
But i need when u restart the system it should continue with existing example
ID value 5+1=6, should start from 6 but its coming 10001...
Plz help why its coming like this.
July 23, 2013 at 1:15 am
pksutha (7/22/2013)
Hi friends,I've a problem in sql server 2012, like i've one table, table name is "tblA".
In that Identity field Name is ID bigint not null identity[1,1].
Now i'm making few records entry like from 1 to 5.
Then i've close the sql server 2012. Then i've restarting the system.
Again i'm making another record entry. Here it should come ID value is 6.
But it has come like
10001.
Then again u make entry its coming correct 10002, 10003,10004 etc...
But i need when u restart the system it should continue with existing example
ID value 5+1=6, should start from 6 but its coming 10001...
Plz help why its coming like this.
The IDENTITY property doesn't give any garantuee there will be no gaps.
SQL Server 2012 caches some of the next identities that might be inserted, in order to speed up the performance. If you restart the server, this cache will be lost.
Maybe you can try to take a look at sequences, if avoiding gaps is important.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 1:34 am
Koen Verbeeck (7/23/2013)
Maybe you can try to take a look at sequences, if avoiding gaps is important.
Sequences have the same gap-causing properties as identities (identities in SQL 2012 use the Sequence code)
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
July 23, 2013 at 8:57 am
This is a known behavior. I reported it as a bug a while back. Microsoft has supplied a work around. You can enable TraceFlag 272 on your server. There are other workarounds as well. Check out the Connect entry for more information.
"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
July 24, 2013 at 3:56 pm
pksutha (7/22/2013)
Again i'm making another record entry. Here it should come ID value is 6.
No. It should become a value > 5.
If you need consecutive numbers, you should not use IDENTITY. The point with IDENTITY and sequences is that they are non-blocking. That is, one process can get a number although another process that just took a number has not yet committed. This also means that there can be gaps if there is a rollback. And in some cases there can be bigger gaps. Which is perfectly OK, and it is completely beyond me why Microsoft wasted time on this trace flag.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply