sql-server – sys.stats_columns不正确吗？

假设我有一个表Foo,其列ID1,ID2和在ID2,ID1上界说的复合主键. (我今朝正在行使System Center产物,该产物具有以这种方法界说的多个表,主键列凭证它们在表界说中呈现的相反次序列出.)

CREATE TABLE dbo.Foo(
  ID1 int NOT NULL,ID2 int NOT NULL,CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED (ID2,ID1)
);
GO

-- Add a row and update stats so that histogram isn't empty
INSERT INTO Foo (ID1,ID2) VALUES (1,2);
UPDATE STATISTICS dbo.Foo;

sys.index_columns中的key_ordinal列以与复合主键中声明的次序沟通的次序表现索引列：

SELECT t.name,i.name,c.column_id,c.name,ic.index_column_id,ic.key_ordinal
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
WHERE t.name = 'Foo';

直方图还以沟通的次序表现统计信息：

DBCC SHOW_STATISTICS ('Foo',PK_Foo);

可是,sys.stats_columns表现以反向次序列出的列(ID1,ID2).

SELECT s.name,sc.stats_column_id,c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc 
ON s.stats_id = sc.stats_id 
AND s.[object_id] = sc.[object_id] 
JOIN sys.columns AS c 
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o 
ON o.[object_id] = c.[object_id] 
WHERE o.name = 'Foo'
AND s.name = 'PK_Foo';

Books Online暗示stats_column_id是“stats列齐集的基于1的序数”,因此我祈望值1指向统计工具中的第一列.

这是sys.stats_columns中的错误照旧我的误解？

我已履历证了此举动产生在SQL Server 2005,2008,2008 R2,2012和2014的当前版本上.

sys.stats_columns好像反应了统计工具在其他环境下的次序,譬喻：

CREATE TABLE dbo.Foo2(
  ID1 int NOT NULL,ID3 int NULL,String VARCHAR(10) NULL,CONSTRAINT [PK_Foo2] PRIMARY KEY CLUSTERED (ID2,ID1)
);

GO

INSERT INTO Foo2 (ID1,ID2,ID3,String) VALUES (1,2,3,'String');

CREATE STATISTICS ST_Test ON Foo2 (ID3,String);
CREATE STATISTICS ST_Test2 ON Foo2 (String,ID3);

DBCC SHOW_STATISTICS ('Foo2',ST_Test);
DBCC SHOW_STATISTICS ('Foo2',ST_Test2);


SELECT s.name,c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc 
ON s.stats_id = sc.stats_id 
AND s.[object_id] = sc.[object_id] 
JOIN sys.columns AS c 
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o 
ON o.[object_id] = c.[object_id] 
WHERE o.name = 'Foo2'
AND s.name LIKE 'ST_Test%';

这是另一个示例,个中sys.stats_columns好像返回正确的数据,这次是对索引的统计信息：

--drop table dbo.Foo3
CREATE TABLE dbo.Foo3(
  ID1 int NOT NULL,CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED (ID2,ID1)
);

GO

INSERT INTO Foo3 (ID1,'String');
UPDATE STATISTICS Foo3;

CREATE INDEX IX_Test ON Foo3 (ID3,String);
CREATE INDEX IX_Test2 ON Foo3 (String,ID3);

DBCC SHOW_STATISTICS ('Foo3',IX_Test);
DBCC SHOW_STATISTICS ('Foo3',IX_Test2);

SELECT s.name,c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc 
ON s.stats_id = sc.stats_id 
AND s.[object_id] = sc.[object_id] 
JOIN sys.columns AS c 
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o 
ON o.[object_id] = c.[object_id] 
WHERE o.name = 'Foo3'
AND s.name LIKE 'IX_Test%';

办理要领

swasheck – 2015年3月5日宣布：

https://connect.microsoft.com/SQLServer/feedback/details/1163126

MSDN notes that sys.stats_columns.stats_column_id is “1-based ordinal
within set of stats columns.” However,it seems to actually reflect
table definition order. Altering index order is not reflected in
sys.stats_columns.

Max Vernon和James Lupolt好像赞成他们的评述/勉励.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!