is it possible to alter a table to remove IDENTITY attribute

We have a column with an IDENTITY attribute and we no longer want this
column to have the IDENTITY attribute.
I know that set IDENTITY_INSERT will more or less ignore the IDENTITY for
that unit of work however I need the IDENTITY attribute totally removed so
that we don't have to set IDENTITY_INSERT each time.
Thanks in advanceYou can add a new column, copy the contents of the identity column to it,
drop the identity column, and rename the new column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"TJT" <TJT@.nospam.com> wrote in message
news:egxy3DYXGHA.1200@.TK2MSFTNGP03.phx.gbl...
We have a column with an IDENTITY attribute and we no longer want this
column to have the IDENTITY attribute.
I know that set IDENTITY_INSERT will more or less ignore the IDENTITY for
that unit of work however I need the IDENTITY attribute totally removed so
that we don't have to set IDENTITY_INSERT each time.
Thanks in advance

is it possible to alter a table to remove IDENTITY attribute

We have a column with an IDENTITY attribute and we no longer want this
column to have the IDENTITY attribute.
I know that set IDENTITY_INSERT will more or less ignore the IDENTITY for
that unit of work however I need the IDENTITY attribute totally removed so
that we don't have to set IDENTITY_INSERT each time.
Thanks in advanceYou can add a new column, copy the contents of the identity column to it,
drop the identity column, and rename the new column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"TJT" <TJT@.nospam.com> wrote in message
news:egxy3DYXGHA.1200@.TK2MSFTNGP03.phx.gbl...
We have a column with an IDENTITY attribute and we no longer want this
column to have the IDENTITY attribute.
I know that set IDENTITY_INSERT will more or less ignore the IDENTITY for
that unit of work however I need the IDENTITY attribute totally removed so
that we don't have to set IDENTITY_INSERT each time.
Thanks in advance

Is IDENTITY_INSERT OFF safe?

Say I have a sproc that is occasionally used to restore deleted records to a
table.
--If I use
SET IDENTITY_INSERT myTable ON
--Then insert the records...
INSERT INTO myTable(ID, AnyField)
VALUES (2,'hello);
--Then I return table to original state
SET IDENTITY_INSERT myTable OFF
During that process, what would happen if another user was attempting to
insert records into the same table?
Even if my transaction only takes .2 milliseconds, is it possible another
user will get an error, or is SQL Server smart enough to delay their
transaction(s) [lock the table] until mine is complete?
Thanks,
ChrisOther updaters are not blocked by SET IDENTITY_INSERT ON. But this is not a
problem since the IDENTITY_INSERT ON applies only to the connection that
runs it. The identity column is handled normally in all other connections
(unless, of course, that connection has also set IDENTITY_INSERT ON.
So, yes, it is safe to use.
Tom
"Chris" <rooster575@.hotmail.com> wrote in message
news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Say I have a sproc that is occasionally used to restore deleted records to
> a table.
> --If I use
> SET IDENTITY_INSERT myTable ON
> --Then insert the records...
> INSERT INTO myTable(ID, AnyField)
> VALUES (2,'hello);
> --Then I return table to original state
> SET IDENTITY_INSERT myTable OFF
> During that process, what would happen if another user was attempting to
> insert records into the same table?
> Even if my transaction only takes .2 milliseconds, is it possible another
> user will get an error, or is SQL Server smart enough to delay their
> transaction(s) [lock the table] until mine is complete?
> Thanks,
> Chris
>|||Thanks Tom!
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:WsmdnSkiXZ4uqBvZnZ2dnUVZ_vqdnZ2d@.co
mcast.com...
> Other updaters are not blocked by SET IDENTITY_INSERT ON. But this is not
> a problem since the IDENTITY_INSERT ON applies only to the connection that
> runs it. The identity column is handled normally in all other connections
> (unless, of course, that connection has also set IDENTITY_INSERT ON.
> So, yes, it is safe to use.
> Tom
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>|||ACID
Isolation