BUG: ALTER COLUMN on Column With Auto-Stats Returns Error Message 1903 (290992)

SYMPTOMS

The following statement:

ALTER TABLE <table_name> ALTER COLUMN <column_name> <new_data_type>

may fail with a 1903 error message when the AUTO_CREATE_STATISTICS database option is set ON:

Msg 1903, Level 16, State 1, Line 1 900 is the maximum allowable size of an index. The composite index specified is xxxx bytes.

CAUSE

A statistics _wa_sys_<column_name>_<xxxx> is automatically created on this column, which is not dropped automatically when the ALTER COLUMN statement executes.

This behavior does not follow this SQL Server 7.0 Books Online statement:

"Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN."

NOTE: The naming convention for statistics created automatically is:

_wa_sys_<column_name>_<xxxx>

<xxxx> is usually the object_id of table in hexadecimal form.

MORE INFORMATION

Steps to Reproduce Behavior

Make sure that AUTO_CREATE_STATISTICS is set ON:

exec sp_dboption <database_name>, 'auto create statistics', true

Create a table that contains a column of datatype varchar(255), for example, without creating an index on the varchar(255) column:

drop table doc
go
create table doc (doc_id int, doc_title varchar(255))
go
insert into doc (doc_id, doc_title) values (1, 'This is a test')
insert into doc (doc_id, doc_title) values (2, 'This is a test')
go

This SELECT statement causes the creation of auto statistics:

select d.doc_title, m.doc_title from doc m inner join doc d on m.doc_title = d.doc_title
go

Make sure that the _wa_sys_doc_title_<xxxx> auto statistics exist:

select name, * from sysindexes where id = object_id('doc')
go

Changing the column definition returns error message 1903, which is shown in the "Symptoms" section:
```
alter table doc alter column doc_title varchar(2000)
go
					
```
To work around the error message that occurs in step 5, run this code:
```
drop statistics doc._wa_sys_doc_title_<xxxx>
					
```
Changing the column definition succeeds without the error message occurring:
```
alter table doc alter column doc_title varchar(2000)
go
					
```