Add support for "real" sequences in mssql

Added support for "CREATE SEQUENCE" and full :class:`.Sequence` support for
Microsoft SQL Server.  This removes the deprecated feature of using
:class:`.Sequence` objects to manipulate IDENTITY characteristics which
should now be performed using ``mssql_identity_start`` and
``mssql_identity_increment`` as documented at :ref:`mssql_identity`. The
change includes a new parameter :paramref:`.Sequence.data_type` to
accommodate SQL Server's choice of datatype, which for that backend
includes INTEGER and BIGINT.   The default starting value for SQL Server's
version of :class:`.Sequence` has been set at 1; this default is now
emitted within the CREATE SEQUENCE DDL for all backends.

Fixes: #4235
Fixes: #4633
Change-Id: I6aa55c441e8146c2f002e2e201a7f645e667b916

1 files changed, 11 insertions, 0 deletions

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 31011a970..2d51e7c9b 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -19,6 +19,7 @@ import platform
 import sys
 
 from . import exclusions
+from . import fails_on_everything_except
 from .. import util
 
 
@@ -1189,3 +1190,13 @@ class SuiteRequirements(Requirements):
             lambda config: not config.db.dialect.supports_is_distinct_from,
             "driver doesn't support an IS DISTINCT FROM construct",
         )
+
+    @property
+    def emulated_lastrowid_even_with_sequences(self):
+        """"target dialect retrieves cursor.lastrowid or an equivalent
+        after an insert() construct executes, even if the table has a
+        Sequence on it..
+        """
+        return fails_on_everything_except(
+            "mysql", "sqlite+pysqlite", "sqlite+pysqlcipher", "sybase",
+        )