I ran into a head-scratcher recently while doing some routine index inventory work, and it’s the kind of thing that will absolutely burn you if you don’t know it’s out there. (Ask me how I know. 🙂 ) I went looking to see if anyone had already written this up, and the closest I found was a 2011 SQLServerCentral forum thread where Gail Shaw (@SQLintheWild) diagnosed the same root cause and even said “worth a blog post, sometime.” I didn’t find any relevant blog posts, so here goes… 🙂 There’s a full demo script included at the bottom so you can reproduce it yourself.

The Setup

I was running a standard “inventory all indexes on this table” query against a SQL Server instance; the kind of script every DBA has a version of, pulling key columns and included columns out of sys.index_columns so you can see what’s already there before adding anything new. (Column and table names below are genericized from the real ones I was working with, but the behavior is identical.)

The results came back showing the clustered primary key with RecordId leading the key. That didn’t line up with what I expected, so I went looking for why.

I’d first noticed something was off while comparing my inventory query’s results against what was checked into source control (Git) for this table’s DDL; the column order didn’t match the repo. Thinking the Git version might just be stale, I scripted out the table directly from the live server to get the ground truth. That scripted DDL showed the clustered index defined with RegionId as the leading key column, not RecordId, and it matched what was in Git; it was my query that was the odd one out. That’s what sent me looking for an explanation.

I fired up Claude and pasted in the query, the scripted DDL, and the actual result set into a prompt and asked it to reconcile the discrepancy. Within a couple of exchanges it had zeroed in on the two catalog view columns doing the damage, which saved me a fair amount of digging through Books Online myself. As a side note, I’ve been leaning on Claude more and more for this kind of script troubleshooting work, and this was a good example of why: it’s fast at diagnosing query issues and pinpointing root cause.

The Root Cause

The problem lives in sys.index_columns, and specifically in confusing two columns that sound like they’d tell you the same thing but don’t:

• key_ordinal: the column’s position within the index key, as currently defined. This is what SSMS uses when it scripts out CREATE TABLE / CREATE INDEX DDL, so it’s the authoritative source for “what order are the key columns really in, right now.”
• index_column_id: an internal identifier assigned to a column the first time it’s added to an index. It is not renumbered if the key order is changed later. It’s unique within the index, but it doesn’t mean “current position.”

My inventory query was building column order like this:

ROW_NUMBER() over (partition by sc.is_included_column order by sc.index_column_id) ColPos

That’s fine for included columns, since key_ordinal is always 0 for those anyway, so there’s nothing else to sort by. It’s a landmine for key columns, though. Sorting key columns by index_column_id only gives you the correct order if the index was created once, in its current column order, and never touched again.

Why the PK Was Out of Sync

Here’s the part that surprised me: this doesn’t require some tangled history of rebuilds and migrations to happen. It can happen the moment the table is created, and it comes down to one specific circumstance for clustered indexes: the column’s position in sys.index_columns.index_column_id gets tied to the column’s physical position in the table (its column_id in sys.columns), not to the order you declared in the key.

So if you create a table where the physical column order doesn’t match the clustered key order you want, index_column_id will follow the physical layout while key_ordinal correctly follows what you declared. Two columns, one index, two different “orders,” both technically true depending on which one you ask.

This can also happen after the fact, if a clustered PK’s key order gets changed later via a DROP_EXISTING rebuild or a drop-and-recreate with a different column order; key_ordinal updates to reflect the new key order, but index_column_id doesn’t get renumbered to match. Either way, the fix is the same, and I worked with Claude to build a clean, minimal demo below so you can watch it happen on a throwaway table instead of just taking my word for it.

Proving It: A Demo You Can Run Yourself

Spin up a scratch table where the physical column order intentionally does not match the clustered key order you’re declaring:

-- Run this in a scratch/test database, not production
IF OBJECT_ID('dbo.Demo_KeyOrderTest', 'U') IS NOT NULL
    DROP TABLE dbo.Demo_KeyOrderTest;
GO
CREATE TABLE dbo.Demo_KeyOrderTest
(
    RegionId    TINYINT       NOT NULL,  -- physically the 1st column in the table
    RecordId    INT           NOT NULL,  -- physically the 2nd column in the table
    RecordDate  DATETIME2(0)  NOT NULL,  -- physically the 3rd column in the table
    CONSTRAINT PK_Demo_KeyOrderTest PRIMARY KEY CLUSTERED
    (
        RecordId    ASC,  -- declared key order: 1st
        RecordDate  ASC,  -- declared key order: 2nd
        RegionId    ASC   -- declared key order: 3rd
    )
);
GO
INSERT INTO dbo.Demo_KeyOrderTest (RegionId, RecordId, RecordDate)
VALUES (1, 100, SYSDATETIME()), (2, 101, SYSDATETIME()), (3, 102, SYSDATETIME());
GO

I declared the clustered key as RecordId, RecordDate, RegionId, but the columns physically sit in the table as RegionId, RecordId, RecordDate. Now run this to see both orderings side by side:

SELECT 
    c.name AS ColumnName,
    ic.key_ordinal,
    ic.index_column_id,
    ic.is_included_column
FROM sys.index_columns ic
JOIN sys.columns c 
    ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.indexes i
    ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('dbo.Demo_KeyOrderTest')
  AND i.is_primary_key = 1;

Look at the two ordinal columns: key_ordinal will show RecordId=1, RecordDate=2, RegionId=3, matching exactly what you declared in the CREATE TABLE statement. index_column_id will instead follow the physical column order (RegionId=1, RecordId=2, RecordDate=3). Sort the same result set by each column and you’ll get two different sequences out of the same three rows.

Now script out the table (right-click it in SSMS’s Object Explorer and choose Script Table As > Create to), and you’ll see the DDL correctly shows the clustered key as RecordId, RecordDate, RegionId, because SSMS is reading from key_ordinal, same as it should. Any query sorting by index_column_id instead will disagree with that DDL, and now you’ve reproduced the exact mismatch I ran into.

The Full Script: Before

This is the actual index inventory script I was running when I hit this. It’s a common style of script: pull every index on a table along with its key columns and included columns in one readable row per index. Point the WHERE clause at the demo table above and run it yourself:

/**************************************************************************************************************
1) Get a list of all currently existing indexes with keys and included columns. (Run on specific database.) 
   See what's out there already before considering new indexes.   
**************************************************************************************************************/
select  
    schema_name(o.schema_id) as [Schema] , o.name ObjectName, i.name IndexName, i.index_id
   ,i.type_desc 
   ,LEFT(list, ISNULL(splitter-1,len(list))) Columns
   , SUBSTRING(list, indCol.splitter +1, 2056) includedColumns--len(name) - splitter-1) columns
   ,i.has_filter, i.filter_definition
from sys.indexes i
join sys.objects o on i.object_id = o.object_id 
cross apply (select NULLIF(charindex('|',indexCols.list),0) splitter , list
              from (select cast((
                           select case when sc.is_included_column = 1 and sc.ColPos = 1 then '|' else '' end +
                                  case when sc.ColPos  > 1 then ', ' else '' end + name 
                             from (select sc.is_included_column, index_column_id, name
                                        , ROW_NUMBER() over (partition by sc.is_included_column 
                                                             order by sc.index_column_id) ColPos
                                    from sys.index_columns  sc
                                    join sys.columns        c on sc.object_id = c.object_id
                                                             and sc.column_id = c.column_id
                                   where sc.index_id = i.index_id 
                                     and sc.object_id = i.object_id ) sc
                        
                    order by sc.is_included_column
                            ,ColPos
                      for xml path (''), type) as varchar(max)) list)indexCols ) indCol
			
 WHERE o.name = 'Demo_KeyOrderTest'
   and schema_name(o.schema_id) = 'dbo'
order by [Columns];

Run that against the demo table, and the Columns value for the primary key will come back in physical column order (RegionId, RecordId, RecordDate), not the declared key order (RecordId, RecordDate, RegionId). That’s the bug, staring back at you in a result grid that otherwise looks perfectly normal.

The Fix: After

The fix is one changed line: stop treating index_column_id as if it means “column order” for key columns. Sort key columns by key_ordinal, and only fall back to index_column_id for included columns, where key_ordinal doesn’t apply (it’s always 0 there, so there’s nothing else to sort by). I ran this past Claude to double check the CASE logic in the window function before trusting it:

/**************************************************************************************************************
1) Get a list of all currently existing indexes with keys and included columns. (Run on specific database.) 
   See what's out there already before considering new indexes.   
   FIXED: key columns now ordered by key_ordinal instead of index_column_id.
**************************************************************************************************************/
select  
    schema_name(o.schema_id) as [Schema] , o.name ObjectName, i.name IndexName, i.index_id
   ,i.type_desc 
   ,LEFT(list, ISNULL(splitter-1,len(list))) Columns
   , SUBSTRING(list, indCol.splitter +1, 2056) includedColumns--len(name) - splitter-1) columns
   ,i.has_filter, i.filter_definition
from sys.indexes i
join sys.objects o on i.object_id = o.object_id 
cross apply (select NULLIF(charindex('|',indexCols.list),0) splitter , list
              from (select cast((
                           select case when sc.is_included_column = 1 and sc.ColPos = 1 then '|' else '' end +
                                  case when sc.ColPos  > 1 then ', ' else '' end + name 
                             from (select sc.is_included_column, index_column_id, key_ordinal, name
                                        , ROW_NUMBER() over (partition by sc.is_included_column 
                                                             order by case when sc.is_included_column = 0 
                                                                            then sc.key_ordinal 
                                                                            else sc.index_column_id end) ColPos
                                    from sys.index_columns  sc
                                    join sys.columns        c on sc.object_id = c.object_id
                                                             and sc.column_id = c.column_id
                                   where sc.index_id = i.index_id 
                                     and sc.object_id = i.object_id ) sc
                        
                    order by sc.is_included_column
                            ,ColPos
                      for xml path (''), type) as varchar(max)) list)indexCols ) indCol
			
 WHERE o.name = 'Demo_KeyOrderTest'
   and schema_name(o.schema_id) = 'dbo'
order by [Columns];

Two changes from the original: key_ordinal is added to the inner column list so it’s available to sort by, and the ROW_NUMBER() window function now picks key_ordinal for key columns and only falls back to index_column_id for included columns. Run this version against the demo table and Columns will now correctly show RecordId, RecordDate, RegionId, matching the declared key order and matching what SSMS scripts out as DDL.

The Takeaway

If you’ve got a homegrown “list my indexes and their key/included columns” script, it’s worth checking whether it’s sorting by index_column_id or key_ordinal. A lot of scripts that circulate around the SQL Server community (mine included, apparently) work fine on the tables where they were first tested, simply because physical column order happened to match key order there, so the bug in the script never had a chance to surface. It’s the tables where physical layout and clustered key order diverge, whether that happened at creation or after a later rebuild, where this will bite you.

The fix takes one line. Finding out you needed it takes a lot longer, especially if you’re staring at a mismatch between a query and a DDL script in source control. If you’re not already in the habit of handing a query issue like this over to an AI assistant to help, hopefully this posts demonstrates how AI can help. It turned what could’ve been a painful afternoon of reading Microsoft Books Online into something I had wrapped up, demoed, and written up before lunch.