Resizing a VARCHAR/NVARCHAR column with indexes

  • I discovered something interesting today while trying to resize a VARCHAR column. Resizing fails with the following error:
    The index 'IX:MyIndex' is dependent on column 'MyVarcharColumn'.
    Msg 4922, Level 16, State 9, Line 13
    ALTER TABLE ALTER COLUMN MyVarcharColumn failed because one or more objects access this column.

    It appears there are some restrictions when resizing a VARCHAR column that is indexed.

    1. If the column is either a key or included in the index, you can NOT shrink the size of the VARCHAR, but you can increase the size.
    2. If the column is part of the filter on a filtered index, you can NOT resize the VARCHAR at all (no shrink or expand)
    In either of these cases you must drop the index prior resizing (disabling will not do it).
    This seems to be the same with NVARCHAR.
    I also performed test around resizing non-VARCHAR columns, such as changing a SMALLDATETIME to the DATETIME and back. There does not seem to be any index related restrictions with these, non-VARCHAR columns. (Additional testing shows this is not the case. I can not change the size of any other datatype).
  • I'd say the only surprise there is that you can increase the size without rebuilding the index. Thinking about it though, it makes sense. We're only affecting future values in the index with that increase, so it's safe. The other restrictions all make sense based on the fact that any column that's part of an include or a key is stored there and you can't just arbitrarily change it from an external process like ALTER TABLE. You'd have to deal with the index using it's commands. By the way, it's not going to be just strings. Try changing an int to a smallint. I'll bet you hit similar issues.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Grant. Yes, you are correct about resizing any data type. 
    When testing previously, I thought I had successfully resized a DATETIME to SMALLDATETIME and back without dropping indexes. Upon retesting, this isn't the case.

    It is only VARCHAR/NVARCHAR that allow upsizing of the column when it is in the key (or included) in an index. If it is part of the filter, you can not change the size.

  • dave-L - Tuesday, September 25, 2018 9:06 AM

    Hi Grant. Yes, you are correct about resizing any data type. 
    When testing previously, I thought I had successfully resized a DATETIME to SMALLDATETIME and back without dropping indexes. Upon retesting, this isn't the case.

    It is only VARCHAR/NVARCHAR that allow upsizing of the column when it is in the key (or included) in an index. If it is part of the filter, you can not change the size.

     Hi - Can anyone guide as to why this happens; just curious to know about it.

    First solve the problem then write the code !

  • TheCTEGuy - Thursday, September 27, 2018 7:20 AM

    dave-L - Tuesday, September 25, 2018 9:06 AM

    Hi Grant. Yes, you are correct about resizing any data type. 
    When testing previously, I thought I had successfully resized a DATETIME to SMALLDATETIME and back without dropping indexes. Upon retesting, this isn't the case.

    It is only VARCHAR/NVARCHAR that allow upsizing of the column when it is in the key (or included) in an index. If it is part of the filter, you can not change the size.

     Hi - Can anyone guide as to why this happens; just curious to know about it.

     I don't know the precise internals, but you can extrapolate why this would be so. Changing any other data type, let's say tinyint to smallint, requires changing the allocated space on the page. Whereas, changing a variable character from any length to any longer length doesn't actually change any of the page allocations until new data is added/updated, and then the change is a normal part of the process. This is why you'd be able to do something like this within the engine. It works from a logical standpoint.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply