Is there a list of the statements that may force an automatic commit?

[VolkerBarth]

(That has somewhat come up as part of Bill's current FAQ...)

AFAIK, each SQL statement in the docs is documented with a "Side effect" paragraph that tells whether the statement will force an implicit (automatic) commit, cf. for the ALTER TRIGGER statement:

Side effects

Automatic commit.

However, it's not always that clear whether a statement will do so (and SET OPTION might be overseen, for example).

Sometimes it would be handy if the docs would also contain a list of all statements that may have an automatic commit as their side effect - similar to Glenn's list of all statements that may trigger an implicit checkpoint.

So I would suggest such an enhancement in the documentation.

[MarkCulp]

I have forwarded your suggestion to the documentation team.

[Former Member · Accepted solution]

Hi Volker, We do try to minimize the use of lists as stand-alone topics in the docs because they can fall out of date, and don't always contain enough context-specific information that may be required, causing confusion for readers.

In this specific case, go to DCX and narrow search scope (Scope tab) to the SQL Reference guide, and then search for "automatic commit", including the double quotes. The list returned should be what you need because we standardized to the wording "Automatic commit" in the Side Effects section of each applicable statement.

I'll look into what we're saying about SET OPTION.. Thank you!

[VolkerBarth · Accepted solution]

Laura, thanks for the detailed explanation, that search facility makes sense to me and seems more reliable than a possibly out-of-date list. - And I certainly would not have thought of system procedures that also may force an automatic commit.

---

BTW, is there a way to change the sort order of the search results? Currently, they seem to appear in somewhat random order:

• GRANT USAGE ON SEQUENCE statement
• DROP CERTIFICATE statement
• DROP REMOTE MESSAGE TYPE statement [SQL Remote]
• ...
• DROP MESSAGE statement
• sa_refresh_text_indexes system procedure
• REVOKE CONSOLIDATE statement [SQL Remote]
• ...

The CREATE statements are mostly at the end of the list. Apparently, it would be easier if all CREATE statements would be listed in a contiguous block...