switching off the identity on a column

Question

switching off the identity on a column

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

I get the same error, can't find in BOL why the syntax error. Anyway, what about this:



-- First add an identity column...

ALTER TABLE MyTable

ADD MyNewIdentityColumn INT NOT NULL IDENTITY(1,1)

GO

-- Next, throw the old values into the new identity column

UPDATE MyTable SET MyNewIdentityColumn = MyOldIntColumn

GO

-- Next, drop old column

ALTER TABLE MyTable

DROP COLUMN MyOldIntColumn

GO

Edited by - jpipes on 06/17/2003 08:14:25 AM

Tomputor SSC Enthusiast Points: 159 More actions · Answer 2

Tomputor

SSC Enthusiast

Points: 159

June 17, 2003 at 8:17 am

#461755

Thanks Jay,

It seems there is no easy way.

Tom

jpipes SSChampion Points: 12230 More actions · Answer 3

As a final note, you may want to go into Enterprise Manager and change the table in the Design View, then click Save Script button and see what SQL internally thinks is the best method for handling this...Sometimes this technique is useful in figuring out some of the trickier DDL constructs...

Good luck!

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 4

Ok, I went back to BOL and looked at the syntax.

The IDENTITY option is only available if you are adding a column. ALTER COLUMN allows you to add or drop ROWGUIDCOL but not IDENTITY.

EM does it by creating a new table with the IDENTITY property, copying the data to it, dropping the old table, and renaming the new table. That's what your script will probably reveal.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley