Identity reused problem

I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?

How can I resolve this issue?

*** Sent via Developersdex http://www.developersdex.com ***"Marie-Christine Bechara" <marie-christine.bechara@.ifsal.comwrote in
message news:45f57272$0$502$815e3792@.news.qwest.net...

Quote:

Originally Posted by

>
>
I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?
>
How can I resolve this issue?
>
*** Sent via Developersdex http://www.developersdex.com ***

An Identity is always an increasing value unless you do a DBCC CHECKIDENT
call.

So already it avoids doing this.

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||"Marie-Christine Bechara" <marie-christine.bechara@.ifsal.comwrote in
message news:45f57272$0$502$815e3792@.news.qwest.net...

Quote:

Originally Posted by

>
>
I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?

There is no foolproof way since SET IDENTITY INSERT ON will allow reuse.

You could leave the records in place, using a deleted flag column to
indicate a logical deletion.

You could use an insert trigger to intercept id's that are below the max(id)
value.

You could use stored procedures and permissions to block the usage of the
identity insert.

Quote:

Originally Posted by

>
How can I resolve this issue?
>
*** Sent via Developersdex http://www.developersdex.com ***