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;
key_ordinal
中的sys.index_columns
列以在复合主键中声明的顺序显示索引列: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';
在线图书说
stats_column_id
是“一组统计信息列中的基于1的序数,”期望值1指向统计对象的第一列。这是
sys.stats_columns
中的错误还是我的误解?我已经验证了此行为在当前版本的SQL Server 2005、2008、200上发生8 R2、2012和2014。
sys.stats_columns
在其他情况下似乎反映了统计对象内的顺序,例如:CREATE TABLE dbo.Foo2(
ID1 int NOT NULL,
ID2 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, 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 = 'Foo2'
AND s.name LIKE 'ST_Test%';
这是另一个示例,其中
sys.stats_columns
似乎返回正确的数据,这一次是为了对索引进行统计:--drop table dbo.Foo3
CREATE TABLE dbo.Foo3(
ID1 int NOT NULL,
ID2 int NOT NULL,
ID3 int NULL,
String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED (ID2, ID1)
);
GO
INSERT INTO Foo3 (ID1, ID2, ID3, String) VALUES (1,2,3,'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, 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 = 'Foo3'
AND s.name LIKE 'IX_Test%';
#1 楼
这似乎是一个长期存在的错误:swasheck-2015年3月5日发布:
https://connect.microsoft.com/SQLServer/feedback/details/1163126
MSDN指出sys.stats_columns.stats_column_id是“在一组统计信息列中基于1的序数
”。但是,它似乎实际上反映了表定义的顺序。更改索引顺序未反映在sys.stats_columns中。
Max Vernon和James Lupolt似乎基于他们的评论/鼓励而达成共识。
评论
几个月前我有同样的问题,但删除了。对于那个很抱歉。尽管如此,sys.stats_columns中的stats_column_id似乎并没有按照它说的去做。因为您要支持索引,所以我坚持使用索引列顺序。如果您只查看统计对象,那么index_col()似乎是目前最好的选择也许您应该为此提交Microsoft Connect项目?对我来说似乎是越野车。
@ MaxVernon,swashesk在这里提交了一个