September 24, 2018 at 1:39 pm
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.
September 25, 2018 at 4:51 am
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
September 25, 2018 at 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.
September 27, 2018 at 7:20 am
dave-L - Tuesday, September 25, 2018 9:06 AMHi 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 !
September 27, 2018 at 8:51 am
TheCTEGuy - Thursday, September 27, 2018 7:20 AMdave-L - Tuesday, September 25, 2018 9:06 AMHi 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