Identity Column

Hi,
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
LeilaAdd a column to the table, copy the data from the old column (UPDATE), drop
the old column, rename
the new column to the old column name. Column order will of course not be pr
eserved.
Or create a new table instead.
You can not disable the identity property for an existing column. The tools
does this by creating a
new table (etc.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:e2p8moWDHHA.4928@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Hi,
> How can I disable an integer column from being IDENTITY? I need to do it w
ith TSQL statements, not
> SSMS or EM.
> Thanks in advance,
> Leila
>[/vbcol]|||Check out SET IDENTITY_INSERT in BOL|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy|||> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
I agree! Perhaps there something at the physical level, possibly combined wi
th transaction logging,
which makes this a non-trivial thing to implement?
OTOH, perhaps there haven't been enough wishes at Connect (used to be sqlwis
h@.microsoft.com) to
warrant the work?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.
4ax.com...
> On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
> Roy|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.
4ax.com...
>|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:

>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft SQL Server returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.

>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx