3

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.

  • @Russ960 Is the table partitioned by ColDate? I believe the partitioning key shows up in sys.index_columns for any partition-aligned non-clustered indexes. Maybe that's your situation? – Geoff Patterson Apr 25 '16 at 20:32
  • @GeoffPatterson Yes it is. – Russ960 Apr 25 '16 at 20:41
11

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
0

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

  • @ChadMattox The clustered index is on ColDate, ColId, Col2Id and Col2Date. But I don't see all of the index columns is only the first one included? – Russ960 Apr 25 '16 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.