The index columns in sys.index_columns different from what are in the index

Question

When I run the following I get 3 indexed columns:

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'

Returns:

IX_MyTableIssue_ColId_Col2Id    ColId 
IX_MyTableIssue_ColId_Col2Id    Col2Id
IX_MyTableIssue_ColId_Col2Id    ColDate

But when I do a right click, script index as then create to new query window I get the following:

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

I'm running SQL Sever 2008. Any advice or direction is greatly appreciated.

Answer 1

If a non-clustered index is created on a partitioned table, that index will be partition-aligned by default unless you explicitly specify that it should not be (e.g., use ON [PRIMARY] to specify the file group).

In such a case, the SSMS script index functionality will not show the partition scheme used, but you can use sp_help on the table to confirm that the non-clustered index is partitioned.

If you expand your query to check all columns on sys.index_columns, you will see that the partition_ordinal column is set to 1 for ColDate, indicating that is a partitioning column, and key_ordinal is set to 0, indicating that it is "Not a key column".

Here is a full repro with a dummy table:

-- 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

Answer 2

Is the clustered Index on ColDate? The key of the clustered index is included in any NC index.