sql - 检查表列上是否存在索引

Question

我在表的单列上定义了一个索引。我正在使用以下查询来确定索引是否存在于一列上。这似乎有效，但有没有更好的方法来做到这一点？我查看了 stackoverflow 上的其他解决方案，但似乎效果不佳。

    SELECT MIN(ColumnsCount) FROM 
    (
    SELECT COUNT(*)  AS ColumnsCount, 
            SysIndex.name
    FROM sys.indexes As SysIndex
        Inner Join sys.index_columns As SysIndexCol On SysIndex.object_id = SysIndexCol.object_id And SysIndex.index_id = SysIndexCol.index_id 
        Inner Join sys.columns As SysCols On SysIndexCol.column_id = SysCols.column_id And SysIndexCol.object_id = SysCols.object_id  
    WHERE SysIndex.name 
    in 
    (
     Select 
        SysIndex.name
    From 
        sys.indexes As SysIndex
        Inner Join sys.index_columns As SysIndexCol On SysIndex.object_id = SysIndexCol.object_id And SysIndex.index_id = SysIndexCol.index_id 
        Inner Join sys.columns As SysCols On SysIndexCol.column_id = SysCols.column_id And SysIndexCol.object_id = SysCols.object_id 
    Where 
        type <> 0 
        And SysIndex.object_id in (Select systbl.object_id from sys.tables as systbl Where SysTbl.name = 'TableName')
        And SysCols.name = 'ColName'
    ) 
    GROUP BY SysIndex.name) A

Answer 1

您正在检索的是给定表的索引中使用的最少列数，其中索引包括给定列。您的查询可以简化为:

    SELECT  TOP 1 ColumnsCount = COUNT(*)
    FROM    sys.index_columns AS ic
            INNER JOIN sys.indexes AS i
                ON ic.[object_id] = i.[object_id]
                AND ic.index_id = i.index_id
            INNER JOIN sys.columns AS c
                ON ic.[object_id] = c.[object_id]
                AND ic.column_id = c.column_id
    WHERE   ic.[object_id] = OBJECT_ID(N'dbo.YourTableName')
    AND     i.[type] != 0
    AND     ic.is_included_column = 0
    GROUP BY i.index_id
    HAVING  COUNT(CASE WHEN c.Name = 'YourColumnName' THEN 1 END) > 0
    ORDER BY ColumnsCount;

我在 ic.is_included_column = 0 中添加了条件，假设您不想在帐户中包含非关键列，也不会对给定列不是关键列的索引感兴趣。如果此假设不正确，则删除此谓词。

但是，如果您当前的查询有效，我看不出优化系统目录上的查询有多大好处。它们不太可能成为性能杀手。