E-Fix - Please Help: ALTER COLUMN (with pre-existing indexes)

Question

E-Fix - Please Help: ALTER COLUMN (with pre-existing indexes)

the__t

Hall of Fame

Points: 3260
More actions
February 10, 2009 at 8:29 am

#205314

I have a request to alter the properties of several columns in a table, but there are several existing indexes that currently depend on those columns, so the ALTER TABLE...ALTER COLUMN statements are failing. So, I have a few questions:
1) Do I need to (or should I) DROP the indexes, execute the ALTER TABLE...ALTER COLUMN statements, and then CREATE the indexes again?
2) When I change the columns, there are also some existing views that reference those columns. Are views updated automatically, or do they need to be re-defined? From the syntax of the CREATE VIEW, it does not appear that I need to do anything to the existing views.
3) I've drilled down on the table in SSMS and I see no KEYS, no CONSTRAINTS, no TRIGGERS. Is there anything else I should be concerned with? This is a production table, but the database is far too large to backup in a timely manner (this is an emergency fix), so I was going to script the table design, the indexes are already scritped, and I already selected all of the table data to a spreadsheet.
4) Do I need to (or should I) UPDATE STATS or UPDATE USAGE after the change to the columns is complete and the indexes are recreated (assuming #1 is even correct)?
"Got no time for the jibba jabba!"
-B.A. Baracus

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

the__t (2/10/2009)
I have a request to alter the properties of several columns in a table, but there are several existing indexes that currently depend on those columns, so the ALTER TABLE...ALTER COLUMN statements are failing. So, I have a few questions:
1) Do I need to (or should I) DROP the indexes, execute the ALTER TABLE...ALTER COLUMN statements, and then CREATE the indexes again?

This is the only way that I would try and do it.

2) When I change the columns, there are also some existing views that reference those columns. Are views updated automatically, or do they need to be re-defined? From the syntax of the CREATE VIEW, it does not appear that I need to do anything to the existing views.

The views will not be automagically corrected. You will need to rebuild the views to make them update their definitions.

3) I've drilled down on the table in SSMS and I see no KEYS, no CONSTRAINTS, no TRIGGERS. Is there anything else I should be concerned with? This is a production table, but the database is far too large to backup in a timely manner (this is an emergency fix), so I was going to script the table design, the indexes are already scritped, and I already selected all of the table data to a spreadsheet.

Why not use a SSIS package and just copy this table and its constraints off to another database to test with. If you can copy the data to a spreadsheet you should be able to do this.

4) Do I need to (or should I) UPDATE STATS or UPDATE USAGE after the change to the columns is complete and the indexes are recreated (assuming #1 is even correct)?

No, the statistics will be updated when you rebuild the indexes. Updating usage is good if you want to run sp_spaceused to know how big the table is afterwards.

the__t Hall of Fame Points: 3260 More actions · Answer 2

Jonathan Kehayias (2/10/2009)
3) I've drilled down on the table in SSMS and I see no KEYS, no CONSTRAINTS, no TRIGGERS. Is there anything else I should be concerned with? This is a production table, but the database is far too large to backup in a timely manner (this is an emergency fix), so I was going to script the table design, the indexes are already scritped, and I already selected all of the table data to a spreadsheet.
Why not use a SSIS package and just copy this table and its constraints off to another database to test with. If you can copy the data to a spreadsheet you should be able to do this.

I actually forgot about a very simple solution, which was to just create a copy of the table structure as a new table, and then SELECT INTO that new table, so that I have a readily-available backout (by renaming the new table back to the old name).

"Got no time for the jibba jabba!"
-B.A. Baracus

the__t Hall of Fame Points: 3260 More actions · Answer 3

Everything worked like a charm, thanks for the assistance.

"Got no time for the jibba jabba!"
-B.A. Baracus

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Good to hear it worked out for you.