Is it true that in MS SQL Server it is better to create temporary tables outside of a transaction?

Question

SQL

That is, first we created it, and then we already use it in a transaction? I read that if you create a temporary table in a transaction, then some system tables are locked for the duration of this transaction of mine. It's true? Is there info about this somewhere in Microsoft sources? I read here

Answer 1

Temporary tables create an entry in the tempdb database. they must be checked for existence, do not forget to drop them, and in general they are sometimes demanding on the code, especially for code that can throw an exception.
For single statements (one query SELECT,INSERT,UPDATE,DELETE) there is an alternative - Common Table Expressions (CTE) - link
Of course, if you need a temporary table for more than 1 query, this approach is not applicable.

Answer 2

There is a subtlety - distributed transactions. In MS SQL Server , a transaction is escalated even if within the transaction there is an access to two different databases on the same server. Since temporary tables are created in a separate database, under some conditions problems with MSDTC can occur. Escalation was a significant problem in 2005, but we ran into it not too long ago in 2008 .

Answer 3

No, this is not true, respectively, you will not find it on the Microsoft website

Answer 4

Never an SQL expert (because I'm a fool stack developer), so take my opinion critically. But the question is interesting.
I see no reason to refuse transactions in C# code if it is a legacy project. Surely there is more to do there. I myself tried not to use it, although it happened. Often, I wrapped a group of stored procedures with a transaction, which I called from C #.
What for to drag creation of the temporary table in transaction? For fear that she will not be cleansed? You can make a couple of storages for creating and deleting global temporary tables with an existence check, which are called before and after the transaction. Always adhered to the point of view - the less code in the transaction, the better.
I try not to use temporary tables. Although if the amount of temporary data is large and indexes are needed, why not. It happened that their abuse seriously squandered productivity, and vice versa. Recently, it was necessary to transfer data between the databases of the same instance. At first I used temporary tables. Then, on the advice of the architect, I rewrote and began to send the parameter through xml (there it fit well into the general outline).