SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 altering column datatype in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/10/2006 :  00:58:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/10/2006 :  01:20:05  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/10/2006 :  04:10:16  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000