2

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem of index being deleted so i wanted to know is there any database changes of events (SQL commands) that will cause deletion of index.

  • 2
    Can you specify what tool you are using to make these changes? – Shawn Melton Aug 13 '14 at 14:14
  • Maybe it drops a custom named index and recreates it with a default name? – Mihai Aug 13 '14 at 16:24
  • Both SSMS and SSDT will allow you to make changes to a table that will actually require the table be dropped and re-created (not sure about SSDT, but SSMS has an option to allow this under Tools > Options somewhere). Not really a stretch to imagine that a change that could drop / re-create the table would, by extension, re-create all of the indexes. – Aaron Bertrand Aug 13 '14 at 19:46
3

It depends on the method you're using to alter the column. We'll start with a simple table:

CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200));
CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName);

Then use the SQL Server Management table designer to change the CustomerName column to a VARCHAR(200) instead of an NVARCHAR. By default, when you try to save the changes (or even just generate a change script), you get a warning:

SQL Server Management Studio warning

You can override that warning in SSMS by clicking Tools, Options, Designers, Table and Database Designers, and uncheck the box that says "Prevent saving changes that require table re-creation".

This is dangerous, because if you change a table in production, you can end up locking it while SQL Server rebuilds the table from scratch. I really like that Microsoft leaves this option enabled by default.

After disabling it, and trying the edit again, don't save the table - instead, click Table Designer, Generate Change Scripts. You get a new warning about data being lost because you're downsizing datatypes, moving down from a Unicode-friendly datatype (NVARCHAR) to a non-Unicode datatype (VARCHAR). If we ignore that error, we can see the change script. It includes the creation of the new table, moving the data into it, and finally the recreation of all nonclustered indexes:

SSMS recreating the nonclustered index

If you use a different database designer tool, or if you use different datatype changes, you can get different results. For example, if you change a field from a VARCHAR(MAX) to an INT, you'll lose full text indexes on the VARCHAR(MAX) field because you can't full text index a number.

3

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index (but not the other way around).

However, if you use SSMS to change a table, SSMS may drop and re-create the index for you, but it shouldn't drop the index either way.

On a side note, if you have an indexed view, modifying the view till drop any indexes on the view.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.