identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

identity

Author

Topic

Parmenion
Starting Member

3 Posts

Posted - 2003-07-22 : 05:11:53

I need to remove the identity constraint from a column, using a TSQL script.

All the searches I've done indicate that this can't be done, but I thought I'd post anyway in case anyone knew of a dodgy hack to accomplish this (aside from dropping the column and adding a new one).

For reference, I'm using SQL server 2000, and the column in question is also a primary key.

Any replies welcome.

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-22 : 05:43:01

The roundabout way -


create table moo (yourcol int identity, things varchar(2))
insert into moo values ('hi')

Select * from moo

alter table moo add yourcol2 int  null

update moo set yourcol2 = yourcol


Select * from moo

alter table moo drop column yourcol

Select * from moo

insert into moo values ( 'oo', null)

select * from moo

drop table moo

PS enterprise manager does it by copying the whole table into a new table then renaming it.

-------
Moo. :)

Parmenion
Starting Member

3 Posts

Posted - 2003-07-22 : 05:51:12

this looks like the add/drop column plan.

unfortunatly my table has millions of rows, so doing this and preserving the data is not feasible.

Is there a way to set IDENTITY_INSERT permanently ON for the table, for ever more? 'cause that would be just as good.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-22 : 10:40:37

Ok, millions of rows...sounds like a transaction table...

do you have a natural key?

a datetime add column?

What's it used for?

Why do You want to disable the identity? Is it prob useless?

Brett

8-)

Parmenion
Starting Member

3 Posts

Posted - 2003-07-22 : 11:43:29

quote:

Ok, millions of rows...sounds like a transaction table...

Yes

quote:

do you have a natural key?

Yes. Transaction Id. The current identity column in question.

quote:

a datetime add column?

yep, some of them too but don't worry about them

quote:

What's it used for?

The table? transactions.
The column? primary key.

quote:

Why do You want to disable the identity? Is it prob useless?

For an upgrade: for the new version I need to manage the id manually rather than relying on identity.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-22 : 12:12:58

quote:

For an upgrade: for the new version I need to manage the id manually rather than relying on identity.

How?

I would think creating a new table, without identity, bcp out, bcp in, drop or rename existing, and rename new to old might be your best bet...

but why?

with bigint you can go up to:

BOL

quote:

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

That's a lot of transactions..

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources