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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy