数据库中临时表,表变量和CTE使用优势极其区别

1	在写SQL时经常会用到临时表,表变量和CTE,这三者在使用时各有优势:

1. 临时表:分为局部临时表和全局临时表.

  1.1局部临时表,创建时以#开头,在系统数据库tempdb中存储. 在当前的链接可见,链接断开则临时表就自动被释放,也可以手动drop table #tmptable

在使用不同的链接同时创建相同的临时表时,互不影响,系统在tempdb中会自动附加以特定的session为标识的名字来区分. 常常在SP中使用,把需要操作的数据或者共同的数据取出放在临时表中,后续可以进行其他的操作(SELECT,UPDATE,DELETE,DROP等).

 可以像创建永久表一样创建临时表:

    
    

     
     CREATE
     
      
     
     TABLE
     
      #tmpTable
(
ID 
     
     INT
     
     ,
NAME 
     
     VARCHAR
     
     (
     
     10
     
     ),
COMPANY 
     
     VARCHAR
     
     (
     
     50
     
     )
)


     
     SELECT
     
      
     
     *
     
      
     
     FROM
     
      #tmpTable 
     
     JOIN
     
      ...


     
     DROP
     
      
     
     TABLE
     
      #tmpTable
    
    

也可以使用INTO创建临时表,如查询EmployeeID=1的所有订单,放在临时表中,以备后续的处理.

    
    

     
     SELECT
     
      E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate 

     
     INTO
     
      #tmpTable

     
     FROM
     
      Orders O 
     
     JOIN
     
      Employees E 
     
     ON
     
      O.EmployeeID
     
     =
     
     E.EmployeeID

     
     WHERE
     
      E.EmployeeID
     
     =
     
     1
    
    

 

 1.2全局临时表,创建时以##开头. 在tempdb中存储,对所有的session都可见.

    
    

     
     CREATE
     
      
     
     TABLE
     
      ##tmpTable2
(
ID 
     
     INT
     
     ,
NAME 
     
     VARCHAR
     
     (
     
     20
     
     ),
COMPANY 
     
     VARCHAR
     
     (
     
     50
     
     )
)

     
     SELECT
     
      
     
     *
     
      
     
     FROM
     
      ##tmpTable2 
     
     JOIN
     
      ...


     
     DROP
     
      
     
     TABLE
     
      ##tmpTable2
    
    

2.表变量:在内存中存储,比临时表执行速度快. 在SP或者function越过有效scope之后会自动释放,不用显式的写drop.表变量只可用在DML的操作中,会有比较多的限制.

      
      

       
       --
       
       直接声明表变量
       
       

       
       DECLARE
       
        
       
       @varTable
       
        
       
       TABLE
       
       
(
ID 
       
       INT
       
       ,
NAME 
       
       VARCHAR
       
       (
       
       20
       
       ),
COMPANY 
       
       VARCHAR
       
       (
       
       50
       
       )
)



       
       --
       
       先创建表类型
       
       

       
       CREATE
       
        TYPE 
       
       [
       
       dbo
       
       ]
       
       .
       
       [
       
       T_TEMP
       
       ]
       
        
       
       AS
       
        
       
       TABLE
       
       (
ID 
       
       INT
       
       ,
NAME 
       
       VARCHAR
       
       (
       
       20
       
       ),
COMPANY 
       
       VARCHAR
       
       (
       
       50
       
       )
)


       
       --
       
       在声明表变量
       
       

       
       DECLARE
       
        
       
       @varTable
       
        T_TEMP
      
      

3.CTE(Common Table Expressions)通用表表达:是一个可以由定义语句引用的临时命名的结果集,在它们的简单形式中,可将 CTE 视为类似于非持续性类型视图的派生表.只须定义 CTE 一次，即可在查询中多次引用.

     
     

      
      WITH
      
       CTE_NAME

      
      AS
      
      
(

      
      SELECT
      
       E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate 

      
      FROM
      
       Orders O 
      
      JOIN
      
       Employees E 
      
      ON
      
       O.EmployeeID
      
      =
      
      E.EmployeeID

      
      WHERE
      
       E.EmployeeID
      
      =
      
      1
      
      
)

      
      SELECT
      
       
      
      *
      
       
      
      FROM
      
       CTE_NAME
     
     

 

CTE最强大之处在于递归查询,如要仔细研究可以参考微软的文章.