The difference between a SQL Server temp table and a table variable

Temporary tables and table variables can play a "temporary" role, so what are the main differences between the two?

This is not discussed here, as well as the global temporary table, the session temporary table these, the main record of the individual on the main difference between the two and the application of the view, there is nothing wrong or complementary places, welcome to discuss.

Difference:

1. Table variables are stored in memory, and when a table variable is created, SQL Server does not generate logs, does not maintain statistics, the table variable's fields cannot be indexed, and cannot have constraints and default values. SQL Server thinks that table variables typically have very little data.

2. When a temporary table is created, SQL Server generates logs, statistics, and the fields of the temporary table can be indexed, so it can store relatively large amounts of data, can have constraints and defaults, and has a locking mechanism. It can be said that the temporary table is almost the same as the actual table, except that it is lightweight and temporary and can be created and destroyed when desired.

Applicable situation:

Table variable: If it is a small amount of data, then use a table variable. The cost of using table variables is less than temporary tables. Where the actual project is used, such as bulk deletion, you need to pass multiple IDs as parameters, some people will splice the ID string and then parse it in the database; My approach is usually to pass the XML, then use XML to generate the table variable, and then link the table to delete.

Temporary tables: Multiple table links the query gets a small result set that needs to be used multiple times in this session or multiple sessions.

The difference between a SQL Server temp table and a table variable