Add SQL Server CI coverage

Change-Id: Ida0d01ae9bcc0573b86e24fddea620a38c962822

24 files changed, 166 insertions, 110 deletions

diff --git a/test/aaa_profiling/test_memusage.py b/test/aaa_profiling/test_memusage.py
index 3181cfe61..381e82d3c 100644
--- a/test/aaa_profiling/test_memusage.py
+++ b/test/aaa_profiling/test_memusage.py
@@ -246,7 +246,7 @@ class MemUsageTest(EnsureZeroed):
 class MemUsageWBackendTest(EnsureZeroed):
 
     __tags__ = 'memory_intensive',
-    __requires__ = 'cpython',
+    __requires__ = 'cpython', 'memory_process_intensive'
     __backend__ = True
 
     # ensure a pure growing test trips the assertion
diff --git a/test/dialect/mssql/test_query.py b/test/dialect/mssql/test_query.py
index 1164270e9..ef2a1426a 100644
--- a/test/dialect/mssql/test_query.py
+++ b/test/dialect/mssql/test_query.py
@@ -6,7 +6,7 @@ from sqlalchemy.testing import fixtures, AssertsCompiledSQL, assertions
 from sqlalchemy import testing
 from sqlalchemy.util import ue
 from sqlalchemy import util
-from sqlalchemy.testing.assertsql import CursorSQL
+from sqlalchemy.testing.assertsql import CursorSQL, DialectSQL
 from sqlalchemy import Integer, String, Table, Column, select, MetaData,\
     func, PrimaryKeyConstraint, desc, Sequence, DDL, ForeignKey, or_, and_
 from sqlalchemy import event
@@ -190,6 +190,8 @@ class QueryUnicodeTest(fixtures.TestBase):
     __only_on__ = 'mssql'
     __backend__ = True
 
+    @testing.requires.mssql_freetds
+    @testing.requires.python2
     def test_convert_unicode(self):
         meta = MetaData(testing.db)
         t1 = Table(
@@ -284,7 +286,7 @@ class QueryTest(testing.AssertsExecutionResults, fixtures.TestBase):
             meta.drop_all()
 
     @testing.provide_metadata
-    def test_disable_scope_identity(self):
+    def _test_disable_scope_identity(self):
         engine = engines.testing_engine(options={"use_scope_identity": False})
         metadata = self.metadata
         t1 = Table(
@@ -298,10 +300,11 @@ class QueryTest(testing.AssertsExecutionResults, fixtures.TestBase):
         with self.sql_execution_asserter(engine) as asserter:
             engine.execute(t1.insert(), {"data": "somedata"})
 
+        # TODO: need a dialect SQL that acts like Cursor SQL
         asserter.assert_(
-            CursorSQL(
-                "INSERT INTO t1 (data) VALUES (?)",
-                ("somedata", )
+            DialectSQL(
+                "INSERT INTO t1 (data) VALUES (:data)",
+                {"data": "somedata"}
             ),
             CursorSQL("SELECT @@identity AS lastrowid"),
         )
diff --git a/test/dialect/mssql/test_reflection.py b/test/dialect/mssql/test_reflection.py
index 2f705d8a3..d3b270d2f 100644
--- a/test/dialect/mssql/test_reflection.py
+++ b/test/dialect/mssql/test_reflection.py
@@ -130,26 +130,28 @@ class ReflectionTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL):
 
         dbname = testing.db.scalar("select db_name()")
         owner = testing.db.scalar("SELECT user_name()")
+        referred_schema = '%(dbname)s.%(owner)s' % {
+            "dbname": dbname, "owner": owner}
 
         inspector = inspect(testing.db)
         bar_via_db = inspector.get_foreign_keys(
-            "bar", schema="%s.%s" % (dbname, owner))
+            "bar", schema=referred_schema)
         eq_(
             bar_via_db,
             [{
                 'referred_table': 'foo',
                 'referred_columns': ['id'],
-                'referred_schema': 'test.dbo',
+                'referred_schema': referred_schema,
                 'name': 'fkfoo',
                 'constrained_columns': ['foo_id']}]
         )
 
-        assert testing.db.has_table("bar", schema="test.dbo")
+        assert testing.db.has_table("bar", schema=referred_schema)
 
         m2 = MetaData()
-        Table('bar', m2, schema="test.dbo", autoload=True,
-                                autoload_with=testing.db)
-        eq_(m2.tables["test.dbo.foo"].schema, "test.dbo")
+        Table('bar', m2, schema=referred_schema, autoload=True,
+                               autoload_with=testing.db)
+        eq_(m2.tables["%s.foo" % referred_schema].schema, referred_schema)
 
     @testing.provide_metadata
     def test_indexes_cols(self):
diff --git a/test/dialect/mssql/test_types.py b/test/dialect/mssql/test_types.py
index 841624303..f0402e8fb 100644
--- a/test/dialect/mssql/test_types.py
+++ b/test/dialect/mssql/test_types.py
@@ -8,6 +8,7 @@ from sqlalchemy import Table, Column, MetaData, Float, \
     Date, Time, DateTime, DefaultClause, PickleType, text, Text, \
     UnicodeText, LargeBinary
 from sqlalchemy import types, schema
+from sqlalchemy import util
 from sqlalchemy.databases import mssql
 from sqlalchemy.dialects.mssql.base import TIME, _MSDate
 from sqlalchemy.dialects.mssql.base import MS_2005_VERSION, MS_2008_VERSION
@@ -46,6 +47,8 @@ class TimeTypeTest(fixtures.TestBase):
 
 
 class MSDateTypeTest(fixtures.TestBase):
+    __only_on__ = 'mssql'
+    __backend__ = True
 
     def test_result_processor(self):
         expected = datetime.date(2000, 1, 2)
@@ -435,6 +438,8 @@ class TypeRoundTripTest(
         fixtures.TestBase, AssertsExecutionResults, ComparesTables):
     __only_on__ = 'mssql'
 
+    __backend__ = True
+
     @classmethod
     def setup_class(cls):
         global metadata
@@ -443,9 +448,6 @@ class TypeRoundTripTest(
     def teardown(self):
         metadata.drop_all()
 
-    @testing.fails_on_everything_except(
-        'mssql+pyodbc',
-        'mssql+mxodbc')
     def test_decimal_notation(self):
         numeric_table = Table(
             'numeric_table', metadata,
@@ -812,22 +814,6 @@ class TypeRoundTripTest(
                 engine.execute(tbl.delete())
 
 
-class MonkeyPatchedBinaryTest(fixtures.TestBase):
-    __only_on__ = 'mssql+pymssql'
-
-    def test_unicode(self):
-        module = __import__('pymssql')
-        result = module.Binary('foo')
-        eq_(result, 'foo')
-
-    def test_bytes(self):
-        module = __import__('pymssql')
-        input = b('\x80\x03]q\x00X\x03\x00\x00\x00oneq\x01a.')
-        expected_result = input
-        result = module.Binary(input)
-        eq_(result, expected_result)
-
-
 binary_table = None
 MyPickleType = None
 
@@ -837,6 +823,8 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults):
     """Test the Binary and VarBinary types"""
 
     __only_on__ = 'mssql'
+    __requires__ = "non_broken_binary",
+    __backend__ = True
 
     @classmethod
     def setup_class(cls):
@@ -874,6 +862,16 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults):
         binary_table.create(engine)
         return binary_table
 
+    def test_character_binary(self):
+        engine = testing.db
+        binary_table = self._fixture(engine)
+        with engine.connect() as conn:
+            conn.execute(
+                binary_table.insert(),
+                primary_id=1,
+                data=b("some normal data")
+            )
+
     def test_binary_legacy_types(self):
         self._test_binary(False)
 
@@ -980,7 +978,10 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults):
                 # the type we used here is 100 bytes
                 # so we will get 100 bytes zero-padded
                 paddedstream = list(stream2[0:99])
-                paddedstream.extend(['\x00'] * (100 - len(paddedstream)))
+                if util.py3k:
+                    paddedstream.extend([0] * (100 - len(paddedstream)))
+                else:
+                    paddedstream.extend(['\x00'] * (100 - len(paddedstream)))
                 eq_(
                     list(row['data_slice']), paddedstream
                 )
diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py
index 8437aca37..5263c79db 100644
--- a/test/engine/test_execute.py
+++ b/test/engine/test_execute.py
@@ -179,7 +179,8 @@ class ExecuteTest(fixtures.TestBase):
     @testing.fails_on_everything_except(
         'postgresql+psycopg2', 'postgresql+psycopg2cffi',
         'postgresql+pypostgresql', 'postgresql+pygresql',
-        'mysql+mysqlconnector', 'mysql+pymysql', 'mysql+cymysql')
+        'mysql+mysqlconnector', 'mysql+pymysql', 'mysql+cymysql',
+        'mssql+pymssql')
     def test_raw_python(self):
         def go(conn):
             conn.execute(
diff --git a/test/orm/inheritance/test_assorted_poly.py b/test/orm/inheritance/test_assorted_poly.py
index 51085763f..a6b5861ed 100644
--- a/test/orm/inheritance/test_assorted_poly.py
+++ b/test/orm/inheritance/test_assorted_poly.py
@@ -1542,10 +1542,10 @@ class Ticket2419Test(fixtures.DeclarativeMappedTest):
                         test_needs_autoincrement=True)
             b_id = Column(Integer, ForeignKey('b.id'))
 
-    @testing.fails_on("oracle",
-                      "seems like oracle's query engine can't "
-                      "handle this, not clear if there's an "
-                      "expression-level bug on our end though")
+    @testing.fails_on(["oracle", "mssql"],
+                      "Oracle / SQL server engines can't handle this, "
+                      "not clear if there's an expression-level bug on our "
+                      "end though")
     def test_join_w_eager_w_any(self):
         A, B, C, D, E = (self.classes.A,
                          self.classes.B,
diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py
index 007061d60..7fd9329f9 100644
--- a/test/orm/inheritance/test_basic.py
+++ b/test/orm/inheritance/test_basic.py
@@ -517,7 +517,7 @@ class SortOnlyOnImportantFKsTest(fixtures.MappedTest):
               Column('id', Integer, primary_key=True,
                      test_needs_autoincrement=True),
               Column('b_id', Integer,
-                     ForeignKey('b.id', use_alter=True, name='b')))
+                     ForeignKey('b.id', use_alter=True, name='b_fk')))
         Table('b', metadata,
               Column('id', Integer, ForeignKey('a.id'), primary_key=True))
 
diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py
index 213856cf6..e5234d254 100644
--- a/test/orm/inheritance/test_polymorphic_rel.py
+++ b/test/orm/inheritance/test_polymorphic_rel.py
@@ -94,7 +94,8 @@ class _PolymorphicTestBase(object):
             select([func.count('*')]).select_from(
                 sess.query(Person).with_polymorphic('*')
                 .options(joinedload(Engineer.machines))
-                .limit(2).offset(1).with_labels().subquery()
+                .order_by(Person.person_id).limit(2).offset(1)
+                .with_labels().subquery()
             ).scalar(), 2)
 
     def test_get_one(self):
diff --git a/test/orm/test_assorted_eager.py b/test/orm/test_assorted_eager.py
index 210d6ac39..affa14c0e 100644
--- a/test/orm/test_assorted_eager.py
+++ b/test/orm/test_assorted_eager.py
@@ -16,7 +16,6 @@ from sqlalchemy.orm import mapper, relationship, backref, create_session
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing import fixtures
 
-
 class EagerTest(fixtures.MappedTest):
     run_deletes = None
     run_inserts = "once"
@@ -25,13 +24,6 @@ class EagerTest(fixtures.MappedTest):
     @classmethod
     def define_tables(cls, metadata):
 
-        if testing.db.dialect.supports_native_boolean:
-            false = 'false'
-        else:
-            false = "0"
-
-        cls.other['false'] = false
-
         Table('owners', metadata,
               Column('id', Integer, primary_key=True,
                      test_needs_autoincrement=True),
@@ -55,7 +47,7 @@ class EagerTest(fixtures.MappedTest):
                      primary_key=True),
               Column('owner_id', Integer, ForeignKey('owners.id'),
                      primary_key=True),
-              Column('someoption', sa.Boolean, server_default=false,
+              Column('someoption', sa.Boolean, server_default=sa.false(),
                      nullable=False))
 
     @classmethod
@@ -216,17 +208,16 @@ class EagerTest(fixtures.MappedTest):
 
     @testing.crashes('sybase', 'FIXME: unknown, verify not fails_on')
     def test_without_outerjoin_literal(self):
-        Thing, tests, false = (self.classes.Thing,
-                               self.tables.tests,
-                               self.other.false)
+        Thing, tests= (self.classes.Thing,
+                               self.tables.tests)
 
         s = create_session()
         q = s.query(Thing).options(sa.orm.joinedload('category'))
         result = (q.filter(
                 (tests.c.owner_id == 1) &
                 text(
-                    'options.someoption is null or options.someoption=%s' %
-                    false)).join('owner_option'))
+                    'options.someoption is null or options.someoption=:opt'
+                ).bindparams(opt=False)).join('owner_option'))
 
         result_str = ["%d %s" % (t.id, t.category.name) for t in result]
         eq_(result_str, ['3 Some Category'])
diff --git a/test/orm/test_composites.py b/test/orm/test_composites.py
index cef0ad8d3..694e85819 100644
--- a/test/orm/test_composites.py
+++ b/test/orm/test_composites.py
@@ -430,7 +430,6 @@ class PrimaryKeyTest(fixtures.MappedTest):
         g2 = sess.query(Graph).get(Version(g.id, g.version_id))
         eq_(g.version, g2.version)
 
-    @testing.fails_on('mssql', 'Cannot update identity columns.')
     def test_pk_mutation(self):
         Graph, Version = self.classes.Graph, self.classes.Version
 
diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py
index 9bfa8e8eb..45656f3fc 100644
--- a/test/orm/test_froms.py
+++ b/test/orm/test_froms.py
@@ -755,7 +755,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL):
             order_by(User.id, adalias.c.id)
 
         def go():
-            eq_(self.static.user_address_result, q.order_by(User.id).all())
+            eq_(self.static.user_address_result, q.all())
         self.assert_sql_count(testing.db, go, 1)
         sess.expunge_all()
 
diff --git a/test/orm/test_hasparent.py b/test/orm/test_hasparent.py
index 2ea7f4e0f..2cb389f11 100644
--- a/test/orm/test_hasparent.py
+++ b/test/orm/test_hasparent.py
@@ -102,6 +102,7 @@ class ParentRemovalTest(fixtures.MappedTest):
 
         self._assert_not_hasparent(a1)
 
+    @testing.requires.updateable_autoincrement_pks
     @testing.requires.predictable_gc
     def test_stale_state_positive_pk_change(self):
         """Illustrate that we can't easily link a
diff --git a/test/orm/test_manytomany.py b/test/orm/test_manytomany.py
index 8fc73129e..f20c3db71 100644
--- a/test/orm/test_manytomany.py
+++ b/test/orm/test_manytomany.py
@@ -250,6 +250,7 @@ class M2MTest(fixtures.MappedTest):
                                    (Transition, [{'name': 'transition1'},
                                                  {'name': 'transition2'}])})
 
+    @testing.requires.updateable_autoincrement_pks
     @testing.requires.sane_multi_rowcount
     def test_stale_conditions(self):
         Place, Transition, place_input, place, transition = (
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 082b62300..19adf8983 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -2082,27 +2082,30 @@ class SliceTest(QueryTest):
         User = self.classes.User
 
         sess = create_session()
-        q = sess.query(User)
+        q = sess.query(User).order_by(User.id)
 
         self.assert_sql(
             testing.db, lambda: q[10:20], [
                 (
                     "SELECT users.id AS users_id, users.name "
-                    "AS users_name FROM users LIMIT :param_1 OFFSET :param_2",
+                    "AS users_name FROM users ORDER BY users.id "
+                    "LIMIT :param_1 OFFSET :param_2",
                     {'param_1': 10, 'param_2': 10})])
 
         self.assert_sql(
             testing.db, lambda: q[:20], [
                 (
                     "SELECT users.id AS users_id, users.name "
-                    "AS users_name FROM users LIMIT :param_1",
+                    "AS users_name FROM users ORDER BY users.id "
+                    "LIMIT :param_1",
                     {'param_1': 20})])
 
         self.assert_sql(
             testing.db, lambda: q[5:], [
                 (
                     "SELECT users.id AS users_id, users.name "
-                    "AS users_name FROM users LIMIT -1 OFFSET :param_1",
+                    "AS users_name FROM users ORDER BY users.id "
+                    "LIMIT -1 OFFSET :param_1",
                     {'param_1': 5})])
 
         self.assert_sql(testing.db, lambda: q[2:2], [])
@@ -2113,19 +2116,19 @@ class SliceTest(QueryTest):
             testing.db, lambda: q[-5:-2], [
                 (
                     "SELECT users.id AS users_id, users.name AS users_name "
-                    "FROM users", {})])
+                    "FROM users ORDER BY users.id", {})])
 
         self.assert_sql(
             testing.db, lambda: q[-5:], [
                 (
                     "SELECT users.id AS users_id, users.name AS users_name "
-                    "FROM users", {})])
+                    "FROM users ORDER BY users.id", {})])
 
         self.assert_sql(
             testing.db, lambda: q[:], [
                 (
                     "SELECT users.id AS users_id, users.name AS users_name "
-                    "FROM users", {})])
+                    "FROM users ORDER BY users.id", {})])
 
 
 class FilterTest(QueryTest, AssertsCompiledSQL):
@@ -4456,6 +4459,8 @@ class SessionBindTest(QueryTest):
         with self._assert_bind_args(session):
             session.query(func.max(User.score)).scalar()
 
+
+    @testing.requires.nested_aggregates
     def test_column_property_select(self):
         User = self.classes.User
         Address = self.classes.Address
diff --git a/test/orm/test_transaction.py b/test/orm/test_transaction.py
index 619e99abd..1510689f9 100644
--- a/test/orm/test_transaction.py
+++ b/test/orm/test_transaction.py
@@ -1,6 +1,7 @@
 from __future__ import with_statement
 from sqlalchemy import (
     testing, exc as sa_exc, event, String, Column, Table, select, func)
+from sqlalchemy.sql import elements
 from sqlalchemy.testing import (
     fixtures, engines, eq_, assert_raises, assert_raises_message,
     assert_warnings, mock, expect_warnings, is_, is_not_)
@@ -11,7 +12,6 @@ from sqlalchemy.testing.util import gc_collect
 from test.orm._fixtures import FixtureTest
 from sqlalchemy import inspect
 
-
 class SessionTransactionTest(fixtures.RemovesEvents, FixtureTest):
     run_inserts = None
     __backend__ = True
@@ -491,7 +491,9 @@ class SessionTransactionTest(fixtures.RemovesEvents, FixtureTest):
 
         def prevent_savepoint_rollback(
                 cursor, statement, parameters, context=None):
-            if "rollback to savepoint" in statement.lower():
+            if context is not None and context.compiled and isinstance(
+                    context.compiled.statement,
+                    elements.RollbackToSavepointClause):
                 raise rollback_error
 
         self.event_listen(
@@ -551,7 +553,9 @@ class SessionTransactionTest(fixtures.RemovesEvents, FixtureTest):
 
         def prevent_savepoint_rollback(
                 cursor, statement, parameters, context=None):
-            if "rollback to savepoint" in statement.lower():
+            if context is not None and context.compiled and isinstance(
+                    context.compiled.statement,
+                    elements.RollbackToSavepointClause):
                 raise rollback_error
 
         self.event_listen(testing.db, "handle_error", canary, retval=True)
diff --git a/test/orm/test_unitofwork.py b/test/orm/test_unitofwork.py
index 09f64c108..90616ae12 100644
--- a/test/orm/test_unitofwork.py
+++ b/test/orm/test_unitofwork.py
@@ -158,9 +158,6 @@ class UnicodeSchemaTest(fixtures.MappedTest):
     def teardown_class(cls):
         super(UnicodeSchemaTest, cls).teardown_class()
 
-    @testing.fails_on(
-        'mssql+pyodbc',
-        'pyodbc returns a non unicode encoding of the results description.')
     def test_mapping(self):
         t2, t1 = self.tables.t2, self.tables.t1
 
@@ -199,9 +196,6 @@ class UnicodeSchemaTest(fixtures.MappedTest):
         assert new_a1.t2s[0].d == b1.d
         session.expunge_all()
 
-    @testing.fails_on(
-        'mssql+pyodbc',
-        'pyodbc returns a non unicode encoding of the results description.')
     def test_inheritance_mapping(self):
         t2, t1 = self.tables.t2, self.tables.t1
 
@@ -241,10 +235,12 @@ class BinaryHistTest(fixtures.MappedTest, testing.AssertsExecutionResults):
         class Foo(cls.Basic):
             pass
 
+    @testing.requires.non_broken_binary
     def test_binary_equality(self):
         Foo, t1 = self.classes.Foo, self.tables.t1
 
-        data = b("this is some data")
+        #data = b("this is some data")
+        data = b'm\x18' #m\xf2\r\n\x7f\x10'
 
         mapper(Foo, t1)
 
@@ -639,7 +635,7 @@ class PassiveDeletesTest(fixtures.MappedTest):
 
 
 class BatchDeleteIgnoresRowcountTest(fixtures.DeclarativeMappedTest):
-    __requires__ = ('foreign_keys',)
+    __requires__ = ('foreign_keys', 'recursive_fk_cascade')
 
     @classmethod
     def setup_classes(cls):
diff --git a/test/orm/test_unitofworkv2.py b/test/orm/test_unitofworkv2.py
index 270c3708e..9c1a26e4b 100644
--- a/test/orm/test_unitofworkv2.py
+++ b/test/orm/test_unitofworkv2.py
@@ -1573,7 +1573,7 @@ class BasicStaleChecksTest(fixtures.MappedTest):
                     sess.flush
                 )
 
-    @testing.requires.sane_multi_rowcount
+    @testing.requires.sane_rowcount
     def test_delete_twice(self):
         Parent, Child = self._fixture()
         sess = Session()
diff --git a/test/orm/test_versioning.py b/test/orm/test_versioning.py
index ed5f78465..089541848 100644
--- a/test/orm/test_versioning.py
+++ b/test/orm/test_versioning.py
@@ -786,6 +786,7 @@ class NoBumpOnRelationshipTest(fixtures.MappedTest):
 
 class ColumnTypeTest(fixtures.MappedTest):
     __backend__ = True
+    __requires__ = 'sane_rowcount',
 
     @classmethod
     def define_tables(cls, metadata):
@@ -900,6 +901,7 @@ class RowSwitchTest(fixtures.MappedTest):
 
 class AlternateGeneratorTest(fixtures.MappedTest):
     __backend__ = True
+    __requires__ = 'sane_rowcount',
 
     @classmethod
     def define_tables(cls, metadata):
@@ -1581,6 +1583,7 @@ class ManualVersionTest(fixtures.MappedTest):
 class ManualInheritanceVersionTest(fixtures.MappedTest):
     run_define_tables = 'each'
     __backend__ = True
+    __requires__ = 'sane_rowcount',
 
     @classmethod
     def define_tables(cls, metadata):
diff --git a/test/requirements.py b/test/requirements.py
index 0362e28d1..9b01a22dd 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -54,7 +54,7 @@ class DefaultRequirements(SuiteRequirements):
         def mysql_not_mariadb_102(config):
             return against(config, "mysql") and (
                 not config.db.dialect._is_mariadb or
-                config.db.dialect.server_version_info < (5, 5, 5, 10, 2)
+                config.db.dialect._mariadb_normalized_version_info < (10, 2)
             )
 
         return self.check_constraints + fails_on(
@@ -103,6 +103,13 @@ class DefaultRequirements(SuiteRequirements):
             skip_if('mssql')
 
     @property
+    def recursive_fk_cascade(self):
+        """target database must support ON DELETE CASCADE on a self-referential
+        foreign key"""
+
+        return skip_if(["mssql"])
+
+    @property
     def deferrable_fks(self):
         """target database must support deferrable fks"""
 
@@ -192,6 +199,13 @@ class DefaultRequirements(SuiteRequirements):
                 )
 
     @property
+    def non_broken_binary(self):
+        """target DBAPI must work fully with binary values"""
+
+        # see https://github.com/pymssql/pymssql/issues/504
+        return skip_if(["mssql+pymssql"])
+
+    @property
     def binary_comparisons(self):
         """target database/driver can allow BLOB/BINARY fields to be compared
         against a bound parameter value.
@@ -227,10 +241,8 @@ class DefaultRequirements(SuiteRequirements):
 
         return skip_if(
             [
-                "mssql+pyodbc",
-                "mssql+mxodbc",
-                "mysql+mysqldb",
-                "mysql+pymysql"], "no driver support"
+                "mssql",
+                "mysql"], "no driver support"
         )
 
     @property
@@ -250,6 +262,17 @@ class DefaultRequirements(SuiteRequirements):
                     "independent connections")])
 
     @property
+    def memory_process_intensive(self):
+        """Driver is able to handle the memory tests which run in a subprocess
+        and iterate through hundreds of connections
+
+        """
+        return skip_if([
+            no_support("oracle", "Oracle XE usually can't handle these"),
+            no_support("mssql+pyodbc", "MS ODBC drivers struggle")
+        ])
+
+    @property
     def updateable_autoincrement_pks(self):
         """Target must support UPDATE on autoincrement/integer primary key."""
 
@@ -330,7 +353,10 @@ class DefaultRequirements(SuiteRequirements):
     @property
     def savepoints_w_release(self):
         return self.savepoints + skip_if(
-            "oracle", "oracle doesn't support release of savepoint")
+            ["oracle", "mssql"],
+            "database doesn't support release of savepoint"
+        )
+
 
     @property
     def schemas(self):
@@ -403,6 +429,15 @@ class DefaultRequirements(SuiteRequirements):
         )
 
     @property
+    def ctes_on_dml(self):
+        """target database supports CTES which consist of INSERT, UPDATE
+        or DELETE"""
+
+        return only_if(
+            ['postgresql']
+        )
+
+    @property
     def mod_operator_as_percent_sign(self):
         """target database must use a plain percent '%' as the 'modulus'
         operator."""
@@ -427,11 +462,23 @@ class DefaultRequirements(SuiteRequirements):
             ], 'no support for EXCEPT')
 
     @property
+    def order_by_col_from_union(self):
+        """target database supports ordering by a column from a SELECT
+        inside of a UNION
+
+        E.g.  (SELECT id, ...) UNION (SELECT id, ...) ORDER BY id
+
+        Fails on SQL Server
+
+        """
+        return fails_if('mssql')
+
+    @property
     def parens_in_union_contained_select_w_limit_offset(self):
         """Target database must support parenthesized SELECT in UNION
         when LIMIT/OFFSET is specifically present.
 
-        E.g. (SELECT ...) UNION (SELECT ..)
+        E.g. (SELECT ... LIMIT ..) UNION (SELECT .. OFFSET ..)
 
         This is known to fail on SQLite.
 
@@ -443,7 +490,7 @@ class DefaultRequirements(SuiteRequirements):
         """Target database must support parenthesized SELECT in UNION
         when OFFSET/LIMIT is specifically not present.
 
-        E.g. (SELECT ... LIMIT ..) UNION (SELECT .. OFFSET ..)
+        E.g. (SELECT ...) UNION (SELECT ..)
 
         This is known to fail on SQLite.  It also fails on Oracle
         because without LIMIT/OFFSET, there is currently no step that
@@ -549,12 +596,6 @@ class DefaultRequirements(SuiteRequirements):
                 util.py2k,
                 "bug in mysqlconnector 2.0"
             ),
-            LambdaPredicate(
-                lambda config: against(config, 'mssql+pyodbc') and
-                config.db.dialect.freetds and
-                config.db.dialect.freetds_driver_version < "0.91",
-                "older freetds doesn't support unicode DDL"
-            ),
             exclude('mysql', '<', (4, 1, 1), 'no unicode connection support'),
         ])
 
@@ -599,6 +640,13 @@ class DefaultRequirements(SuiteRequirements):
                                           'sybase', 'sqlite')
 
     @property
+    def nested_aggregates(self):
+        """target database can select an aggregate from a subquery that's
+        also using an aggregate"""
+
+        return skip_if(["mssql"])
+
+    @property
     def array_type(self):
         return only_on([
             lambda config: against(config, "postgresql") and
@@ -722,8 +770,7 @@ class DefaultRequirements(SuiteRequirements):
              ('sqlite', None, None, 'TODO'),
              ("firebird", None, None, "Precision must be from 1 to 18"),
              ("sybase+pysybase", None, None, "TODO"),
-             ('mssql+pymssql', None, None,
-              'FIXME: improve pymssql dec handling')]
+            ]
         )
 
     @property
@@ -892,15 +939,7 @@ class DefaultRequirements(SuiteRequirements):
 
     @property
     def mssql_freetds(self):
-        return only_on(
-            LambdaPredicate(
-                lambda config: (
-                    (against(config, 'mssql+pyodbc') and
-                     config.db.dialect.freetds)
-                    or against(config, 'mssql+pymssql')
-                )
-            )
-        )
+        return only_on(["mssql+pymssql"])
 
     @property
     def ad_hoc_engines(self):
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 3c4ccc050..1ef49bf04 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -634,12 +634,15 @@ class CTEDefaultTest(fixtures.TablesTest):
                 expected
             )
 
+    @testing.requires.ctes_on_dml
     def test_update_in_select(self):
         self._test_a_in_b("update", "select")
 
+    @testing.requires.ctes_on_dml
     def test_delete_in_select(self):
         self._test_a_in_b("update", "select")
 
+    @testing.requires.ctes_on_dml
     def test_insert_in_select(self):
         self._test_a_in_b("update", "select")
 
diff --git a/test/sql/test_insert_exec.py b/test/sql/test_insert_exec.py
index 6015f4e74..502ef6912 100644
--- a/test/sql/test_insert_exec.py
+++ b/test/sql/test_insert_exec.py
@@ -158,6 +158,7 @@ class InsertExecTest(fixtures.TablesTest):
             {'id': 'hi', 'foo': 'thisisfoo', 'bar': "thisisbar"}
         )
 
+    @testing.requires.sequences
     def test_lastrow_accessor_four(self):
         metadata = MetaData()
         self._test_lastrow_accessor(
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 28300855f..afb113748 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -503,9 +503,7 @@ class QueryTest(fixtures.TestBase):
 
     @testing.fails_on('firebird', "uses sql-92 rules")
     @testing.fails_on('sybase', "uses sql-92 rules")
-    @testing.fails_if(
-        lambda: testing.against('mssql+pyodbc') and not
-        testing.db.dialect.freetds, "uses sql-92 rules")
+    @testing.skip_if(['mssql'])
     def test_bind_in(self):
         """test calling IN against a bind parameter.
 
diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py
index 48fe28861..41092efe9 100644
--- a/test/sql/test_resultset.py
+++ b/test/sql/test_resultset.py
@@ -523,6 +523,7 @@ class ResultProxyTest(fixtures.TablesTest):
         eq_(result.fetchone(), None)
         assert connection.closed
 
+    @testing.requires.updateable_autoincrement_pks
     def test_connectionless_autoclose_no_metadata(self):
         result = testing.db.execute("update users set user_id=5")
         connection = result.connection
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index fdcf53c27..b6cc04322 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -344,13 +344,18 @@ class UserDefinedTest(fixtures.TablesTest, AssertsCompiledSQL):
             def get_col_spec(self):
                 return "BAR"
 
+        t = Table('t', MetaData(), Column('bar', MyType, nullable=False))
+
         self.assert_compile(
-            ddl.CreateColumn(Column('bar', MyType)),
-            "bar FOOB bar"
+            ddl.CreateColumn(t.c.bar),
+            "bar FOOB bar NOT NULL"
         )
+
+        t = Table('t', MetaData(),
+                  Column('bar', MyOtherType, nullable=False))
         self.assert_compile(
-            ddl.CreateColumn(Column('bar', MyOtherType)),
-            "bar BAR"
+            ddl.CreateColumn(t.c.bar),
+            "bar BAR NOT NULL"
         )
 
     def test_typedecorator_literal_render_fallback_bound(self):
@@ -1165,7 +1170,7 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
 
         Table(
             'non_native_enum_table', metadata,
-            Column("id", Integer, primary_key=True),
+            Column("id", Integer, primary_key=True, autoincrement=False),
             Column('someenum', Enum('one', 'two', 'three', native_enum=False)),
             Column('someotherenum',
                    Enum('one', 'two', 'three',
@@ -1369,7 +1374,7 @@ class EnumTest(AssertsCompiledSQL, fixtures.TablesTest):
     @testing.requires.enforces_check_constraints
     def test_check_constraint(self):
         assert_raises(
-            (exc.IntegrityError, exc.ProgrammingError),
+            (exc.IntegrityError, exc.ProgrammingError, exc.OperationalError),
             testing.db.execute,
             "insert into non_native_enum_table "
             "(id, someenum) values(1, 'four')")
@@ -1614,6 +1619,7 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults):
     def teardown_class(cls):
         metadata.drop_all()
 
+    @testing.requires.non_broken_binary
     def test_round_trip(self):
         testobj1 = pickleable.Foo('im foo 1')
         testobj2 = pickleable.Foo('im foo 2')
@@ -2399,10 +2405,10 @@ class TestKWArgPassThru(AssertsCompiledSQL, fixtures.TestBase):
                 return "FOOB %s" % kw['type_expression'].name
 
         m = MetaData()
-        t = Table('t', m, Column('bar', MyType))
+        t = Table('t', m, Column('bar', MyType, nullable=False))
         self.assert_compile(
             ddl.CreateColumn(t.c.bar),
-            "bar FOOB bar"
+            "bar FOOB bar NOT NULL"
         )