altering column datatype in a table

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)

altering column datatype in a table

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-10 : 00:58:09

Hi,

I'm trying to run the following code to change a column from INT to TINYINT. Since there are millions of rows I figure its best to make sure I'm using best practice.

ALTER TABLE tblComment ALTER COLUMN deletedByRecipient tinyint

However I get the below errors. Do I have to drop the indexes and re-create them? Is there any way around this ?

Thanks once again
mike123

Server: Msg 5074, Level 16, State 8, Line 1
The index 'tblComment29' is dependent on column 'deletedByRecipient'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'tblComment2121' is dependent on column 'deletedByRecipient'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN deletedByRecipient failed because one or more objects access this column.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 01:20:05

Yes. You need to drop and recreate all those indexes which depends on the column you are altering. Also, make sure changing from Int to TinyInt does not results in loss of the data.

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 04:10:16

"Also, make sure changing from Int to TinyInt does not results in loss of the data"

Must be obvious! but in case not:

SELECT MIN(deletedByRecipient), MAX(deletedByRecipient) FROM tblComment

If the answer is 0 and 1 [and no intention of additional values] you can change it to BIT datatype
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources