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

Columns that have manually-created statistics attached cannot have their properties modified without first dropping the stats object. SQL Server returns an error message stating “ALTER TABLE ALTER COLUMN failed because one or more objects access this column.” I ran into this limitation recently when attempting to modify the datatype for a column from varchar to nvarchar. This particular client has a policy in place where auto create statistics is disabled by default. This necessitates manual creation of statistics objects to ensure good performance.

alter table alter column failed!

     Have a failed column or three!

When attempting to modify a column that has a manually created statistics object attached, you receive the following “ALTER TABLE ALTER COLUMN failed” error message:

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. This difference in how auto-created stats and manually created stats are treated by the engine can make for some confusion.

Ads by Google, Paying the Rent:

Consider the following minimally complete and verifiable example code that can be used to reproduce the problem:

SQL Server returns this error:

Let’s continue on:

╔════════════╦════════════════════════════════╦══════════════╗
║    name    ║              name              ║ auto_created ║
╠════════════╬════════════════════════════════╬══════════════╣
║ stats_test ║ PK__stats_te__3213E83FF58F8430 ║            0 ║
║ stats_test ║ _WA_Sys_00000002_21B6055D      ║            1 ║
╚════════════╩════════════════════════════════╩══════════════╝

The auto-created stats object has been silently dropped:

╔════════════╦════════════════════════════════╦══════════════╗
║    name    ║              name              ║ auto_created ║
╠════════════╬════════════════════════════════╬══════════════╣
║ stats_test ║ PK__stats_te__3213E83FF58F8430 ║            0 ║
╚════════════╩════════════════════════════════╩══════════════╝

If you have auto_create_statistics turned off, you probably want to update your stats objects on a regular basis to ensure good performance. See my previous post for details about how to do that.

Read the other articles in our series on SQL Server Internals.

Ads by Google, Paying the Rent: