ALTER TABLE ALTER COLUMN <name> failed because one or more objects access this column

Question

Introduction

Recently during an update of an application I received the following error message:

Msg 5074 Level 16 State 1 Line 1
The statistics 'BreakingStuff' is dependent on column 'TaskText'.
Msg 4922 Level 16 State 9 Line 1
ALTER TABLE ALTER COLUMN TaskText failed because one or more objects access this column.

^{Example error message based on analysis}

This was a manually created statistic on a column in a table. Actually there were multiple manually created statistics referencing the column in question.

Workaround

After dropping the statistics for the table, running the update, and then recreating the statistics, everything was running fine.

Analysis

I started out with a trivial simulation on db<>fiddle to see if I could reproduce the issue. After some fiddling (pun intended) I found out that the error message only gets triggered if I reduce the size of the column.

e.g. varchar(50) --> varchar (40)

Here is the db<>fiddle.

The only warnings I could find on Microsoft Learn were:

1. Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type. In addition, code and applications that depend on the modified column may fail. These include queries, views, stored procedures, user-defined functions, and client applications. Note that these failures will cascade. For example, a stored procedure that calls a user-defined function that depends on the modified column may fail. Carefully consider any changes you want to make to a column before making it.

   ^{Reference: Modify columns (Microsoft Learn | SQL)}

I was aware that reducing the column size would possibly result in loss of data, but this wasn't the case, as the data were all shorter than varchar(40).

Questions

1. Why does reducing the size of column result in an error, because of the manually created statistics?
2. Why does increasing the size of a column not result in an error with the manually created statistics?

Answer 1

SQL Server 2022 has a new option when creating statistics that can automate this as per the documentation (emphasis mine):

AUTO_DROP = { ON | OFF } Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database, Azure SQL Managed Instance

Before SQL Server 2022 (16.x), if statistics are manually created by a user or third party tool on a user database, those statistics objects can block or interfere with schema changes the customer might desire.

Starting with SQL Server 2022 (16.x), the AUTO_DROP option is enabled by default on all new and migrated databases. The AUTO_DROP property allows the creation of statistics objects in a mode such that a subsequent schema change is not blocked by the statistic object, but instead the statistics are dropped as necessary. In this way, manually created statistics with AUTO_DROP enabled behave like autocreated statistics.

Note

Trying to set or unset the Auto_Drop property on auto-created statistics might raise errors. Auto-created statistics always uses auto drop. Some backups, when restored, might have this property set incorrectly until the next time the statistics object is updated (manually or automatically). However, auto-created statistics always behave like auto drop statistics. When restoring a database to SQL Server 2022 (16.x) from a previous version, it's recommended to execute sp_updatestats on the database, setting the proper metadata for the statistics AUTO_DROP feature.

Answer 2

Columns that have manually-created statistics attached cannot have their properties modified without first dropping the stats object – this is to ensure the stats object accurately reflects the content of the column. When a manually created stats column is detected, SQL Server returns an error message stating “ALTER TABLE ALTER COLUMN failed because one or more objects access this column.”

I wrote a blog post about it here

Interestingly, if SQL Server has auto-created a stats object on a column, and you subsequently modify that column, you receive no such error. SQL Server silently drops the statistics object, and modifies the column. The auto-created stats object is not automatically recreated until a query is executed that needs the stats object.

Increasing the size of the column is a meta-data-only operation that doesn't actually change any of the existing data, hence the stats object is still valid.

Decreasing the size of a column may mean values are truncated, which could invalidate the stats.