Improvement to support for filtered indexes

[Brett_Shearer]

Considering the following code works:

CREATE TABLE TableName (ColumnName varchar(10))
CREATE INDEX IndexName on TableName (ColumnName)
ALTER TABLE TableName alter column ColumnName varchar(11) -- increasing the length of an already indexed column

Why does SQL return error 5074 for the following code?

CREATE TABLE TableName (ColumnName varchar(10))
CREATE INDEX IndexName on TableName (ColumnName) where ColumnName <> ''
ALTER TABLE TableName alter column ColumnName varchar(11) -- increasing the length of an already indexed column

This seems like an arbitrary failure. The indexing structure has not changed by increasing the varchar for the column as evidenced by it working on the non filtered index example. 

Msg 5074, Level 16, State 1, Line 8
The index 'IndexName' is dependent on column 'ColumnName'.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN ColumnName failed because one or more objects access this column.

[#2]

---

This seems like an arbitrary failure. The indexing structure has not changed by increasing the varchar

---

@Brett_Shearer , there is no failure on database side, with change the column data type (size), the index structure would change; therefore you get the error message. The enigne behaviour is absolutley correct.

[#3]

@Brett_Shearer I fully understand your question here, as it looks like it should be supported.
From ALTER TABLE (Transact-SQL) - SQL Server | Microsoft Learn

The data type of columns included in an index can't be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

---

You have a varchar, and you increase it's size, no problems detected.

The reason you can't alter the column size, is that it's referenced in the filter.

Consider the following

CREATE TABLE TableName (reference varchar(10), indexed varchar(10))
CREATE INDEX IndexName on TableName (indexed) where reference <> ''
ALTER TABLE TableName alter column indexed varchar(11) 
ALTER TABLE TableName alter column reference varchar(11) 

Only the last step fails, which leads me to believe this text from the same article is why this fails.

• The modified column can't be: 

  • A computed column or used in a computed column.

---

I suspect that the filter in a filtered index is implemented as a computed column, and thus you can't alter the column size.

[#4]

@olafhelper What index structure changes? AFAIK the maximum length of the field is not written on the nodes in the index (considering the field is varchar). This feels like a missing optimisation.
I'm just about to whip out dbcc page to see what is actually different on the index page - I suspect nothing.