Shulou(Shulou.com)05/31 Report--

SQL Server table variables and temporary table what is the difference, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Table variables

Table variables were first introduced in SQL Server 2000. Specific definitions of table variables include column definitions, column names, data types, and constraints. Constraints that can be used in table variables include primary key constraints, unique constraints, NULL constraints, and CHECK constraints (foreign key constraints cannot be used in table variables). Statements that define table variables are a subset of table statements that are normally defined using Create Table. It's just that the table variables are defined by the DECLARE @ local_variable statement.

The characteristics of table variables: 1. Table variables have a specific scope (in the current batch statement, but not in any stored procedures and functions called by the current batch statement), and the table variables are automatically cleared after the batch ends. two。 Table variables produce fewer stored procedure recompilations than temporary tables. 3. Transactions against table variables take effect only when the data is updated, so fewer locks and logs are generated. 4. Because the scope of the table variable is so small and it is not part of the persistent part of the database, the transaction rollback does not affect the table variable.

Table variables can be used like normal tables in their scope. More specifically, table variables can be used in SELECT,DELETE,UPDATE,INSERT statements like normal tables or table expressions, but table variables cannot be used in statements such as "SELECT select_list INTO table_variable". In SQL Server2000, table variables cannot be used in statements such as INSERT INTO table_variable EXEC stored_procedure.

Table variables cannot do the following: 1. Although a table variable is one variable, it cannot be assigned to another variable. 2.check constraints, default values, and computed columns cannot refer to custom functions. 3. Constraints cannot be named. 4. Cannot Truncate table variables. 5. Explicit values cannot be inserted into identity columns (that is, table variables do not support SET IDENTITY_INSERT ON)

Let's play with table variables.

Define a table variable, insert a piece of data, and query:

DECLARE @ tb1 Table (Id int, Name varchar (20), Age int) INSERT INTO @ tb1 VALUES (1) 'Liu Bei', 22) SELECT * FROM @ tb1

The output is as follows:

Let's try some situations that do not meet the requirements, such as adding a table variable, adding a constraint and naming the constraint:

Let's try some situations that do not meet the requirements, such as adding a table variable, adding a constraint and naming the constraint:

ALTER TABLE @ tb1 ADD CONSTRAINT CN_AccountAge CHECK (Account_Age > 18);-- insertion age must be greater than 18

The SQL Server prompt error is as follows:

SQL Server does not support naming Constraint when defining table variables, nor does it support building Constraint for table variables after defining them.

More are not allowed, please check the above requirements.

II. Temporary watch

Before diving into the temporary table, let's take a look at the session (Session), where a session is simply a client-to-data engine connection. In SQL Server Management Studio, each query window establishes a connection with the database engine. An application can establish one or more connections to the database, in addition, the application may not release the connection until the end of the application, or it may establish a connection when it is needed to release the connection.

Temporary tables and tables created by Create Table statements have the same physical project, but temporary tables differ from normal tables in the following ways:

1. The name of a temporary table cannot exceed 116 characters. This is because the database engine automatically appends a string to the name of the temporary table in order to identify different sessions and establish different temporary tables.

2. The scope of the local temporary table (named with "#") is only within the current connection. From the point of view of establishing the local temporary table in the stored procedure, the local temporary table will be Drop under the following circumstances: a, display calling Drop Table statement b, when the local temporary table is created in the stored procedure, the end of the stored procedure means that the local temporary table is Drop. C. When the current session ends, all local temporary tables created within the session will be Drop.

3. The global temporary table (named with the name "#") is visible in all sessions, so check whether it exists before creating the global temporary table, otherwise, if it already exists, you will get the error of creating objects repeatedly. A. The global temporary table will not be referenced by other sessions after it is Drop,Drop after the session in which it was created. B. the reference is made at the statement level, such as: 1. Create a new query window and run the statement:

The copy code is as follows: CREATE TABLE # # temp (RowID int) INSERT INTO # # temp VALUES (3)

two。 Create a new query window again, referencing the global temporary table every 5 seconds

While 1mm 1 BEGIN SELECT * FROM # # temp WAITFOR delay '00VO 05' END

3. Go back to the first window and close it. 4. An error will be generated the next time the second window is referenced.

4. Temporary tables cannot be partitioned. 5. You cannot add foreign key constraints to temporary tables. 6. The data types of columns in temporary tables cannot be defined as having no custom data types defined in TempDb (custom data types are database-level objects, while temporary tables belong to TempDb). Since TempDb is automatically created after each SQL Server restart, you must use startup stored procedure to create custom data types for TempDb. You can also achieve this goal by modifying the Model database. 7. The XML column cannot be defined in the form of a XML collection unless the collection is already defined in TempDb. Temporary tables can be created either by the Create Table statement or by the "SELECT INTO # table" statement. You can also use statements like "INSERT INTO # table EXEC stored_procedure" for temporary tables. Temporary tables can have named constraints and indexes. However, when two users call the same stored procedure at the same time, an error such as "There is already an object named'in the database" will occur. So it's best not to name the created object, but to use what is assigned by the system that is unique in the TempDb.

Third, misunderstanding

Misunderstanding 1. Table variables are only in memory.

Misunderstanding 2. Temporary tables are stored only in physical media.

Both views are wrong, only if there is enough memory, table variables and temporary tables will be created and processed in memory. They can also be saved to disk at any time. Note that the name of the table variable is assigned by the system, and the first character "@" of the table variable is not a letter, so it is not a valid variable name. The system creates a system-assigned name for the table variable in TempDb, so any method of looking up the table variable in sysobjects or sys.tables will fail.

The correct method should be the one in my previous example, and I've seen a lot of people use the following query to look up table variables:

Select * from sysobjects where name like'#tempTables%'

The above code seems to be easy to use, but it can cause multi-user problems. You make two connections, create a temporary table in the first connection, run the above statement in the second window to see the temporary table created by the first connection, and if you try to manipulate the temporary table in the second connection, an error may occur because the temporary table does not belong to your session.

Misunderstanding 3. Table variables cannot have indexes.

This misunderstanding is equally wrong. Although once you have created a table variable, you cannot make DDL statements on it, including Create Index statements. However, you can index a table variable when it is defined) such as the following statement.

Declare @ MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

This statement will create a table variable that has a clustered index. Because the primary key has a corresponding clustered index, a system-named index will be created on the RowID column.

The following example shows how you can create a unique constraint on a column of a table variable and how to create a composite index.

Declare @ temp TABLE (RowID int NOT NULL, ColA int NOT NULL, ColB char (1) UNIQUE, PRIMARY KEY CLUSTERED (RowID, ColA))

1) SQL cannot create statistics for table variables, just as it can create statistics for temporary tables. This means that for table variables, the execution engine thinks there is only one row, which also means that the execution plan for table variables is not optimal. Although the estimated execution plan is 1 for both table variables and temporary tables, the actual execution plan for temporary tables changes with each recompilation of the stored procedure. If the temporary table does not exist, an error occurs when the execution plan is generated.

2) once a table variable is established, it cannot be manipulated by DDL statements. So if you need to index a table or add a column, you need a temporary table.

3) Table variables cannot use select. Into statement, while temporary tables can.

4) in SQL Server 2008, you can pass table variables into stored procedures as parameters. But temporary watches don't work. Table variables also do not work in SQL Server 2000 and 2005.

5) scope: table variables are only valid in the current batch and are not visible to any stored procedures nested in it, etc. Local temporary tables are only valid in the current session, which also includes nested stored procedures. But not visible to the parent stored procedure. The global temporary table can be visible in any session, but will DROP as the session that created it terminates, and other sessions can no longer reference the global temporary table.

6) collation: table variables use the collation of the current database, and temporary tables use the collation of TempDb. If they are not compatible, you also need to specify them in the query or table definition.

7) if you want to use table variables in dynamic SQL, you must define table variables in dynamic SQL. Temporary tables can be defined in advance and referenced in dynamic SQL.

Fourth, how to choose

Microsoft recommends using table variables, and if the number of rows in the table is very small, use table variables. Many "network experts" will tell you that 100 is a dividing line, because this is the beginning of the efficiency of statistics to create query plans. But I still want to tell you to test temporary tables and table variables for your specific needs. Many people use table variables in custom functions, and if you need to use primary keys and unique indexes in table variables, you will find that table variables containing thousands of rows still perform well. But if you need to join table variables and other tables, you will find that performance tends to be very poor due to inaccurate execution plans.

To prove this, please see the attachment to this article. The code in the attachment creates table variables and temporary tables. And load the Sales.SalesOrderDetail table of the AdventureWorks database. In order to get enough test data, I inserted the data in this table 10 times. Then take the ModifiedDate column as the condition to Join the temporary table and table variables with the original Sales.SalesOrderDetail table. From the statistical information, there is a significant difference in IO. In terms of time, it took more than 50 seconds for the table variable to make join, while the temporary table took only 8 seconds.

If you need to DLL a table after it is created, choose a temporary table.

There are many similarities between temporary tables and table variables. So sometimes there are no specific rules on which to choose. For any particular situation, you need to consider their respective advantages and disadvantages and do some performance tests. The table below will give you a more detailed reference for comparing its advantages and strategies.

Property table variable temporary table scope current batch processing current session, nested stored procedures, global: all sessions use scene custom functions, stored procedures, batch custom functions, stored procedures, batch creation mode DECLARE statement only. Can only be created through DECLEARE statements

CREATE TABLE statement

SELECT INTO statement.

Table name length up to 128 bytes up to 116 bytes column type

You can use custom data types

You can use XML collections

Custom data types and XML collections must be defined within TempDb Collation string collations inherited from the current database string collations inherited from TempDb database indexes must be indexed at table definition PRIMARY KEY, UNIQUE, NULL, CHECK constraints can be used, but PRIMARY KEY, UNIQUE, NULL, CHECK must be declared when the table is created. Constraints can be used and can be added at any time, but no foreign key constraints are allowed after the table is created using DDL (indexes, columns). Data insertion method INSERT statement (SQL 2000: INSERT/EXEC cannot be used).

INSERT statements, including INSERT/EXEC.

SELECT INTO statement.

Insert explicit values into identity columns (SET IDENTITY_INSERT). Do not support SET IDENTITY_INSERT statements support SET IDENTITY_INSERT statements Truncate table does not allow automatic destructing explicit calls to DROP TABLE statements after the end of destructing batch processing. Automatic destructing at the end of the current session (global temporary table: also includes when other session sentences are not in the reference table.) Transactions will only have transactions when updating the table, and the duration is shorter than the normal transaction length of the temporary table. Whether the recompilation of the variable length stored procedure of the table will cause recompilation and rollback will not be affected by the rollback will affect the statistics not to be created, so all the estimated rows are 1, so generating the execution plan will not accurately create statistics. The execution plan is generated from the actual number of rows. The stored procedure passed as a parameter is only in SQL Server2008, and user-defined table type. Exe must be predefined. Explicit naming of objects (indexes, constraints) is not allowed. Not allowed, but be aware of multi-user problems. Dynamic SQL must define table variables in dynamic SQL. Temporary tables can be defined before calling dynamic SQL.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.