January 9, 2015 at 4:49 am
Hello,
We just switched from Sql server 2008R2 to Sql server 2012.I am facing one problem with identity Columns
"When ever i restarts my sql server,the seed value for each identity column is increased by 1000 (For int identity column it is 1000 and for big int it is 10000).
"For Example if seed value of any table was 3 then after restarting sql server will be 1003 if i again restart sql server it will be 2003 and so on."
After searching on google i found that it is a new feature (don't know what is use of it) in sql server 2012 and having only two solution if you want old identity concept
1. Use sequence object -
It is not possible for me because
a) I am using same database in sql server 2008 and 2012 both so can't use sequence in 2008.
b) if i go with sequence then need not change save procedure for each table,which is bulky task for us.
2. Use Trace Flag 272 (-T272)
I can go with this solution because there is need not do any changes in my application.Some one suggested me that add -T272 in startup parameter,after this sql server identity column will work normal as previous version.I did the same but it is not working.
I don't want to do any changes in my database structure.
1.So plz suggest me any solution for it
2. Can any one suggest me that how to use this -T272 or why it is not working.
I don't want to use this new identity feature how to suppress it. Why -T272 is not working
January 9, 2015 at 6:29 am
Yep. It's a known issue and, according to Microsoft, by design. There are a few things you can use to try do deal with it at the link.
"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
January 9, 2015 at 6:55 am
1) The reason for it is performance
2) Did you make the trace flag a startup parameter?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 9, 2015 at 7:29 am
I rarely restart my SQL Servers. I wish I could restart them more often (apply patches and the like). In my case it wouldn't be an issue. How often are you restarting your servers?
Why are gaps so important in your implementation? Identy columns are surrogate keys, so they should be hidden from end users and their values should not matter at all. If you're exposing your surrogate keys, you're looking for trouble.
That said, one thing you could do is add a startup stored procedure or a SQL Server Agent job that runs on server startup which reseeds the identity columns that matter for you.
-- Gianluca Sartori
January 9, 2015 at 12:21 pm
+1 for Spaggeties observation.
The actual value of an identity column should be irrelevant as it should never be presented to the business - it only ensures uniqueness and referential integrity.
The only reason I can see for a break in numbers causing an issue would be if you have some sort of Sarbanes Oxley audit where you have to be able to prove that you have every transaction by proving no breaks in the numbers. If that is the case then I would suggest that server restarts are part of that audit trail and you can prove that there is a perfect 1000 missing numbers (actually 999 if it goes from 3 to 1003) in each table at the restart.
DO NOT DO NOT DO NOT copy data from non production databases to production environments with their identity columns intact - it will eventually bite you big time!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply