After the SQL Server clustered index is rebuilt, the column name of the change column location statistics information remains unchanged,
It was originally found by using the clustered index primary key. It was confirmed that it was not a primary key issue but a clustered index issue.
Version: Microsoft SQL Server 2008 R2 (SP1)
-- Create a test table -- drop table [TestTable] create table [dbo]. [TestTable] ([id] [int] not null, [name] [varchar] (20) not null) go -- insert data into [TestTable] ([id], [name]) select id % 10, left (name, 20) from sys. sysobjectsgo -- create a clustered index ([id] asc, [name] asc) create clustered index [pk_testtable] on [dbo]. [TestTable] ([id], [name]) go
-- Dbcc show_statistics ('[dbo]. [TestTable]', 'pk _ testtable ')
Note the sequence of the red box. Create a clustered index again
-- Delete the clustered index drop index [pk_testtable] on [dbo]. [TestTable] go -- create a clustered index ([name], [id]) create clustered index [pk_testtable] on [dbo]. [TestTable] ([name], [id]) go
When an index is deleted, the statistics are deleted. Now let's look at the name again. The index has been changed to ([name], [id]), but the statistics are still the same !~ Bug?
-- Check the statistical information dbcc show_statistics ('[dbo]. [TestTable]', 'pk _ testtable ')
Statistics are normal, but the order of column names displayed on the page is different.
-- Check the index usage. [name] indicates that the index must be used. select * from [TestTable] where [id] = 1 select * from [TestTable] where [name] = 'A'
Because it is an index and cannot delete statistics, it cannot be changed.
Although this is not a serious problem, is it strange that these problems can be fixed? We are not sure whether the problem is ssms.
In addition to this problem, the function "Updating the statistics of these columns" is also invalid !~