-1

I want to change column of table that table has more than 5 millions records. this column related to a few indexes and I have to drop these indexes and rename column and after that recreate indexes. this process take to much time. almost 1:30 hour. does have better solution to rename that column with better performance?

Mousa Ghajar Najafi
  • 29
  • 3
  • 1
    Most of the answers to [this old SO question](https://stackoverflow.com/q/16296622/4003419) seem to suggest [sp_rename](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql). But I have no idea about how good it is with renaming a column used in several indexes. – LukStorms Mar 07 '22 at 09:34
  • sp_rename will indeed handle columns used in indexes. – Stu Mar 07 '22 at 09:38
  • `sp_rename` will handle indexes and (foreign key) constraints fine. It won't affect other objects that reference the column though, such as `VIEW`s, `PROCEDURE`s, `FUNCTION`s, etc. – Thom A Mar 07 '22 at 09:45
  • @Stu unfortunately not. sp_rename return error coz of indexes – Mousa Ghajar Najafi Mar 07 '22 at 09:47
  • What error are you getting, Mousa? Show us what you've *actually* tried. For a "simple" set up, `sp_rename` works fine: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=039657920d4cc677cc2082e3afefffc8). – Thom A Mar 07 '22 at 09:49
  • @Larnu error : Object 'dbo.tablename.column' cannot be renamed because the object participates in enforced dependencies. – Mousa Ghajar Najafi Mar 07 '22 at 09:59
  • Do you have calculated columns that reference the column, @MousaGhajarNajafi ? If so, you'll need to `DROP` those first and recreate them; the indexes would be handled fine. – Thom A Mar 07 '22 at 10:01
  • @Larnu yes i have 2 calculated columns. DROP and recreate these columns takes too much time – Mousa Ghajar Najafi Mar 07 '22 at 10:04
  • 2
    Are these computed columns persisted then, @MousaGhajarNajafi ? All this informations should be in your question... The attempt(s) you made, the error(s) you get, etc, etc... – Thom A Mar 07 '22 at 10:07
  • [Here's a small test](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cead9db327c461d701000aaf95d7a4f8) tthat replicates the problem. I guess you first need to drop the computed columns that use the column. If it's not a persisted computed column that should be fast. – LukStorms Mar 07 '22 at 10:30
  • This is a one time change? If so, does it really matter if you can speed it up? – SMor Mar 07 '22 at 12:05

1 Answers1

-1

Create an empty table with the correct definition. Select old data into the table with the correct structure. DROP old table.

Craig Gers
  • 544
  • 3
  • 9