| Author |
Topic |
|
shilpaprele
Starting Member
6 Posts |
 Posted - 2012-05-30 : 05:43:40
|
| In sybase we have identy_gap command to limit the identiy burn outs. What is equivalent comand for this in sql2008 R2? |
|
|
mani_12345
Starting Member
35 Posts |
Posted - 2012-05-30 : 06:35:56
|
| Wht exactly u want to do ? in sql 2008 r2 we have set identity_insert <ur table NAme> on/off |
 |
|
|
shilpaprele
Starting Member
6 Posts |
Posted - 2012-05-30 : 06:38:37
|
| 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) |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 07:20:32
|
| 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. |
|
|
|
shilpaprele
Starting Member
6 Posts |
Posted - 2012-05-30 : 07:34:12
|
| so we should write dbcc checkident in the server's auto run procedure to check and reseed the identity value? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 07:57:53
|
| 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. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-05-30 : 09:06:33
|
| yeah -- explain what you actually need.If you need consecutive numbering then you can either1) Use ROW_NUMBER() window function when SELECTing the data to provide a sequence2) Write a Function to generate a new sequence ID when inserting into the tablemethod (2) is problematic when you have parallelism.Transact CharlieMsg 3903, Level 16, State 1, Line 1736:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION http://nosqlsolution.blogspot.co.uk/ |
|
|
|
shilpaprele
Starting Member
6 Posts |
Posted - 2012-05-30 : 23:42:43
|
| 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? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-31 : 00:34:20
|
| 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 thishttp://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