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.com> wrote in
message news:45f57272$0$502$815e3792@news.qwest.net...
>
>
> 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.com> wrote in
message news:45f57272$0$502$815e3792@news.qwest.net...
>
>
> 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.

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