November 4, 2014 at 12:07 am
This question is related to my question in SO 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?
November 4, 2014 at 7:50 am
To the best of my knowledge, expanding the size of a variable length field is a metadata-only operation (the table doesn't get rewritten, therefore the indexes don't have to be dropped and recreated). Reducing the column length DOES however trigger an action that requires the table to be dropped and rewritten and thus the index will ALSO have to be dropped and rewritten.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 4, 2014 at 11:27 am
To confirm this - this behavior only works if you use *variable length* data types. You would not be able to get away with that if you were using NCHAR instead of NVARCHAR (you'd get the same error as during the shrinking of the data length). Fixed length data type would cause a drop and recreate of the table regardless of which way you change the data type.
CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NCHAR(200));
CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName);
go
alter table Customers
alter column CustomerName nchar(300) null --this causes an error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply