Database events that will cause deletion of index

Question

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.

Answer 1

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:

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:

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.

Answer 2

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.