sql server (alternate):

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?

"Marie-Christine Bechara" <marie-christine.bechara@ifsal.com> wrote in
message news:45f57272$0$502$815e3792@news.qwest.net...
[quoted text, click to view]

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...
[quoted text, click to view]

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.

[quoted text, click to view]