Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-28 : 07:03:54
|
| karthik writes "HI, WHEN I EXECUTE THE INSERT FUNCTION IN BEGIN TRAN AND ROLLBACK TRAN, HAVING THE IDENTITY COLUMN IN THE INSERTED TABLE THE VALUE OF THE IDENTITY COLUMN GETS INCREMENTED . IS THERE ANY POSSIBILITY HAVING THE SAME IDENTITY VALUE AFTER THE TRANSACTION BEEN ROLLED BACK??EXAMPLE:create table empl(col1 int identity(1,1),col2 char(5))begin traninsert into empl(col1,col2) values ('d')rollback tranin the above example the value of COL1 gets incremented even the transaction been rolled back.. is any possibility to have same identity value??RegardsKarthik" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-28 : 07:06:53
|
| You'd have to get the current seed at the beginning of the transaction, then use DBCC CHECKIDENT to reset it after the transaction rolls back. However, this won't work if you have two or more instances of this code running. And so what if the identity is incremented? Gaps are practically unavoidable with identity. It's not worth the hassle trying to preserve a perfectly sequential (and meaningless) number in your table. |
 |
|
|
|
|
|
IDENTITY COLUMN IN BEGIN TRAN ROLLBACK TRAN