Temporary tables and table variables in SQL Server

In SQL Server performance tuning, there is an unmatched problem: how do you work with temporary datasets in code that takes a long time or is frequently called? Table variables and temporary tables are two choices. Remember that a large number of temporary data set processing requirements have been seen in SQL Server application performance evaluation and tuning for a leading shipping company in the country, and their developers are not sure when to use temporary tables and when to use table variables, so they simply use temporary tables. In fact, both temporary tables and table variables have a specific applicable environment.

Show off some basic knowledge first:

　　Table variables

Variables are prefixed with @ or @@ 为 The table variable is one of the variables, and the other variable is called a scalar (which can be understood as a standard variable, that is, a variable of a standard data type, such as Integer int or date datetime). A table variable at the @ prefix is local, so it is accessible only in the current user session, and the table variable of the @@ 前缀 is global, usually system variables, such as @ @error represents the error number of the most recent T-SQL statement. Of course, because a table variable is a variable first, it can only survive in one batch, which is what we call the boundary, beyond which the table variable dies.

Table variables are stored in memory, and it is precisely because of this that SQL Server does not need to generate logs when all users access table variables. At the same time variables are not required to consider other session access problems, so there is no lock mechanism, for very busy systems, to avoid the use of locks can reduce a part of the system load.

Table variables There is another limitation is not to create an index, of course, there is no statistical problem, so when the user accesses the table variable there is no execution plan selection problem (that is, after the compilation phase there is no optimization stage), this feature is sometimes a good thing, and sometimes it can cause some trouble.

　　Temp table

Temporary objects are #为前缀 in # or #, temporary tables are one of the temporary objects, and temporary objects such as temporary stored procedures, temporary functions, and temporary objects are stored in tempdb. The temporary table with the # prefix is local, so it is accessible only in the current user session, and # #前缀的临时表是全局的, so all user sessions can be accessed. Temporary tables are session-bound, and the temporary table persists as long as the session that created the temporary table does not end, and of course the user can destroy the temporary table prematurely through the drop TABLE command in the session.

As we said earlier, temporary tables are stored in tempdb, so access to temporary tables is likely to result in physical IO, but it is also necessary to generate logs to ensure consistency when modifying, while locking mechanisms are also indispensable.

Another notable addition to the table variable is that temporary tables can create indexes or define statistics, so SQL Server needs to consider the issue of execution plan optimization when it processes statements that access temporary tables.

　　Table variables vs. temporary tables

	Table variables	Temp table
Where the data set is stored	Memory (regardless of being swapped to a paging file)	Disk (not considered to be cached in memory after access)
Do I need a log	Whether	Is
Whether the index can be created	Whether	Is
Is it possible to use statistical data	Whether	Is
Whether it can be accessed in multiple sessions	Whether	Is
Whether a lock mechanism is required	Whether	Is

　　Conclusion

In summary, you will find that temporary tables and table variables in the underlying processing mechanism is a lot of differences.

Briefly, we recommend using a table variable for a smaller, temporary calculation with a dataset. If the dataset is large, and if it is used in the code for ad hoc calculations, this temporary use is always a simple full-data-set scan with no optimizations to consider, such as aggregations that are not grouped or grouped (for example, Count, SUM, AVERAGE, Max, and so on), or you can consider using table variables. Using table variables Another consideration is the memory pressure of the application environment, and if the code is running a lot of instances, pay special attention to memory variables for memory consumption.

Generally for large datasets we recommend using temporal tables, creating indexes at the same time, or automating the creation and maintenance of SQL Server statistics (STATISITCS) to provide optimizations for accessing SQL statements. If you need to exchange data between multiple user sessions, the temporary table is the only option. It is important to note that because temporary tables are stored in tempdb, be aware of the tuning of tempdb.

Temporary tables and table variables in SQL Server