There are apparently 2 problems with you constraint:
Msg 1046, Level 15, State 1, Line 244 Subqueries are not allowed in this context. Only scalar expressions are allowed.
What this means is that you can not use a subquery inside a check constraint, you can only validate such constraint row by row. Examples of valid check constraints would be:
CHECK ( x ...
From everything I'm reading this is only a DEFAULT option in this March 2020 doc from MS Azure.1:
In March 2020, Azure SQL Database will introduce new automatic tuning default settings designed to provide optimal database performance.
Existing servers that aren’t configured with automatic tuning will have the new default settings automatically applied and ...
You should be able to track the DTCTransaction event in SQL Trace or XEvents.
Or you could just shut down MSTDC (not in production, of course) and see what fails.
Also note that updates to .NET's SqlClient enables some distributed transaction scenarios with Azure SQL Database without MSDTC. See
Distributed transactions across cloud databases
Being on the Standard Tier (S0) is causing your queries to be throttled significantly, which is affecting the total runtime. Here are the times for all 5 statements in the batch, as viewed in Sentry One Plan Explorer:
As you can see, most all of the queries have a duration that's much longer than CPU time. This often means the queries are waiting on some ...
If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck?
The answer is NO because the step when SQL Server gets next identity value is outside of transaction. This is why when you perform ROLLBACK identity does not decrease and you can have gaps.
Please see answer to similar question here
You have brought alot of issues into your question and your question is almost impossible to answer the way you ask it. People don't like to mess around with "could you rescue me out of my situation" - questions. You have to reduce complexity and experiment yourcels and extract specific questions you cannot answer yourself, otherwise you see that your ...