Forum Discussion
Brett_Shearer
May 01, 2024Copper Contributor
Improvement to support for filtered indexes
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) -...
SivertSolem
May 02, 2024Iron Contributor
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.