October 4, 2011 at 4:52 pm
Hello,
Can someone explain why, once there is a nonclustered index on a column, I cannot reduce the size of that column without first dropping the index?
CREATE TABLE table1 (
col1 NVARCHAR(50) NULL
)
CREATE INDEX idx_index1 ON table1 (col1)
ALTER TABLE table1 ALTER COLUMN col1 NVARCHAR(100) NULL
--Command(s) completed successfully.
ALTER TABLE table1 ALTER COLUMN col1 NVARCHAR(50) NULL
--Msg 5074, Level 16, State 1, Line 2
--The index 'idx_index1' is dependent on column 'col1'.
--Msg 4922, Level 16, State 9, Line 2
--ALTER TABLE ALTER COLUMN col1 failed because one or more objects access this column.
Any insight is greatly appreciated!
October 4, 2011 at 4:56 pm
As the error says, there is something dependant on the column (an index) which has to be dropped first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2011 at 10:00 pm
It's by design. Expanding a column does not require the truncation of data. Call it a safety measure enforced by Microsoft when you reduce the size of a column
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 6, 2011 at 11:59 am
Thanks for the replies, I find it very odd that the error is thrown even when there is no data in the table. Thanks!
October 6, 2011 at 12:05 pm
The index is dependant on the column no matter how much data is in the table. It's just a schema rule.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply