- SQL Server 2012 now recommends VARCHAR(max), NVARCHAR(max),

VARBINARY(max) for large text/binary types.  The MSSQL dialect will
now respect this based on version detection, as well as the new
``deprecate_large_types`` flag.
fixes #3039

1 files changed, 96 insertions, 9 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index dad02ee0f..5d84975c0 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -226,6 +226,53 @@ The DATE and TIME types are not available for MSSQL 2005 and
 previous - if a server version below 2008 is detected, DDL
 for these types will be issued as DATETIME.
 
+.. _mssql_large_type_deprecation:
+
+Large Text/Binary Type Deprecation
+----------------------------------
+
+Per `SQL Server 2012/2014 Documentation <http://technet.microsoft.com/en-us/library/ms187993.aspx>`_,
+the ``NTEXT``, ``TEXT`` and ``IMAGE`` datatypes are to be removed from SQL Server
+in a future release.   SQLAlchemy normally relates these types to the
+:class:`.UnicodeText`, :class:`.Text` and :class:`.LargeBinary` datatypes.
+
+In order to accommodate this change, a new flag ``deprecate_large_types``
+is added to the dialect, which will be automatically set based on detection
+of the server version in use, if not otherwise set by the user.  The
+behavior of this flag is as follows:
+
+* When this flag is ``True``, the :class:`.UnicodeText`, :class:`.Text` and
+  :class:`.LargeBinary` datatypes, when used to render DDL, will render the
+  types ``NVARCHAR(max)``, ``VARCHAR(max)``, and ``VARBINARY(max)``,
+  respectively.  This is a new behavior as of the addition of this flag.
+
+* When this flag is ``False``, the :class:`.UnicodeText`, :class:`.Text` and
+  :class:`.LargeBinary` datatypes, when used to render DDL, will render the
+  types ``NTEXT``, ``TEXT``, and ``IMAGE``,
+  respectively.  This is the long-standing behavior of these types.
+
+* The flag begins with the value ``None``, before a database connection is
+  established.   If the dialect is used to render DDL without the flag being
+  set, it is interpreted the same as ``False``.
+
+* On first connection, the dialect detects if SQL Server version 2012 or greater
+  is in use; if the flag is still at ``None``, it sets it to ``True`` or
+  ``False`` based on whether 2012 or greater is detected.
+
+* The flag can be set to either ``True`` or ``False`` when the dialect
+  is created, typically via :func:`.create_engine`::
+
+        eng = create_engine("mssql+pymssql://user:pass@host/db",
+                        deprecate_large_types=True)
+
+* Complete control over whether the "old" or "new" types are rendered is
+  available in all SQLAlchemy versions by using the UPPERCASE type objects
+  instead: :class:`.NVARCHAR`, :class:`.VARCHAR`, :class:`.types.VARBINARY`,
+  :class:`.TEXT`, :class:`.mssql.NTEXT`, :class:`.mssql.IMAGE` will always remain
+  fixed and always output exactly that type.
+
+.. versionadded:: 1.0.0
+
 .. _mssql_indexes:
 
 Clustered Index Support
@@ -367,19 +414,20 @@ import operator
 import re
 
 from ... import sql, schema as sa_schema, exc, util
-from ...sql import compiler, expression, \
-    util as sql_util, cast
+from ...sql import compiler, expression, util as sql_util
 from ... import engine
 from ...engine import reflection, default
 from ... import types as sqltypes
 from ...types import INTEGER, BIGINT, SMALLINT, DECIMAL, NUMERIC, \
     FLOAT, TIMESTAMP, DATETIME, DATE, BINARY,\
-    VARBINARY, TEXT, VARCHAR, NVARCHAR, CHAR, NCHAR
+    TEXT, VARCHAR, NVARCHAR, CHAR, NCHAR
 
 
 from ...util import update_wrapper
 from . import information_schema as ischema
 
+# http://sqlserverbuilds.blogspot.com/
+MS_2012_VERSION = (11,)
 MS_2008_VERSION = (10,)
 MS_2005_VERSION = (9,)
 MS_2000_VERSION = (8,)
@@ -545,6 +593,26 @@ class NTEXT(sqltypes.UnicodeText):
     __visit_name__ = 'NTEXT'
 
 
+class VARBINARY(sqltypes.VARBINARY, sqltypes.LargeBinary):
+    """The MSSQL VARBINARY type.
+
+    This type extends both :class:`.types.VARBINARY` and
+    :class:`.types.LargeBinary`.   In "deprecate_large_types" mode,
+    the :class:`.types.LargeBinary` type will produce ``VARBINARY(max)``
+    on SQL Server.
+
+    .. versionadded:: 1.0.0
+
+    .. seealso::
+
+        :ref:`mssql_large_type_deprecation`
+
+
+
+    """
+    __visit_name__ = 'VARBINARY'
+
+
 class IMAGE(sqltypes.LargeBinary):
     __visit_name__ = 'IMAGE'
 
@@ -683,8 +751,17 @@ class MSTypeCompiler(compiler.GenericTypeCompiler):
     def visit_unicode(self, type_):
         return self.visit_NVARCHAR(type_)
 
+    def visit_text(self, type_):
+        if self.dialect.deprecate_large_types:
+            return self.visit_VARCHAR(type_)
+        else:
+            return self.visit_TEXT(type_)
+
     def visit_unicode_text(self, type_):
-        return self.visit_NTEXT(type_)
+        if self.dialect.deprecate_large_types:
+            return self.visit_NVARCHAR(type_)
+        else:
+            return self.visit_NTEXT(type_)
 
     def visit_NTEXT(self, type_):
         return self._extend("NTEXT", type_)
@@ -717,7 +794,10 @@ class MSTypeCompiler(compiler.GenericTypeCompiler):
             return self.visit_TIME(type_)
 
     def visit_large_binary(self, type_):
-        return self.visit_IMAGE(type_)
+        if self.dialect.deprecate_large_types:
+            return self.visit_VARBINARY(type_)
+        else:
+            return self.visit_IMAGE(type_)
 
     def visit_IMAGE(self, type_):
         return "IMAGE"
@@ -1370,13 +1450,15 @@ class MSDialect(default.DefaultDialect):
                  query_timeout=None,
                  use_scope_identity=True,
                  max_identifier_length=None,
-                 schema_name="dbo", **opts):
+                 schema_name="dbo",
+                 deprecate_large_types=None, **opts):
         self.query_timeout = int(query_timeout or 0)
         self.schema_name = schema_name
 
         self.use_scope_identity = use_scope_identity
         self.max_identifier_length = int(max_identifier_length or 0) or \
             self.max_identifier_length
+        self.deprecate_large_types = deprecate_large_types
         super(MSDialect, self).__init__(**opts)
 
     def do_savepoint(self, connection, name):
@@ -1390,6 +1472,9 @@ class MSDialect(default.DefaultDialect):
 
     def initialize(self, connection):
         super(MSDialect, self).initialize(connection)
+        self._setup_version_attributes()
+
+    def _setup_version_attributes(self):
         if self.server_version_info[0] not in list(range(8, 17)):
             # FreeTDS with version 4.2 seems to report here
             # a number like "95.10.255".  Don't know what
@@ -1405,6 +1490,9 @@ class MSDialect(default.DefaultDialect):
             self.implicit_returning = True
         if self.server_version_info >= MS_2008_VERSION:
             self.supports_multivalues_insert = True
+        if self.deprecate_large_types is None:
+            self.deprecate_large_types = \
+                self.server_version_info >= MS_2012_VERSION
 
     def _get_default_schema_name(self, connection):
         if self.server_version_info < MS_2005_VERSION:
@@ -1592,12 +1680,11 @@ class MSDialect(default.DefaultDialect):
             if coltype in (MSString, MSChar, MSNVarchar, MSNChar, MSText,
                            MSNText, MSBinary, MSVarBinary,
                            sqltypes.LargeBinary):
+                if charlen == -1:
+                    charlen = 'max'
                 kwargs['length'] = charlen
                 if collation:
                     kwargs['collation'] = collation
-                if coltype == MSText or \
-                        (coltype in (MSString, MSNVarchar) and charlen == -1):
-                    kwargs.pop('length')
 
             if coltype is None:
                 util.warn(