Should I always use 'implicit_returning':False in SQLAlchemy?

Question

What's the potential pitfall of always using 'implicit_returning': False in SQLAlchemy?

I've encountered problems a number of times when working on MSSQL tables that have triggers defined, and since the DB is in replication, all of the tables have triggers.

I'm not sure now what the problem exactly is. It has something to do with auto-increment fields - maybe because I'm prefetching the auto-incremented value so I can insert it in another table.

If I don't set 'implicit_returning': False for the table, when I try to insert values, I get this error:

The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

So what if I put __table_args__ = {'implicit_returning': False} into all mapped classes just to be safe?

Particularly frustrating for me is that local DB I use for development & testing is not in replication and doesn't need that option, but the production DB is replicated so when I deploy changes they sometimes don't work. :)

Answer 1

As you probably already know, the cause of your predicament is described in SQLAlchemy Docs as the following:

SQLAlchemy by default uses OUTPUT INSERTED to get at newly generated primary key values via IDENTITY columns or other server side defaults. MS-SQL does not allow the usage of OUTPUT INSERTED on tables that have triggers. To disable the usage of OUTPUT INSERTED on a per-table basis, specify implicit_returning=False for each Table which has triggers.

If you set your SQLAlchemy engine to echo the SQL, you will see that by default, it does this:

INSERT INTO [table] (id, ...) OUTPUT inserted.[id] VALUES (...)

But if you disable implicit_returning, it does this instead:

INSERT INTO [table] (id, ...) VALUES (...); select scope_identity()

So the question, "Is there any harm in disabling implicit_returning for all tables just in case?" is really, "Is there any disadvantage to using SCOPE_IDENTITY() instead of OUTPUT INSERTED?"

I'm no expert, but I get the impression that although OUTPUT INSERTED is the preferred method these days, SCOPE_IDENTITY() is usually fine too. In the past, SQL Server 2008 (and maybe earlier versions too?) had a bug where SCOPE_IDENTITY sometimes didn't return the correct value, but I hear that has now been fixed (see this question for more detail). (On the other hand, other techniques like @@IDENTITY and IDENT_CURRENT() are still dangerous since they can return the wrong value in corner cases. See this answer and the others on that same page for more detail.)

The big advantage that OUTPUT INSERTED still has is that it can work for cases where you are inserting multiple rows via a single INSERT statement. Is that something you are doing with SQLAlchemy? Probably not, right? So it doesn't matter.

Note that if you are going to have to disable implicit_returning for many tables, you could avoid a bit of boilerplate by making a mixin for it (and whichever other columns and properties you want all of the tables to inherit):

class AutoincTriggerMixin():
    __table_args__ = {
        'implicit_returning': False
    }

    id = Column(Integer, primary_key=True, autoincrement=True)

class SomeModel(AutoincTriggerMixin, Base):
    some_column = Column(String(1000))
    ...

See this page in the SQLALchemy documentation for more detail. As an added bonus, it makes it more obvious which tables involve triggers.