14

I am writing this a follow up to Changing data type of column in SQL Server

My question earlier was if I need to remove all indexes and constraints and it was answered I do need to remove them.

So as I was surf internet on the topic I came across a few post saying its better to disable and enable an index, rather than removing and recreating them .

So which is better way of doing it? Does disabling of index allow you to change the data type of the column as well? What is the difference between both?

Statement with dropping and creating index

DROP INDEX UX_1_COMPUTATION ON  dbo.Computation 

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) not null

CREATE UNIQUE INDEX UX_1_COMPUTATION ON dbo.Computation (ComputationID);

Statement with disabling and enabling index

ALTER INDEX [UX_1_COMPUTATION ] ON  dbo.Computation DISABLE

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) not null

ALTER INDEX [UX_1_COMPUTATION ] ON dbo.Computation REBUILD;
Community
  • 1
  • 1
Sam
  • 1,298
  • 6
  • 30
  • 65
  • 4
    Have you run your test script? You can also have a look at the documentation, which tells you the [limitations for altering a column](http://msdn.microsoft.com/en-us/library/ms190273.aspx) used in an index and also what [disabling indexes](http://msdn.microsoft.com/en-us/library/ms177406(v=sql.100).aspx) does. Hopefully it's obvious that there is a difference between disabling something and dropping (deleting) it. – Pondlife Dec 18 '12 at 20:12

1 Answers1

8

Does disabling of index allow you to change the data type of the column as well?

It is based on the index type you choosed. You cannot modify a column data type when an clustered index on the table is disabled. If your index is a non clustered index then you can.

What is the difference between both?

The biggest difference between disabling and dropping an index is whether the metadata and statistics are persisted. If disabled, they are. If dropped, they are not. Make sure you carefully weigh your options before performing either action, and always have a way to recreate the index available.

which is better way of doing it?

In your case I would suggest drop and recreate indexes.

user229044
  • 232,980
  • 40
  • 330
  • 338
StackUser
  • 5,370
  • 2
  • 24
  • 44
  • can you give any indication of *why* you'd choose to drop a Non-Clustered index, rather than disable it? – Brondahl May 07 '21 at 08:44