question

rudrapbiswas avatar image
rudrapbiswas 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
indexrebuildalter-tablerename
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nidheesh.r.pillai avatar image nidheesh.r.pillai commented ·
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?
0 Likes 0 ·
rudrapbiswas avatar image rudrapbiswas commented ·
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.
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
You will have to drop and recreate the index. You have no other choices.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I understand what you want to avoid. I'm just telling you, you can't avoid it.
1 Like 1 ·
rudrapbiswas avatar image rudrapbiswas commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.