PRB: DBCC DBREINDEX May Set the STATBLOB Column that Corresponds to All Column Statistics of the Sysindexes Table to NULL (306223)

SYMPTOMS

If you execute this code on a table that has a clustered index, you clear all the saved statistical information for all of the auto created and user created statistics:

DBCC DBREINDEX(<table_name>) or DBCC DBREINDEX(<table_name>, <clustered_index_name>)

You will not get any output when you run DBCC SHOW_STATISTICS for one of the statistics, and the corresponding sysindexes.statblob entry will be NULL.

RESOLUTION

Each time you execute DBCC DBREINDEX against a cluster index or a table with a cluster index on a computer that is running SQL Server 7.0, you might update statistics on the corresponding column or the whole table.

MORE INFORMATION

Steps to Reproduce Behavior

To reproduce the problem, use this code:

create table test (col1 int, col2 int) 

insert test values (1,1) 
insert test values (2,1) 
insert test values (3,1)  

create clustered index test_c_ind on test(col1)  
create index test_ind on test(col2)  
create statistics test_stat2 on test(col2)  

select * from sysindexes where name like 'test%'  

dbcc dbreindex (test)    -- or dbcc dbreindex (test, test_c_ind)   

dbcc show_statistics (test, test_stat2)

The query output from SQL Server 7.0 is: