asked
How to rename a column in an index
I have a requirement to rename a column in UI downtime. If I use sp_rename, for normal columns having non clustered indices are getting adjusted with the new column name. But I am facing a problem while renaming a column having filtered index on it. Here are the steps I followed:- 1. In UI Uptime:- I added a duplicate column with the required column property changes (varchar to nvarchar(4000)) 2. UI Uptime:- Added an index on the new column similar to the actual column which is going to be decommissioned. 3. UI Downtime:- Using sp_rename, renaming the currently used column to col_OLD But in this step it failed to execute. The filtered column is causing a problem. It's not getting renamed like normal nonclustered indices. Limitation:- I can't recreate the index during UI uptime. Since the index will be in use and there could be a heavy performance hit. Please help me resolving this. Regards, --Rudra
Hi Rudra, You say you can't recreate the index during UI uptime but you are getting the error in UI Downtime. Is there any problem dropping the index in UI downtime and then renaming?
Hi Nidheesh, There is no problem in dropping the index in downtime, infact we used to drop indices in downtime only and rename a column. But here the problem lies in the index having a filtered column. This column has to be altered/renamed.
You will have to drop and recreate the index. You have no other choices.
I understand what you want to avoid. I'm just telling you, you can't avoid it.
Thanks Grant. Actually, I wanted to avoid index creation in uptime. Since it requires time to create an index and the queries already using it will be affected in this duration.
15 People are following this question.
