資料庫中暫時表,表變量和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

NAME VARCHAR(20),

SELECT * FROM ##tmpTable2 JOIN ...

DROP TABLE ##tmpTable2

2.表變量:在記憶體中存儲,比暫時表運作速度快. 在SP或者function越過有效scope之後會自己主動釋放,不用顯式的寫drop.表變量僅僅可用在DML的操作中,會有比較多的限制.

--直接聲明表變量

DECLARE @varTable TABLE

--先建立表類型

CREATE TYPE [dbo].[T_TEMP] AS TABLE(

--在聲明表變量

DECLARE @varTable T_TEMP

WITH CTE_NAME

AS

SELECT * FROM CTE_NAME