主页 / dba / 问题 / 136510
Accepted
Russ960
Asked: 2016-04-26 12:12:37 +0800 CST

sys.index_columns 中的索引列与索引中的不同

  • 772

当我运行以下命令时，我得到 3 个索引列：

SELECT b.name,
       c.name
FROM sys.index_columns a
INNER JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.columns c ON b.object_id = c.object_id
AND a.column_id = c.column_id
WHERE b.object_id = OBJECT_ID('dbo.MyTableIssue')
  AND b.name = 'IX_MyTableIssue_ColId_Col2Id'

退货：

IX_MyTableIssue_ColId_Col2Id    ColId 
IX_MyTableIssue_ColId_Col2Id    Col2Id
IX_MyTableIssue_ColId_Col2Id    ColDate

但是当我右击脚本索引然后创建到新的查询窗口时，我得到以下信息：

CREATE NONCLUSTERED INDEX [IX_MyTableIssue_ColId_Col2Id] ON [dbo].[MyTableIssue]
(
    [ColId] ASC,
    [Col2Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

我正在运行 SQL Sever 2008。非常感谢任何建议或指导。

sql-server sql-server-2008

2 个回答

  1. Best Answer

    如果在分区表上创建非聚集索引，该索引将默认分区对齐，除非您明确指定它不应该是（例如，用于ON [PRIMARY]指定文件组）。

    在这种情况下，SSMS 脚本索引功能将不会显示使用的分区方案，但您可以sp_help在表上使用以确认非聚集索引已分区。

    如果您展开查询以检查 上的所有列sys.index_columns，您将看到该partition_ordinal列设置为1for ColDate，表示它是一个分区列，并且key_ordinal设置为0，表示它是“不是键列”。

    这是带有虚拟表的完整复制品：

    -- Create dummy partition function and scheme
CREATE PARTITION FUNCTION pf_test (INT)
AS RANGE RIGHT
FOR VALUES (1,2)
GO
-- Create dummy partition function and scheme
CREATE PARTITION SCHEME ps_test
AS PARTITION pf_test_transactionId
ALL TO ( [PRIMARY] )
GO

-- Create dummy partitioned table
CREATE TABLE dbo.testPartitionedTable (
    id INT NOT NULL,
    a INT NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL,
    CONSTRAINT PK_testPartitionedTable PRIMARY KEY (a, id) ON ps_test(id)
)
-- Create the non-clustered index
CREATE INDEX IX_testPartitionedTable ON dbo.testPartitionedTable (b)
GO
-- Run your query for inspecting the index columns
SELECT b.name,
       c.name,
       a.*
FROM sys.index_columns a
INNER JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.columns c ON b.object_id = c.object_id
AND a.column_id = c.column_id
WHERE b.object_id = OBJECT_ID('dbo.testPartitionedTable')
  AND b.name = 'IX_testPartitionedTable'
GO
    • 11

  2. ColDate 上的聚集索引是什么？聚簇索引的键包含在任何 NC 索引中。

    • 0

相关问题