SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 identity gaps
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpaprele
Starting Member

6 Posts

Posted - 05/30/2012 :  05:43:40  Show Profile  Reply with Quote
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

India
35 Posts

Posted - 05/30/2012 :  06:35:56  Show Profile  Reply with Quote
Wht exactly u want to do ?
in sql 2008 r2 we have set identity_insert <ur table NAme> on/off
Go to Top of Page

shilpaprele
Starting Member

6 Posts

Posted - 05/30/2012 :  06:38:37  Show Profile  Reply with Quote
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)
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 05/30/2012 :  07:20:32  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

shilpaprele
Starting Member

6 Posts

Posted - 05/30/2012 :  07:34:12  Show Profile  Reply with Quote
so we should write dbcc checkident in the server's auto run procedure to check and reseed the identity value?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 05/30/2012 :  07:57:53  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/30/2012 :  09:06:33  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
yeah -- explain what you actually need.

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/
Go to Top of Page

shilpaprele
Starting Member

6 Posts

Posted - 05/30/2012 :  23:42:43  Show Profile  Reply with Quote
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?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 05/31/2012 :  00:34:20  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000