mssql get_indexes() does not preserve column order

[CaselIT]

Discussed in #12892

^{Originally posted by e2ne0 October 1, 2025}
Hi everyone,

While using alembic, I noticed that indexes which were already correctly created kept showing up as changes and got re-created every time I ran autogenerate.
Looking into it, I found that when reflecting indexes from mssql, the columns are returned in the order of sys.columns.column_id rather than the actual order defined in the index sys.index_columns.key_ordinal.

I'm posting this in the SQLAlchemy repo because the issue comes from how SQLAlchemy reflects indexes, not from Alembic itself.

Example

Database index definition:

CREATE NONCLUSTERED INDEX [Index_Example]
    ON [Table1]([Column3] DESC, [Column1] ASC, [Column2] DESC);

What get_indexes() returns:

{
    'name': 'Index_Example',
    'column_names': ['Column1', 'Column2', 'Column3']
    # sorted by column_id
}

What it should return to make alembic autogenerate correctly:

{
    'name': 'Index_Example', 
    'column_names': ['Column3', 'Column1', 'Column2']
    # sorted by key_ordinal
}

Reproducible test case

from sqlalchemy import create_engine, inspect, MetaData, Table, Column, Integer, Index

engine = create_engine('mssql+pyodbc://...')

metadata = MetaData()
test_table = Table(
    'Table1',
    metadata,
    Column('Column1', Integer, primary_key=True),
    Column('Column2', Integer),
    Column('Column3', Integer),
)

# create test index
idx = Index(
    'Index_Example',
    test_table.c.Column3.desc(),
    test_table.c.Column1.asc(),
    test_table.c.Column2.desc(),
)

metadata.create_all(engine)

inspector = inspect(engine)
indexes = inspector.get_indexes('Index_Example')
print(indexes[0]['column_names'])
# output shows wrong order

metadata.drop_all(engine)

MSSQL Verification

Running this query shows the correct order:

SELECT 
    COL_NAME(ic.object_id, ic.column_id) AS ColumnName,
    ic.key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('Table1') AND i.name = 'Index_Example'
ORDER BY ic.key_ordinal;  -- This ORDER BY is missing in get_indexes()

Root cause

In sqlalchemy/dialects/mssql/base.py, the get_indexes() method's second query doesn't order by key_ordinal:

# Current query
rp = connection.execution_options(future_result=True).execute(
    sql.text(
        """
select
    ind_col.index_id,
    col.name,
    ind_col.is_included_column
from
    sys.columns as col
join sys.tables as tab on tab.object_id = col.object_id
join sys.index_columns as ind_col on 
    ind_col.column_id = col.column_id
    and ind_col.object_id = tab.object_id
join sys.schemas as sch on sch.schema_id = tab.schema_id
where
    tab.name = :tabname
    and sch.name = :schname
        """
    )
    # missing: ORDER BY ind_col.index_id, ind_col.key_ordinal
)

Proposed fix

Add ORDER BY ind_col.index_id, ind_col.key_ordinal to ensure columns are returned in the correct order.

My questions

1. Is this considered a bug?
2. If yes, would you accept a PR with the above fix?
3. Any known backward compatibility concerns?

Environment

• SQLAlchemy: 2.0.x
• db: Microsoft SQL Server 2016+
• This affects Alembic autogenerate significantly

[zzzeek]

I thought this might have been a regression from recent changes I made in mssql but that was for get_columns(), the get_indexes() query here has been using that ORDER BY for a long time so this is a new issue.

[zzzeek]

the test for this should be in lib/sqlalchemy/testing/suite/test_reflection.py so that it works for all backends, all backends should be confirming columns come back in the same order, im not exactly sure why the many tests for index reflections that are there already arent doing this.

[zzzeek]

OK this doesnt reproduce for me, first off the get_indexes() call is incorrect, it should be get_indexes('Table1').

Then it works for me:

from sqlalchemy import create_engine, inspect, MetaData, Table, Column, Integer, Index

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql2022:1433/test?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&Encrypt=Optional")

metadata = MetaData()
test_table = Table(
    'Table1',
    metadata,
    Column('Column1', Integer, primary_key=True),
    Column('Column2', Integer),
    Column('Column3', Integer),
)

# create test index
idx = Index(
    'Index_Example',
    test_table.c.Column3.desc(),
    test_table.c.Column1.asc(),
    test_table.c.Column2.desc(),
)

metadata.create_all(engine)

inspector = inspect(engine)
indexes = inspector.get_indexes('Table1')
print(indexes[0]['column_names'])
# output shows wrong order

metadata.drop_all(engine)

output:

['Column3', 'Column1', 'Column2']

the query is indeed missing ORDER BY, and seems to return the first column twice, im not really sure how this organizes things to come up with the ordering it does:

select
    ind_col.index_id,
    col.name,
    ind_col.is_included_column
from
    sys.columns as col
join sys.tables as tab on
    tab.object_id = col.object_id
join sys.index_columns as ind_col on
    ind_col.column_id = col.column_id
    and ind_col.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = CAST(? AS NVARCHAR(max))
    and sch.name = CAST(? AS NVARCHAR(max))
            
2025-10-01 08:56:46,952 INFO sqlalchemy.engine.Engine [generated in 0.00028s] ('Table1', 'scott')
2025-10-01 08:56:47,001 DEBUG sqlalchemy.engine.Engine Col ('index_id', 'name', 'is_included_column')
2025-10-01 08:56:47,001 DEBUG sqlalchemy.engine.Engine Row (1, 'Column1', False)
2025-10-01 08:56:47,002 DEBUG sqlalchemy.engine.Engine Row (2, 'Column3', False)
2025-10-01 08:56:47,002 DEBUG sqlalchemy.engine.Engine Row (2, 'Column1', False)
2025-10-01 08:56:47,002 DEBUG sqlalchemy.engine.Engine Row (2, 'Column2', False)
2025-10-01 08:56:47,004 INFO sqlalchemy.engine.Engine ROLLBACK
['Column3', 'Column1', 'Column2']

but anyway that would be why the tests dont find this, @e2ne0 can you share detail about the MSSQL version in use and anything else interesting about this index?

[zzzeek]

Here's a test for the suite. the asc/desc is removed because this emits another bug on oracledb:

diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index aa1a4e90a..e5e011160 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -2699,6 +2699,25 @@ class ComponentReflectionTestExtra(ComparesIndexes, fixtures.TestBase):
             ],
         )
 
+    def test_index_column_order(self, metadata, inspect_for_table):
+        """test for #12894"""
+        with inspect_for_table("sa_multi_index") as (schema, inspector):
+            test_table = Table(
+                "sa_multi_index",
+                metadata,
+                Column("Column1", Integer, primary_key=True),
+                Column("Column2", Integer),
+                Column("Column3", Integer),
+            )
+            Index(
+                "Index_Example",
+                test_table.c.Column3,
+                test_table.c.Column1,
+                test_table.c.Column2,
+            )
+        indexes = inspector.get_indexes("sa_multi_index")
+        eq_(indexes[0]["column_names"], ["Column3", "Column1", "Column2"])
+
     @testing.requires.indexes_with_expressions
     def test_reflect_expression_based_indexes(self, metadata, connection):
         t = Table(

[e2ne0]

@zzzeek

I found that this bug only shows up when index was clustered.
Let's try this test case

from sqlalchemy import create_engine, inspect, MetaData, Table, Column, Integer, Index, PrimaryKeyConstraint

engine = create_engine('mssql+pyodbc://user:password@host/db?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes', echo=True)

metadata = MetaData()
test_table_clustered_index = Table(
    'Table_Clustered_Index',
    metadata,
    Column('Column1', Integer, primary_key=True),
    Column('Column2', Integer),
    Column('Column3', Integer),
    PrimaryKeyConstraint('Column1', mssql_clustered=False),
)

idx_clustered = Index(
    'Clustered_Index',
    test_table_clustered_index.c.Column3.desc(),
    test_table_clustered_index.c.Column1.asc(),
    test_table_clustered_index.c.Column2.desc(),
    mssql_clustered=True,
)

test_table_nonclustered_index = Table(
    'Table_Nonclustered_Index',
    metadata,
    Column('Column1', Integer, primary_key=True),
    Column('Column2', Integer),
    Column('Column3', Integer),
)

idx_nonclustered = Index(
    'Nonclustered_Index',
    test_table_nonclustered_index.c.Column3.desc(),
    test_table_nonclustered_index.c.Column1.asc(),
    test_table_nonclustered_index.c.Column2.desc(),
)

metadata.create_all(engine)

inspector = inspect(engine)
indexes_clustered = inspector.get_indexes('Table_Clustered_Index')
indexes_nonclustered = inspector.get_indexes('Table_Nonclustered_Index')
print(indexes_clustered[0]['column_names'])
print(indexes_nonclustered[0]['column_names'])

metadata.drop_all(engine)

my output

2025-10-02 09:26:08,149 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2025-10-02 09:26:08,149 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-10-02 09:26:08,155 INFO sqlalchemy.engine.Engine SELECT schema_name()
2025-10-02 09:26:08,155 INFO sqlalchemy.engine.Engine [generated in 0.00005s] ()
2025-10-02 09:26:08,169 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2025-10-02 09:26:08,169 INFO sqlalchemy.engine.Engine [generated in 0.00011s] ()
2025-10-02 09:26:08,173 INFO sqlalchemy.engine.Engine SELECT 1 FROM fn_listextendedproperty(default, default, default, default, default, default, default)
2025-10-02 09:26:08,173 INFO sqlalchemy.engine.Engine [generated in 0.00006s] ()
2025-10-02 09:26:08,186 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-02 09:26:08,188 INFO sqlalchemy.engine.Engine SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE ([INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) OR [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max))) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2025-10-02 09:26:08,188 INFO sqlalchemy.engine.Engine [generated in 0.00012s] ('BASE TABLE', 'VIEW', 'Table_Clustered_Index', 'dbo')
2025-10-02 09:26:08,199 INFO sqlalchemy.engine.Engine SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE ([INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) OR [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max))) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2025-10-02 09:26:08,199 INFO sqlalchemy.engine.Engine [cached since 0.01147s ago] ('BASE TABLE', 'VIEW', 'Table_Nonclustered_Index', 'dbo')
2025-10-02 09:26:08,210 INFO sqlalchemy.engine.Engine 
CREATE TABLE [Table_Clustered_Index] (
        [Column1] INTEGER NOT NULL IDENTITY, 
        [Column2] INTEGER NULL, 
        [Column3] INTEGER NULL, 
        PRIMARY KEY NONCLUSTERED ([Column1])
)


2025-10-02 09:26:08,210 INFO sqlalchemy.engine.Engine [no key 0.00005s] ()
2025-10-02 09:26:08,218 INFO sqlalchemy.engine.Engine CREATE CLUSTERED INDEX [Clustered_Index] ON [Table_Clustered_Index] ([Column3] DESC, [Column1] ASC, [Column2] DESC)
2025-10-02 09:26:08,218 INFO sqlalchemy.engine.Engine [no key 0.00005s] ()
2025-10-02 09:26:08,226 INFO sqlalchemy.engine.Engine 
CREATE TABLE [Table_Nonclustered_Index] (
        [Column1] INTEGER NOT NULL IDENTITY, 
        [Column2] INTEGER NULL, 
        [Column3] INTEGER NULL, 
        PRIMARY KEY ([Column1])
)


2025-10-02 09:26:08,226 INFO sqlalchemy.engine.Engine [no key 0.00005s] ()
2025-10-02 09:26:08,232 INFO sqlalchemy.engine.Engine CREATE INDEX [Nonclustered_Index] ON [Table_Nonclustered_Index] ([Column3] DESC, [Column1] ASC, [Column2] DESC)
2025-10-02 09:26:08,232 INFO sqlalchemy.engine.Engine [no key 0.00004s] ()
2025-10-02 09:26:08,238 INFO sqlalchemy.engine.Engine COMMIT
2025-10-02 09:26:08,242 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-02 09:26:08,242 INFO sqlalchemy.engine.Engine 
select
    ind.index_id,
    ind.is_unique,
    ind.name,
    ind.type,
    ind.filter_definition
from
    sys.indexes as ind
join sys.tables as tab on
    ind.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = CAST(? AS NVARCHAR(max))
    and sch.name = CAST(? AS NVARCHAR(max))
    and ind.is_primary_key = 0
    and ind.type != 0
order by
    ind.name
                
2025-10-02 09:26:08,242 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ('Table_Clustered_Index', 'dbo')
2025-10-02 09:26:08,249 INFO sqlalchemy.engine.Engine 
select
    ind_col.index_id,
    col.name,
    ind_col.is_included_column
from
    sys.columns as col
join sys.tables as tab on
    tab.object_id = col.object_id
join sys.index_columns as ind_col on
    ind_col.column_id = col.column_id
    and ind_col.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = CAST(? AS NVARCHAR(max))
    and sch.name = CAST(? AS NVARCHAR(max))
            
2025-10-02 09:26:08,250 INFO sqlalchemy.engine.Engine [generated in 0.00009s] ('Table_Clustered_Index', 'dbo')
2025-10-02 09:26:08,257 INFO sqlalchemy.engine.Engine ROLLBACK
2025-10-02 09:26:08,261 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-02 09:26:08,261 INFO sqlalchemy.engine.Engine 
select
    ind.index_id,
    ind.is_unique,
    ind.name,
    ind.type,
    ind.filter_definition
from
    sys.indexes as ind
join sys.tables as tab on
    ind.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = CAST(? AS NVARCHAR(max))
    and sch.name = CAST(? AS NVARCHAR(max))
    and ind.is_primary_key = 0
    and ind.type != 0
order by
    ind.name
                
2025-10-02 09:26:08,261 INFO sqlalchemy.engine.Engine [cached since 0.0192s ago] ('Table_Nonclustered_Index', 'dbo')
2025-10-02 09:26:08,287 INFO sqlalchemy.engine.Engine 
select
    ind_col.index_id,
    col.name,
    ind_col.is_included_column
from
    sys.columns as col
join sys.tables as tab on
    tab.object_id = col.object_id
join sys.index_columns as ind_col on
    ind_col.column_id = col.column_id
    and ind_col.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = CAST(? AS NVARCHAR(max))
    and sch.name = CAST(? AS NVARCHAR(max))
            
2025-10-02 09:26:08,287 INFO sqlalchemy.engine.Engine [cached since 0.03782s ago] ('Table_Nonclustered_Index', 'dbo')
2025-10-02 09:26:08,297 INFO sqlalchemy.engine.Engine ROLLBACK
['Column1', 'Column2', 'Column3']
['Column3', 'Column1', 'Column2']

[zzzeek]

OK then we should add the test I have above, then also have a test in test/dialect/mssql/test_reflection.py with the specific case above. thanks for looking into that!

[e2ne0]

OK this doesnt reproduce for me, first off the get_indexes() call is incorrect, it should be get_indexes('Table1').

Then it works for me:

from sqlalchemy import create_engine, inspect, MetaData, Table, Column, Integer, Index

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql2022:1433/test?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&Encrypt=Optional")

metadata = MetaData()
test_table = Table(
'Table1',
metadata,
Column('Column1', Integer, primary_key=True),
Column('Column2', Integer),
Column('Column3', Integer),
)

create test index

idx = Index(
'Index_Example',
test_table.c.Column3.desc(),
test_table.c.Column1.asc(),
test_table.c.Column2.desc(),
)

metadata.create_all(engine)

inspector = inspect(engine)
indexes = inspector.get_indexes('Table1')
print(indexes[0]['column_names'])

output shows wrong order

metadata.drop_all(engine)
output:

['Column3', 'Column1', 'Column2']

the query is indeed missing ORDER BY, and seems to return the first column twice, im not really sure how this organizes things to come up with the ordering it does:

select
    ind_col.index_id,
    col.name,
    ind_col.is_included_column
from
    sys.columns as col
join sys.tables as tab on
    tab.object_id = col.object_id
join sys.index_columns as ind_col on
    ind_col.column_id = col.column_id
    and ind_col.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = CAST(? AS NVARCHAR(max))
    and sch.name = CAST(? AS NVARCHAR(max))
            
2025-10-01 08:56:46,952 INFO sqlalchemy.engine.Engine [generated in 0.00028s] ('Table1', 'scott')
2025-10-01 08:56:47,001 DEBUG sqlalchemy.engine.Engine Col ('index_id', 'name', 'is_included_column')
2025-10-01 08:56:47,001 DEBUG sqlalchemy.engine.Engine Row (1, 'Column1', False)
2025-10-01 08:56:47,002 DEBUG sqlalchemy.engine.Engine Row (2, 'Column3', False)
2025-10-01 08:56:47,002 DEBUG sqlalchemy.engine.Engine Row (2, 'Column1', False)
2025-10-01 08:56:47,002 DEBUG sqlalchemy.engine.Engine Row (2, 'Column2', False)
2025-10-01 08:56:47,004 INFO sqlalchemy.engine.Engine ROLLBACK
['Column3', 'Column1', 'Column2']

but anyway that would be why the tests dont find this, @e2ne0 can you share detail about the MSSQL version in use and anything else interesting about this index?

mssql version I tested: Windows SQL Server 2016/2019 and both Ubuntu based latest docker images from 2017~2025

[e2ne0]

Here's temp workaround if anyone encounter this issue.
add this before run_migrations_offline() in your alembic's env.py

from sqlalchemy.dialects.mssql import base as mssql_base, information_schema as ischema
from sqlalchemy import sql
from sqlalchemy import types as sqltypes
from sqlalchemy.engine import reflection
original_get_indexes = mssql_base.MSDialect.get_indexes


@reflection.cache
@mssql_base._db_plus_owner
def patched_get_indexes(self, connection, tablename, dbname, owner, schema, **kw):
    filter_definition = (
        "ind.filter_definition"
        if self.server_version_info >= mssql_base.MS_2008_VERSION
        else "NULL as filter_definition"
    )

    rp = connection.execution_options(future_result=True).execute(
        sql.text(
            f"""
select
    ind.index_id,
    ind.is_unique,
    ind.name,
    ind.type,
    {filter_definition}
from
    sys.indexes as ind
join sys.tables as tab on
    ind.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = :tabname
    and sch.name = :schname
    and ind.is_primary_key = 0
    and ind.type != 0
order by
    ind.name
            """
        )
        .bindparams(
            sql.bindparam("tabname", tablename, ischema.CoerceUnicode()),
            sql.bindparam("schname", owner, ischema.CoerceUnicode()),
        )
        .columns(name=sqltypes.Unicode())
    )

    indexes = {}
    for row in rp.mappings():
        indexes[row["index_id"]] = current = {
            "name": row["name"],
            "unique": row["is_unique"] == 1,
            "column_names": [],
            "include_columns": [],
            "dialect_options": {},
        }

        do = current["dialect_options"]
        index_type = row["type"]
        if index_type in {1, 2}:
            do["mssql_clustered"] = index_type == 1
        if index_type in {5, 6}:
            do["mssql_clustered"] = index_type == 5
            do["mssql_columnstore"] = True
        if row["filter_definition"] is not None:
            do["mssql_where"] = row["filter_definition"]

    rp = connection.execution_options(future_result=True).execute(
        sql.text(
            """
select
    ind_col.index_id,
    col.name,
    ind_col.is_included_column
from
    sys.columns as col
join sys.tables as tab on
    tab.object_id = col.object_id
join sys.index_columns as ind_col on
    ind_col.column_id = col.column_id
    and ind_col.object_id = tab.object_id
join sys.schemas as sch on
    sch.schema_id = tab.schema_id
where
    tab.name = :tabname
    and sch.name = :schname
order by
    ind_col.index_id,
    ind_col.key_ordinal
        """
        )
        .bindparams(
            sql.bindparam("tabname", tablename, ischema.CoerceUnicode()),
            sql.bindparam("schname", owner, ischema.CoerceUnicode()),
        )
        .columns(name=sqltypes.Unicode())
    )
    
    for row in rp.mappings():
        if row["index_id"] not in indexes:
            continue
        index_def = indexes[row["index_id"]]
        is_colstore = index_def["dialect_options"].get("mssql_columnstore")
        is_clustered = index_def["dialect_options"].get("mssql_clustered")
        if not (is_colstore and is_clustered):
            if row["is_included_column"] and not is_colstore:
                index_def["include_columns"].append(row["name"])
            else:
                index_def["column_names"].append(row["name"])
    
    for index_info in indexes.values():
        index_info["dialect_options"]["mssql_include"] = index_info["include_columns"]

    if indexes:
        return list(indexes.values())
    else:
        return self._default_or_error(
            connection, tablename, owner, reflection.ReflectionDefaults.indexes, **kw
        )

mssql_base.MSDialect.get_indexes = patched_get_indexes

[sqla-tester]

Allen Chen has proposed a fix for this issue in the main branch:

Fix mssql index column order https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6184

[sqla-tester]

Allen Chen has proposed a fix for this issue in the rel_2_0 branch:

Fix mssql index column order https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6193