From cf609c19bccc74c0dba38d2fc4976df3a205f3f6 Mon Sep 17 00:00:00 2001
From: mollardthomas <mollardthomas@gmail.com>
Date: Fri, 3 May 2019 14:43:12 +0200
Subject: [PATCH] Add support for filtered indexes for mssql dialect

---
 lib/sqlalchemy/dialects/mssql/base.py | 20 +++++++++++++++++++-
 test/dialect/mssql/test_compiler.py   |  9 +++++++++
 2 files changed, 28 insertions(+), 1 deletion(-)

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 507fcfdcb5..f18c72b8cc 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -540,6 +540,16 @@ names::
 
 would render the index as ``CREATE INDEX my_index ON table (x) INCLUDE (y)``
 
+Filtered Indexes
+^^^^^^^^^^^^^^^^
+
+The ``mssql_where`` option renders WHERE(condition) for the given string
+names::
+
+    Index("my_index", table.c.x, mssql_where=table.c.x > 10)
+
+would render the index as ``CREATE INDEX my_index ON table (x) WHERE x > 10``
+
 Index ordering
 ^^^^^^^^^^^^^^
 
@@ -1950,6 +1960,14 @@ class MSDDLCompiler(compiler.DDLCompiler):
             ),
         )
 
+        whereclause = index.dialect_options["mssql"]["where"]
+
+        if whereclause is not None:
+            where_compiled = self.sql_compiler.process(
+                whereclause, include_table=False, literal_binds=True
+            )
+            text += " WHERE " + where_compiled
+
         # handle other included columns
         if index.dialect_options["mssql"]["include"]:
             inclusions = [
@@ -2182,7 +2200,7 @@ class MSDialect(default.DefaultDialect):
     construct_arguments = [
         (sa_schema.PrimaryKeyConstraint, {"clustered": None}),
         (sa_schema.UniqueConstraint, {"clustered": None}),
-        (sa_schema.Index, {"clustered": None, "include": None}),
+        (sa_schema.Index, {"clustered": None, "include": None, "where": None}),
         (sa_schema.Column, {"identity_start": 1, "identity_increment": 1}),
     ]
 
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 339cc75907..30a11d16b2 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -1129,6 +1129,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             schema.CreateIndex(idx), "CREATE CLUSTERED INDEX foo ON test (id)"
         )
 
+    def test_index_where(self):
+        metadata = MetaData()
+        tbl = Table("test", metadata, Column("data", Integer))
+        idx = Index("test_idx_data_1", tbl.c.data, mssql_where=tbl.c.data > 1)
+        self.assert_compile(
+            schema.CreateIndex(idx),
+            "CREATE INDEX test_idx_data_1 ON test (data) WHERE data > 1"
+        )
+
     def test_index_ordering(self):
         metadata = MetaData()
         tbl = Table(
-- 
2.47.2