Hi,

I have a table with a column of datatype INT with IDENTITY(1, 1) in my SQL Server DB. when i am trying to insert a value into this table, some of the related tables violated constraints. so, that transaction with insert statement is rollbacked. when i am trying to insert a another record into the table, record is inserted but, while identity column value is caluculated by considering the rollbacked records also.

I want not to consider the rollback values while inserting the new values. Pls suggest me.

thanks in adv,

Identity values are kept in memory and are not subject to ROLLBACKs.

Does it matter to you if there are gaps in the identities?

Hello,

      Unfortunately SQL Server doesn't refill the gaps in identity values automatically. The gaps can happen due to rollbacked transactions, deletes.

      However if you are serious about the filling the gaps of the identity column, you can do this way to fill the gaps of identity. You need to ensure that this refilling will not break the referential integrity to other tables, which is already in place.

      01. Take all the rows into a temporary table

      02. Select <column list excluding the id column> into #tmptbl from <actual table>

      03. Truncate table <actual tablename>

      04. DBCC CHECKIDENT (<Actual tablename>,reseed,<seed intial value>)

      05. insert into <actual table> <columns other than id column> select * from #tmptbl

            After this you can see all the identity values in serial.

           Preferably use physical table instead of temporary table with some prefix, as the temporary tables are not persistent objcts between the startups.

Narchand