The difference between a [MSSQL] table variable and a temporary table

Source: Internet
Author: User
Tags create index mssql sessions table definition management studio sql server management sql server management studio

First, table variables

Table variables were first introduced in SQL Server 2000. The 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 and normally use the CREATE table to define a subset of table statements. Only table variables are defined by DECLARE @local_variable statements.

Characteristics of Table variables:

  • Table variables have a specific scope (in the current batch statement, but not in the stored procedures and functions that are called by any current batch statement), and the table variable is automatically cleared after the batch finishes .
  • Table variables produce fewer stored procedure recompilation than temporary tables.
  • A transaction against a table variable takes effect only when the data is updated, so the number of locks and logs is generated less.
  • Because the scope of a table variable is so small and not part of the persistence of the database, a transaction rollback does not affect the table variable.
  • Table variables can be used as normal tables within their scope. Rather, a table variable can be used as a normal table or table expression in a Select,delete,update,insert statement, but the table variable cannot be in a similar "select select_list into table_ Variable "is used in statements like this. In SQL Server2000, table variables cannot be used in statements such as insert into Table_variable EXEC stored_procedure.

    The table variable cannot do the following things:

  • Although a table variable is a variable, it cannot be assigned to another variable.
  • Check constraints, default values, and computed columns cannot reference custom functions.
  • You cannot name a constraint.
  • You cannot truncate a table variable.
  • You cannot insert an explicit value into an identity column (that is, the table variable does not support set IDENTITY_INSERT on)
  • Let's play table variables below.

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

    DECLARE @tb1 Table(Idint, Namevarchar( -), ageint)INSERT  into @tb1 VALUES(1,'Liu Bei', A)SELECT *  from @tb1

    The output results are as follows:

      

    Try some non-conforming scenarios, such as adding a table variable, adding a constraint, and naming the constraint:

    ALTER TABLE @tb1    ADD CONSTRAINT cn_accountage     CHECK >) ;          --insertion age must be greater than

    The SQL Server prompt error is as follows:

      

    SQL Server does not support defining a table variable when it is named constraint, nor does it support the definition of a table variable after it is built constraint.

    More not allowed, please check the above requirements.

    Second, temporary table

    Before we dive into the temporal table, we need to know about the session, a session that is just a client-to-data engine connection. In SQL Server Management Studio, each query window establishes a connection to the database engine. An application can establish one or more connections to the database, and in addition, the application may establish a connection after it has not been released knowing that the application is finished, or it may establish a connection when the release connection is needed.

    The table created by the temporary table and the CREATE TABLE statement has the same physical engineering, but the temporary table differs from the normal table:

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

    2, the Local temporary table (named after "#") scope only within the current connection , from the point of view of establishing a local temporary table in the stored procedure, the local temporary table will be drop in the following cases:
    A. Show call to drop TABLE statement
    B, authorities temporary tables are created within a stored procedure, and the end of the stored procedure means that the local temporary table is drop.
    C, at the end of the current session, all local temporary tables created within the session will be drop.

    3, the Global temporary table (named after "# #") is visible in all sessions, so before creating a global temporary table, check if it exists, or if it already exists, you will get an error creating the object repeatedly.
    A, the global temporary table will be drop,drop after the session at which it was created, and other sessions will not be able to reference the global staging table.
    b, references are made at the statement level, such as:
    1. Create a new query window and run the statement:

    CREATE TABLE # #tempint)  INSERT into # #tempVALUES( 3)

    --Create a local temporary table
    CREATE TABLE #tmpStudent (Tid int,name varchar (), age int)
    INSERT into #tmpStudent values (' Xiaowang ',+)

    SELECT * FROM #tmpStudent

    --Create a local temporary table another way of writing
    SELECT * to #tmpStudent from student
    SELECT * FROM #tmpStudent

    --Another way to create:

    CREATE table tempdb. mytemptable (Tid int)--You can write this with a corresponding permission

    --Delete

    drop table #tmpStudent

    2. Create a new query window again, referencing the global temporary table every 5 seconds

     while 1 = 1 BEGIN SELECT *  from # #tempWAITFOR'00:00:05'END         

    3. Go back to the first window and close the window.
    4. The next time a second window is referenced, an error is generated.

    4. The staging table cannot be partitioned.

    5. You cannot add foreign key constraints to temporary tables.

    6. The data type of a column in a temporary table cannot be defined as a custom data type that is not defined in tempdb (the custom data type is a database-level object, and the temporary table belongs to tempdb). Because tempdb is automatically created after each SQL Server restart, you must use the startup stored procedure to create a custom data type for tempdb. You can also achieve this by modifying the model database.

    7. An XML column cannot be defined as a form of an XML collection unless the collection is already defined in tempdb.

    Temporary tables can be created either through the CREATE TABLE statement or through the Select <select_list> into #table statement. You can also use the "INSERT into #table EXEC stored_procedure" statement 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 a object named ' <objectname> ' in the database" will be generated. Therefore, it is best not to name the established objects, but to use the system-assigned unique in tempdb.

    Third, the misunderstanding

    Myth 1. Table variables are only in memory.

    Myth 2. Temporary tables are stored only on physical media.

    Both of these views are wrong, and only enough memory is available for table variables and temporary tables to 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 system-assigned, and the first character of the table variable "@" is not a letter, so it is not a valid variable name. A system-assigned name is created for the table variable in tempdb, so any method that looks up a table variable in sysobjects or sys.tables fails.

    The correct method should be the method in my previous example, and I see many people using the following query to look up the table variables:

    Select *  from where  like ' #tempTables% '

    The code looks good, but it creates a problem with multiple users. You build 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, you might get an error because the temporary table is not part of your session.

      Myth 3. A table variable cannot have an index.

    This myth is equally wrong. Although once you create a table variable, you cannot make DDL statements for it, which includes the CREATE INDEX statement. You can, however, create an index for a table variable when it is defined, such as the following statement.

    Declare @MyTableVariable Table 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 is created on the ROWID column.

    The following example shows that 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   char  (1 ) unique   primary  key  clustered  (RowID, ColA)) 

    1) SQL does not establish statistics for table variables, just as it can create statistics for temporary tables. This means that for a table variable, the execution engine thinks it has only 1 rows, which means that the execution plan for the table variable is not optimal. Although the estimated execution plan is 1 for both table variables and temporary tables, the actual execution plan is changed for temporary tables based on the recompilation of each 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 the SELECT ... into statement, while temporary tables can.

    4) in SQL Server 2008, you can pass a table variable as a parameter to a stored procedure. But the temp table doesn't work. Table variables do not work in SQL Server 2000 and 2005.

    5) Scope: The table variable is only valid in the current batch and is not visible to any stored procedures that are nested within it. Local temporary tables are only valid in the current session, which also includes nested stored procedures. However, the parent stored procedure is not visible. The global temporary table can be visible in any session, but it will drop as the session that created it terminates, and other sessions will no longer be able to reference the global staging table.

    6) Collation: Table variables use the collation of the current database, and temporary tables use the collation of tempdb. If they are incompatible, 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.

    Iv. How to choose

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

    To prove this, please see the annex to this article. The code in the attachment creates table variables and temporary tables. The Sales.SalesOrderDetail table for the AdventureWorks database is loaded. In order to get enough test data, I inserted the data in this table 10 times. The ModifiedDate column is then used as a condition to join the temporary table and table variables with the original Sales.SalesOrderDetail table, and the IO differs significantly from the statistics. It took more than 50 seconds to make a join from the time table variable, and the temporary table took only 8 seconds.

    If you need to perform DLL operations on the table after the table is established, select the temporary table.

    Temporary tables and table variables have a lot in the same place. So sometimes there are no specific rules on how to choose which one. For any particular situation, you need to consider their pros and cons and do some performance testing. The table below will give you a more detailed reference to the advantages.

    V. Summary

    Characteristics Table variables Temp table
    Scope Current batch Processing Current session, nested stored procedure, global: all Sessions
    Usage Scenarios Custom functions, stored procedures, batch processing Custom functions, stored procedures, batch processing
    How to create DECLARE statement only. Can only be created by Decleare statements

    CREATE TABLE Statement

    SELECT into statement.

    Table name length Up to 128 bytes Up to 116 bytes
    Column type

    You can use the custom data type

    You can use the XML collection

    Custom data types and XML collections must be defined within tempdb
    Collation String collation inherits from the current database String collation inherits from the tempdb database
    Index The index must be established when the table is defined Indexes can be established after a table is created
    Constraints PRIMARY KEY, UNIQUE, NULL, check constraint can be used, but must be declared when the table is established PRIMARY KEY, UNIQUE, NULL, CHECK. Constraints can be used, can be added after any time, but cannot have foreign key constraints
    Use DDL (index, column) after table creation Not allowed Allow.
    How data is inserted INSERT Statement (SQL 2000: Cannot use insert/exec).

    INSERT statements, including Insert/exec.

    SELECT into statement.

    Insert explicit values into identity columns (SET identity_insert). SET IDENTITY_INSERT statements are not supported Support for SET IDENTITY_INSERT statements
    Truncate table Not allowed Allow
    Destruction mode Automatic destruction after batch finishes Explicitly call the DROP TABLE statement.
    Current session End auto-destructor (Global temp table: Also included when other conversational sentences are not in the reference table.)
    Transaction Only have a transaction when the table is updated, the duration is shorter than the temporary table Normal transaction length, longer than table variable
    Stored Procedure re-compilation Whether can cause recompilation
    Rolling back Will not be affected by rollback Will be rolled back affect
    Statistical data No statistics are created, so all estimated rows are 1, so generating an execution plan is not accurate Create the statistics to generate the execution plan from the actual number of rows.
    Incoming stored procedure as a parameter Only in SQL Server2008, and the user-defined table type must be predefined. Not allowed
    Explicitly name an object (index, constraint). Not allowed Allow, but be aware of problems with multiple users
    Dynamic SQL Table variables must be defined in dynamic SQL You can define a temporary table before calling dynamic SQL

    The difference between a [MSSQL] table variable and a temporary table

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.