Differences between system temporary tables and user temporary tables

In the past, when writing SP in SQL, for example, in complex cases, I like to use temporary tables in the middle to store related records. This has many advantages, which increase efficiency and improve Program . Of course, after the use of temporary tables, generally use the user temporary table, that is, # temptable, but in some cases, occasionally use the system temporary table, that is, # temptable.

The syntax used for the two temporary tables is similar. You can use several methods to create a temporary table, or select.

Of course, the key is the difference between the system temporary table and the user temporary table: (as follows)
1) user temporary table: the name of the user temporary table starts with a single numeric symbol;
The user temporary table is only visible to the user's session who created the table and invisible to other processes.
The temporary table is automatically deleted when the process for creating it disappears.
2) system temporary table: the name of the system temporary table starts with a numerical symbol (##).
The global temporary table is visible to the entire SQL server instance, but it is automatically deleted when all sessions accessing it disappear.

Once you understand this, you will know their purpose and restrictions. However, there are some problems that are still prone to problems, so we will list them here.

1. When exec (sqlscript) is used to execute a script, it is also equivalent to a separate process. Therefore, if you create a user temporary table during the execution period, it will end after the execution is complete, that is, after execution, you cannot use the user temporary table generated in sqlscript, instead of the system temporary table.

2. When using a user temporary table, you should note that it is best to specify the user as DBO at the time of creation to avoid possible problems;

3. When using a temporary system table, you must consider that it cannot be used in Environment functions or systems used by multiple users. Otherwise, a conflict may occur, leading to unpredictable results.

If you use a system temporary table in a multi-user environment, multiple users may simultaneously process the same system temporary table, resulting in conflicts and data errors. I didn't pay attention to this before, so I wasted a lot of time.

Sometimes exec (sqlscript) is used to generate data, but only temporary tables can be used. How can this problem be solved?

1. You can define a user temporary table in advance, and then add the result records generated by Exec to the user temporary table using the insert # temptable exec (sqlscript) method, avoid using temporary tables;
2. Avoid using exec () instead of using other methods.

Exec () is used because some scripts are complicated, and some character combinations are required, such as in ('','', '') or other possible situations. In this case, you cannot directly use a general script to generate a record. You can only generate a combined script and then execute it with exec.