IDENTITY_INSERT

Is there a way in MSSQL2005 to allow "IDENTITY_INSERT" all the time, e.g. as
a server option or database property? Right now SET IDENTITY_INSERT ON/OFF
has to be issued if a hard coded identity column value is to be inserted.
This is really minor comparing to other compatibility issues. Thanks.Why would you want to do this? It would defeat the purpose of the identity p
roperty. If you don't
want SQL server to generate a value for the column, just don't set the ident
ity property...
The short answer is no, you can't do this.
Also, I don't see how you refer to this as a compatibility issue. In what wa
y would you say that the
behavior has changed. All the way since 6.0 (when identity was introduced),
only a table owner could
specify a value for the identity column and a requirement is that the sessio
n had SET
IDENTITY_INSERT ON for the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mason" <masonliu@.msn.com> wrote in message news:uO6wfMUQGHA.1676@.TK2MSFTNGP14.phx.gbl...[c
olor=darkred]
> Is there a way in MSSQL2005 to allow "IDENTITY_INSERT" all the time, e.g.
as a server option or
> database property? Right now SET IDENTITY_INSERT ON/OFF has to be issued i
f a hard coded identity
> column value is to be inserted. This is really minor comparing to other co
mpatibility issues.
> Thanks.
>[/color]|||Why don't you make the column an int instead (without an identity)
doesn't this accomplish the same?
Put a unique constraint on the column to ensure that you won't have
duplicate values
http://sqlservercode.blogspot.com/|||That's a fair question. We have a legacy database. It contains two or more
tables that accept data from several sources, some of which use the identity
feature and other don't. To minimize app changes and efforts to locate them,
I like to confirm first that MSSQL2005 does not have such an option since I
couldn't find it in online book, etc. As I expected, this particular
flexibility is not offerred by MSSQL. Thanks.
My "compatibility" was not meant for backward compatibility, but to indicate
the difference between MSSQL and other DBMSes.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebheNXUQGHA.740@.TK2MSFTNGP12.phx.gbl...
> Why would you want to do this? It would defeat the purpose of the identity
> property. If you don't want SQL server to generate a value for the column,
> just don't set the identity property...
> The short answer is no, you can't do this.
> Also, I don't see how you refer to this as a compatibility issue. In what
> way would you say that the behavior has changed. All the way since 6.0
> (when identity was introduced), only a table owner could specify a value
> for the identity column and a requirement is that the session had SET
> IDENTITY_INSERT ON for the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mason" <masonliu@.msn.com> wrote in message
> news:uO6wfMUQGHA.1676@.TK2MSFTNGP14.phx.gbl...|||I could, then some apps that rely on the identity feature will have to be
updated as well. Since what I am looking for is not available, I will have
some of the apps updated to conform with the identity designation. Thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141667501.059206.78420@.u72g2000cwu.googlegroups.com...
> Why don't you make the column an int instead (without an identity)
> doesn't this accomplish the same?
> Put a unique constraint on the column to ensure that you won't have
> duplicate values
> http://sqlservercode.blogspot.com/|||Ok, I can see where you are coming from... Unfortunately, you don't have muc
h options regarding SQL
Server here. You can't set this at any global level, has to be done at conne
ction level and you have
to be table owner or higher to set this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mason" <masonliu@.msn.com> wrote in message news:%23nfyh4UQGHA.2816@.TK2MSFTNGP15.phx.gbl...

> That's a fair question. We have a legacy database. It contains two or more
tables that accept data
> from several sources, some of which use the identity feature and other don
't. To minimize app
> changes and efforts to locate them, I like to confirm first that MSSQL2005
does not have such an
> option since I couldn't find it in online book, etc. As I expected, this p
articular flexibility is
> not offerred by MSSQL. Thanks.
> My "compatibility" was not meant for backward compatibility, but to indica
te the difference
> between MSSQL and other DBMSes.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ebheNXUQGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||To make it clear, the database was duplicatd into MSSQL2005 a w or two
ago to meet new customer requests. We used MSSQL2000 a while back for a tiny
isolated project.
"mason" <masonliu@.msn.com> wrote in message
news:%23nfyh4UQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> That's a fair question. We have a legacy database. It contains two or more
> tables that accept data from several sources, some of which use the
> identity feature and other don't. To minimize app changes and efforts to
> locate them, I like to confirm first that MSSQL2005 does not have such an
> option since I couldn't find it in online book, etc. As I expected, this
> particular flexibility is not offerred by MSSQL. Thanks.
> My "compatibility" was not meant for backward compatibility, but to
> indicate the difference between MSSQL and other DBMSes.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ebheNXUQGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||The problem that you will face is that when you have SET
IDENTITY_INSERT ON all the apps will fail that rely on the identity
value being auto generated (if they try to insert before you run a SET
IDENTITY_INSERT OFF statement)
Just keep that in mind
http://sqlservercode.blogspot.com/|||I have not tried this myself, but if you do your updates with stored
procedures, you could try checking for the existence of the identity column
in your parameters and the issuing SET IDENTITY_INSERT ON/OFF within the
stored procedure as needed.
However, as others have pointed out, this tends to defeat the purpose of the
identity column.
"mason" <masonliu@.msn.com> wrote in message
news:uO6wfMUQGHA.1676@.TK2MSFTNGP14.phx.gbl...
> Is there a way in MSSQL2005 to allow "IDENTITY_INSERT" all the time, e.g.
as
> a server option or database property? Right now SET IDENTITY_INSERT ON/OFF
> has to be issued if a hard coded identity column value is to be inserted.
> This is really minor comparing to other compatibility issues. Thanks.
>|||Thanks. I will try not to play with this on/off switch.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141671850.478649.112510@.j52g2000cwj.googlegroups.com...
> The problem that you will face is that when you have SET
> IDENTITY_INSERT ON all the apps will fail that rely on the identity
> value being auto generated (if they try to insert before you run a SET
> IDENTITY_INSERT OFF statement)
> Just keep that in mind
> http://sqlservercode.blogspot.com/