ALTER COLUMN & Indexes

• SwedishOrr

  SSCrazy

  Points: 2955

  More actions

  October 4, 2011 at 4:52 pm

  #246071

  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!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 4, 2011 at 4:56 pm

  #1390452

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  October 4, 2011 at 10:00 pm

  #1390488

  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

• SwedishOrr

  SSCrazy

  Points: 2955

  More actions

  October 6, 2011 at 11:59 am

  #1391367

  Thanks for the replies, I find it very odd that the error is thrown even when there is no data in the table. Thanks!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 6, 2011 at 12:05 pm

  #1391373

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass