Allow creating explicit non-clustered keys and indexes MS SQL.

mssql_clustered=False on Index, UniqueConstraint,
PrimaryKeyConstraint now renders NONCLUSTERED.
The default of mssql_clustered is now None.

Co-Authored-By: mike bayer <mike_mp@zzzcomputing.com>
Change-Id: Id6b8d840e355be8f8fa72360cb4b6d2617ba72cf
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/252

4 files changed, 98 insertions, 12 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index f58b3e328..709eaab5e 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,15 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: feature, mssql
+
+        The ``mssql_clustered`` flag available on :class:`.UniqueConstraint`,
+        :class:`.PrimaryKeyConstraint`, :class:`.Index` now defaults to
+        ``None``, and can be set to False which will render the NONCLUSTERED
+        keyword in particular for a primary key, allowing a different index to
+        be used as "clustered". Pull request courtesy Saulius Žemaitaitis.
+
+    .. change::
         :tags: feature, orm
         :tickets: 1311
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index 90f76fef0..b217f0420 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -2351,6 +2351,18 @@ the same thing.
 
 :ticket:`3504`
 
+Support for "non clustered" on primary key to allow clustered elsewhere
+-----------------------------------------------------------------------
+
+The ``mssql_clustered`` flag available on :class:`.UniqueConstraint`,
+:class:`.PrimaryKeyConstraint`, :class:`.Index` now defaults to ``None``, and
+can be set to False which will render the NONCLUSTERED keyword in particular
+for a primary key, allowing a different index to be used as "clustered".
+
+.. seealso::
+
+    :ref:`mssql_indexes`
+
 .. _change_3434:
 
 The legacy_schema_aliasing flag is now set to False
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 966700420..bc1ad5cdf 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -363,8 +363,6 @@ To generate a clustered index::
 
 which renders the index as ``CREATE CLUSTERED INDEX my_index ON table (x)``.
 
-.. versionadded:: 0.8
-
 To generate a clustered primary key use::
 
     Table('my_table', metadata,
@@ -386,7 +384,24 @@ Similarly, we can generate a clustered unique constraint using::
           UniqueConstraint("y", mssql_clustered=True),
           )
 
-  .. versionadded:: 0.9.2
+To explicitly request a non-clustered primary key (for example, when
+a separate clustered index is desired), use::
+
+    Table('my_table', metadata,
+          Column('x', ...),
+          Column('y', ...),
+          PrimaryKeyConstraint("x", "y", mssql_clustered=False))
+
+which will render the table, for example, as::
+
+  CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
+                         PRIMARY KEY NONCLUSTERED (x, y))
+
+.. versionchanged:: 1.1 the ``mssql_clustered`` option now defaults
+   to None, rather than False.  ``mssql_clustered=False`` now explicitly
+   renders the NONCLUSTERED clause, whereas None omits the CLUSTERED
+   clause entirely, allowing SQL Server defaults to take effect.
+
 
 MSSQL-Specific Index Options
 -----------------------------
@@ -1420,8 +1435,12 @@ class MSDDLCompiler(compiler.DDLCompiler):
             text += "UNIQUE "
 
         # handle clustering option
-        if index.dialect_options['mssql']['clustered']:
-            text += "CLUSTERED "
+        clustered = index.dialect_options['mssql']['clustered']
+        if clustered is not None:
+            if clustered:
+                text += "CLUSTERED "
+            else:
+                text += "NONCLUSTERED "
 
         text += "INDEX %s ON %s (%s)" \
             % (
@@ -1464,8 +1483,12 @@ class MSDDLCompiler(compiler.DDLCompiler):
                     self.preparer.format_constraint(constraint)
         text += "PRIMARY KEY "
 
-        if constraint.dialect_options['mssql']['clustered']:
-            text += "CLUSTERED "
+        clustered = constraint.dialect_options['mssql']['clustered']
+        if clustered is not None:
+            if clustered:
+                text += "CLUSTERED "
+            else:
+                text += "NONCLUSTERED "
 
         text += "(%s)" % ', '.join(self.preparer.quote(c.name)
                                    for c in constraint)
@@ -1481,8 +1504,12 @@ class MSDDLCompiler(compiler.DDLCompiler):
                     self.preparer.format_constraint(constraint)
         text += "UNIQUE "
 
-        if constraint.dialect_options['mssql']['clustered']:
-            text += "CLUSTERED "
+        clustered = constraint.dialect_options['mssql']['clustered']
+        if clustered is not None:
+            if clustered:
+                text += "CLUSTERED "
+            else:
+                text += "NONCLUSTERED "
 
         text += "(%s)" % ', '.join(self.preparer.quote(c.name)
                                    for c in constraint)
@@ -1576,13 +1603,13 @@ class MSDialect(default.DefaultDialect):
 
     construct_arguments = [
         (sa_schema.PrimaryKeyConstraint, {
-            "clustered": False
+            "clustered": None
         }),
         (sa_schema.UniqueConstraint, {
-            "clustered": False
+            "clustered": None
         }),
         (sa_schema.Index, {
-            "clustered": False,
+            "clustered": None,
             "include": None
         })
     ]
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 599820492..92ff18069 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -650,6 +650,44 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "PRIMARY KEY CLUSTERED (x, y))"
         )
 
+    def test_table_pkc_explicit_nonclustered(self):
+        metadata = MetaData()
+        tbl = Table('test', metadata,
+                    Column('x', Integer, autoincrement=False),
+                    Column('y', Integer, autoincrement=False),
+                    PrimaryKeyConstraint("x", "y", mssql_clustered=False))
+        self.assert_compile(
+            schema.CreateTable(tbl),
+            "CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NOT NULL, "
+            "PRIMARY KEY NONCLUSTERED (x, y))"
+        )
+
+    def test_table_idx_explicit_nonclustered(self):
+        metadata = MetaData()
+        tbl = Table(
+            'test', metadata,
+            Column('x', Integer, autoincrement=False),
+            Column('y', Integer, autoincrement=False)
+        )
+
+        idx = Index("myidx", tbl.c.x, tbl.c.y, mssql_clustered=False)
+        self.assert_compile(
+            schema.CreateIndex(idx),
+            "CREATE NONCLUSTERED INDEX myidx ON test (x, y)"
+        )
+
+    def test_table_uc_explicit_nonclustered(self):
+        metadata = MetaData()
+        tbl = Table('test', metadata,
+                    Column('x', Integer, autoincrement=False),
+                    Column('y', Integer, autoincrement=False),
+                    UniqueConstraint("x", "y", mssql_clustered=False))
+        self.assert_compile(
+            schema.CreateTable(tbl),
+            "CREATE TABLE test (x INTEGER NULL, y INTEGER NULL, "
+            "UNIQUE NONCLUSTERED (x, y))"
+        )
+
     def test_table_uc_clustering(self):
         metadata = MetaData()
         tbl = Table('test', metadata,