• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

not able to rearrange the columns if it had the ALTER TABLE INDEX

when a table has the below kind of index (with a WHERE), then it does not allow me to rearrange the order of columns, unless i drop the index first. is there a way to disable this need?

CREATE UNIQUE NONCLUSTERED INDEX ix_abc
 ON tablename (Columname) WHERE Columname IS NOT NULL
0
25112
Asked:
25112
1 Solution
 
Randy PooleCommented:
why do you care what order the columns are in?
0
 
Scott PletcherSenior DBACommented:
SQL does not have a direct method of adding a column in a given location in the table.  SSMS (SQL's gui) adds a column in a given location, but it does it by copying the data out, recreating the table (essentially), and copying the data back in.  Given that, yes, there are index restrictions, but you're much better off dropping such indexes before that and re-creating them afterward anyway with all that data copying going on.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now