• Table Variable vs. Temp Table


     Table Variable vs. Temp Table Summary

    Feature

    Table Variable

    Temp Table

    Note

    Table Name

    Max 128 characters

    Max 116 characters

     

    Data Storage

    In memory and TempDB

    TempDB

     

    Meta Data

    In memory

    TempDB

    A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB.

    Scope

    Current batch

    Current session

    Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP.

    Constraints

    Allowed

    Allowed

    For table variables, since no DDL is allowed, constraints can not be created in separate DDL statements.

    DDL

    Not allowed

    Allowed.

    E.g. create Index on the temp table.

    Concurrent

    Supported

    Supported

    Constraints and Indexes with explicit name in a temp table cause duplicate name error.

    Statistics

    Not supported

    Supported

    Estimated row number in execution plan for table variable is always 1

    Parallel execution plan

    Supported only for select

    Supported

    Parallel query execution plans are not generated for queries that modify table variables.

    Transaction and Locking

    Not participated

    Participated

    Data in table variable is not affected if the transaction is rolled back

    Cause Recompile

    No

    Yes

    Temp Table creation causes SPs/batches to recompile

    SELECT INTO <t>

    Not supported

    Supported

     

    INSERT <t> EXEC

    Not supported

    Supported

     

    Use

    UDFs, Stored procedures, Triggers, Batches

    Stored procedures, Triggers, Batches

     Temp tables can't be used in UDFs.

  • 相关阅读:
    jmeter(二十三)分布式测试
    jmeter(二十二)内存溢出原因及解决方法
    jmeter(二十一)jmeter常用插件介绍
    jmeter(二十)阶梯式加压测试
    jmeter(十九)HTTP属性管理器
    《jmeter:菜鸟入门到进阶》系列
    jmeter(十八)关联之XPath Extractor
    <转>jmeter(十七)目录结构
    jmeter(十六)配置元件之计数器
    Springmvc中参数的绑定
  • 原文地址:https://www.cnblogs.com/rockniu/p/1281136.html
Copyright © 2020-2023  润新知