Microsoft KB Archive/290992

= BUG: ALTER COLUMN on Column With Auto-Stats Returns Error Message 1903 =

Article ID: 290992

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q290992



BUG #: 101257 (SQLBUG_70)



SYMPTOMS
The following statement: ALTER TABLE  ALTER COLUMN   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__ 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:

&quot;Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.&quot;

NOTE: The naming convention for statistics created automatically is:

_wa_sys__

is usually the object_id of table in hexadecimal form.



WORKAROUND
Drop the statistics of the column explicitly before you execute the code. For example: ALTER TABLE  ALTER COLUMN drop statistics ._wa_sys__



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



Steps to Reproduce Behavior
 <li> Make sure that AUTO_CREATE_STATISTICS is set ON: exec sp_dboption <database_name>, 'auto create statistics', true </li> <li> 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 </li> <li> 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 </li> <li> Make sure that the _wa_sys_doc_title_ auto statistics exist: select name, * from sysindexes where id = object_id('doc') go </li> <li> Changing the column definition returns error message 1903, which is shown in the &quot;Symptoms&quot; section: alter table doc alter column doc_title varchar(2000) go </li> <li> To work around the error message that occurs in step 5, run this code: drop statistics doc._wa_sys_doc_title_ Changing the column definition succeeds without the error message occurring: alter table doc alter column doc_title varchar(2000) go </li></ol>

Keywords: kbbug kbpending KB290992

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.