how to ALTER COLUMN ?

Hi,
i do a create:
CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL)
and now i want to remove the IDENTITY definition from colum id
using ALTER myTable ALTER COLUMN id ... '
so that column id has definition as when i do a:
CREATE TABLE myTable ( id int NULL)
How can i do this?
thanks, HelmutHelmut
CREATE TABLE Test (col INT NOT NULL IDENTITY(1,1))
GO
insert into Test default values
ALTER TABLE Test ADD col1 INT NULL
GO
ALTER TABLE Test DROP COLUMN col
GO
EXEC sp_rename 'TEST.COL1', 'COL', 'COLUMN'
Note: If you had some data inserted into the table before addin a new column
and need to move it ,so use UPDATE command as
UPDATE Test SET col1=col
"Helmut Woess" <user22@.inode.at> wrote in message
news:dzg357eusfly$.19hvp25nnxfh1.dlg@.40tude.net...
> Hi,
> i do a create:
> CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL)
> and now i want to remove the IDENTITY definition from colum id
> using ALTER myTable ALTER COLUMN id ... '
> so that column id has definition as when i do a:
> CREATE TABLE myTable ( id int NULL)
> How can i do this?
> thanks, Helmut|||I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
to loose an entire table because of that command. The table in question had
17million rows. So be carful with it.
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIFAYrEbGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Helmut
> CREATE TABLE Test (col INT NOT NULL IDENTITY(1,1))
> GO
> insert into Test default values
> ALTER TABLE Test ADD col1 INT NULL
> GO
> ALTER TABLE Test DROP COLUMN col
> GO
> EXEC sp_rename 'TEST.COL1', 'COL', 'COLUMN'
> Note: If you had some data inserted into the table before addin a new
> column and need to move it ,so use UPDATE command as
> UPDATE Test SET col1=col
>
> "Helmut Woess" <user22@.inode.at> wrote in message
> news:dzg357eusfly$.19hvp25nnxfh1.dlg@.40tude.net...
>|||Colin
This stored procedure is documented and supported officialy by MS .

>I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
>to loose an entire table because of that command. The table in question
>had 17million rows. So be carful with it.
I just did some testing on the table that has 50 million rows and it worked
nice. Can you provide a repro where you are loosing an entire of table?
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:vJ15g.61831$wl.42277@.text.news.blueyonder.co.uk...
>I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
>to loose an entire table because of that command. The table in question
>had 17million rows. So be carful with it.
> Regards
> Colin Dawson
> www.cjdawson.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eIFAYrEbGHA.4060@.TK2MSFTNGP02.phx.gbl...
>|||Yes, I know. I was at the MS Labs when the table got lost. Fortunately I
had a backup of the table, so I retried and lost the table again, and a
third time. Bottom line is, that if you try to rename a large table there
is a chance that you will loose it completely.
If you want to take your chances and risk loosing data on a production
system - or more to the point, creating unneeded downtime if you've been
cautious, and sensible enough to backup before hand. Then go ahead.
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uCabo8EbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Colin
> This stored procedure is documented and supported officialy by MS .
>
> I just did some testing on the table that has 50 million rows and it
> worked nice. Can you provide a repro where you are loosing an entire of
> table?
>
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:vJ15g.61831$wl.42277@.text.news.blueyonder.co.uk...
>