change datatype of a column having clustered index

Question

Post reply

change datatype of a column having clustered index

rpatil22

Ten Centuries

Points: 1371
More actions
April 13, 2010 at 7:00 am

#217747

I want to change datatype of a column having clustered index.
ALTER TABLE ProdMachine
ALTER COLUMN MachineID Varchar(10) NOT NULL
error -
Msg 5074, Level 16, State 1, Line 1
The index 'IX_Clust_ID' is dependent on column 'MachineID'
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN MachineID failed because one or more objects access this column.

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

You will have to:

1. drop any foreign keys in other tables referencing this index if applicable.

2. drop the index

3. change the column

4. rebuild the index

5. reinstate any foreign keys that you dropped.

Note that you pose the risk of adding data that violates referential integrity if you need to drop the FK. This should only be done during a maintenance period when all users are out of the system.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 2

Option B is to wrap everything in a big transaction that holds a table exclusive lock and create a 'new' table with the indexes and data types you want as an end state and insert from the old table (again all in one big transaction)

then rename the 'old' table and rename the new table to the name of the old table.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

rpatil22 (4/13/2010)
I want to change datatype of a column having clustered index.
ALTER TABLE ProdMachine
ALTER COLUMN MachineID Varchar(10) NOT NULL
error -
Msg 5074, Level 16, State 1, Line 1
The index 'IX_Clust_ID' is dependent on column 'MachineID'
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN MachineID failed because one or more objects access this column.

Use SSMS in the "Design" mode... it'll take care of all that for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)