From 22ede6eedce8fefdb2b81265e93f82b0960a5a57 Mon Sep 17 00:00:00 2001
From: Allen Chen <e2ne00@gmail.com>
Date: Thu, 2 Oct 2025 22:53:34 -0400
Subject: [PATCH] Fix mssql index column order

Fixed issue where the index reflection for SQL Server would
not correctly return the order of the column inside an index
when the order of the columns in the index did not match the
order of the columns in the table.
Pull request courtesy of Allen Chen.

Fixes: #12894
Closes: #12895
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12895
Pull-request-sha: bd9bd43219f35a29eaeee81fedea452afc64eb5d

Change-Id: I45ed30bbd0fcfd4f67cb2b682ecb3a18029be2b7
---
 doc/build/changelog/unreleased_20/12894.rst   |  9 ++++++++
 lib/sqlalchemy/dialects/mssql/base.py         |  3 +++
 lib/sqlalchemy/testing/requirements.py        |  5 +++++
 .../testing/suite/test_reflection.py          | 20 ++++++++++++++++++
 test/dialect/mssql/test_reflection.py         | 21 +++++++++++++++++++
 test/requirements.py                          |  4 ++++
 6 files changed, 62 insertions(+)
 create mode 100644 doc/build/changelog/unreleased_20/12894.rst

diff --git a/doc/build/changelog/unreleased_20/12894.rst b/doc/build/changelog/unreleased_20/12894.rst
new file mode 100644
index 0000000000..43cbd74b79
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/12894.rst
@@ -0,0 +1,9 @@
+.. change::
+    :tags: bug, mssql
+    :tickets: 12894
+
+    Fixed issue where the index reflection for SQL Server would
+    not correctly return the order of the column inside an index
+    when the order of the columns in the index did not match the
+    order of the columns in the table.
+    Pull request courtesy of Allen Chen.
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 96d4b520bc..001c076833 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -3479,6 +3479,9 @@ join sys.schemas as sch on
 where
     tab.name = :tabname
     and sch.name = :schname
+order by
+    ind_col.index_id,
+    ind_col.key_ordinal
             """
             )
             .bindparams(
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 580579a2d9..d4828712a6 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -796,6 +796,11 @@ class SuiteRequirements(Requirements):
         ASC/DESC but reflects them as expressions (like oracle)."""
         return exclusions.closed()
 
+    @property
+    def indexes_check_column_order(self):
+        """target database supports CREATE INDEX with column order check."""
+        return exclusions.closed()
+
     @property
     def indexes_with_expressions(self):
         """target database supports CREATE INDEX against SQL expressions."""
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index aa1a4e90a8..b4fc34a476 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -2699,6 +2699,26 @@ class ComponentReflectionTestExtra(ComparesIndexes, fixtures.TestBase):
             ],
         )
 
+    @testing.requires.indexes_check_column_order
+    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(
diff --git a/test/dialect/mssql/test_reflection.py b/test/dialect/mssql/test_reflection.py
index 733f29fef0..4569a5249f 100644
--- a/test/dialect/mssql/test_reflection.py
+++ b/test/dialect/mssql/test_reflection.py
@@ -750,6 +750,27 @@ class ReflectionTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL):
             CreateIndex(idx), "CREATE NONCLUSTERED INDEX idx_x ON t (x)"
         )
 
+    def test_index_column_order_clustered(self, metadata, connection):
+        """test for #12894"""
+        test_table = Table(
+            "t",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("x", Integer),
+            Column("y", Integer),
+            PrimaryKeyConstraint("id", mssql_clustered=False),
+        )
+        Index(
+            "idx_x",
+            test_table.c.y,
+            test_table.c.id,
+            test_table.c.x,
+            mssql_clustered=True,
+        )
+        metadata.create_all(connection)
+        indexes = testing.db.dialect.get_indexes(connection, "t", None)
+        eq_(indexes[0]["column_names"], ["y", "id", "x"])
+
     @testing.only_if("mssql>=12")
     def test_index_reflection_colstore_clustered(self, metadata, connection):
         t1 = Table(
diff --git a/test/requirements.py b/test/requirements.py
index 29051536d3..cf1c0f3825 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -673,6 +673,10 @@ class DefaultRequirements(SuiteRequirements):
             ]
         )
 
+    @property
+    def indexes_check_column_order(self):
+        return exclusions.open()
+
     @property
     def indexes_with_expressions(self):
         return only_on(["postgresql", "sqlite>=3.9.0", "oracle"])
-- 
2.47.3