Reducing size of datatype involved in index fails where as increasing size work This question is related to my previous question Database events that will cause deletion of index

For example if i create a table like

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


And then try to change column datatype like shown below to 230 from 200.

alter table Customers 
alter column CustomerName nvarchar(230) null


The above alter command runs successfully and index IX_CustomerName is not dropped.

But if i try to reduce the size of column to 150 from 200 then it shows error.

alter table Customers 
alter column CustomerName nvarchar(150) null


I get error like

Msg 5074, Level 16, State 1, Line 1
The index 'IX_CustomerName' is dependent on column 'CustomerName'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CustomerName failed because one or more objects access this column.


So reducing the size(data may loss) of a column does not work if index is present on a column where as if the index not present then above query works successfully.
So why does it does not allow reducing the column when index present where as increasing size does not show any problem and works?
Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length").  As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index).  The SQL engine is smart enough to know that and doesn't do anything to either, other than notate that the max size has changed.

On the other hand - if you were to try your script using fixed-length data types (i.e. CHAR of CHAR), any change up or down will change how the content is stored (since the full length is pre-allocated).  This would force the table to rewrite (and the index would need to reflect the change as well).

If you run that very script you have above but with NCHAR, the reported error would pop up during the INCREASE as well.