in syabse we faced an issue that when server was resatrted before stopping the service there was jump in identity value. But server on its own can control this jump by identity_gap syntax. So if similar sutuation arises in sql 2008, if server can not control it then is there any other way to check and reseed the identity gap. ( automatically when we start the server)
You can use dbcc checkident to check and change the seed. Does it matter that there are gaps? An identity column doesn't enforce consecutive values so there can always be gaps and not just due to a failure.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
If you really think it's needed - you should first ask why it is a problem.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
If you need consecutive numbering then you can either
1) Use ROW_NUMBER() window function when SELECTing the data to provide a sequence 2) Write a Function to generate a new sequence ID when inserting into the table
method (2) is problematic when you have parallelism.
Transact Charlie Msg 3903, Level 16, State 1, Line 1736: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION http://nosqlsolution.blogspot.co.uk/
since i am new to sql server i have not yet faced issue of Identity jump. But based on my experince , we have noticed that there is identity jump issue ( when server machine hard booted without stopping the sybase service).Hence i had asked the question regarding how to control the jump. Such issue not present in sql?
Why would you assume it is? They are very different systems. Try testing it - I don't know about sybase identities but you can't rely on sequential values in SQL server whether or not there's a restart. Read this http://www.simple-talk.com/sql/t-sql-programming/identity-columns/
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
identity gaps
Posted - 05/30/2012 : 05:43:40
