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?