Comparison between table variables and temporary tables:
-
- Temporary tables use hard disks (tempdb database), and table name variables occupy memory. Therefore, small data volumes are faster than table variables in memory. When there is a large amount of data, you cannot use table variables, which consumes too much memory. Temporary tables are suitable for large data volumes.
-
- Table variables are stored in the memory by default, which is fast. Therefore, if the data volume in the trigger and stored procedure is small, table variables should be used.
-
- The temporary table uses the hard disk by default. Generally, the speed is relatively slow. Is there no need to use the temporary table? No. When the data volume is large, if the table variable is used up, the memory will be exhausted and the tempdb space will be used up. In this way, the hard disk space will be used up, but the memory will be basically exhausted at the same time, increases the chance of memory calling and calling, but reduces the speed. In this case, we recommend that you allocate appropriate space for tempdb and then use a temporary table.
-
- For temporary tables, the table variables mainly take longer I/O time, but are less occupied by memory resources. When the data volume is large, the use of temporary tables has better performance than the table variables due to the low consumption of memory resources.
-
- Suggestion: Use table variables for triggers and user-defined functions. Most table variables are used for stored procedures. Temporary tables are used for special applications and large data volumes.
-
- Table variables have a clear scope. When the function, stored procedure, or batch processing of the table variables are defined, the table variables are automatically cleared.
-
- Using table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table.
-
- Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources.
-
- Table variables need to know the table structure in advance. For normal temporary tables, you can only use the same into as the table variables in the current session for convenience. Global temporary tables: you can use the drop to be displayed in temp in multiple sessions. (It is easier to create a temporary table without knowing the table structure)
-
- The global temporary table function cannot be achieved by table variables.
-
- Table variables do not need to be deleted, so there will be no naming conflicts. Temporary tables, especially global temporary tables, must be used to resolve naming conflicts.
- Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
-
- When creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.
-
- If the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.
-
- If a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.
-
- Exercise caution when using large temporary tables to connect to other large tables for query and modification, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in a statement.
