SQL Server暫存資料表、表標量和CTE

標籤：begin   exec   varchar   res   變數類型   程式   兩種   範圍   會話   

在SQL Server中暫存資料表、表變數和CTE通常用來儲存暫存資料表資料，這裡簡單介紹下它們間的不同和不同的應用情境。

CTE

CTE通常叫做“通用運算式”，在記憶體中建立。
用途：通常用來替換需要遞迴的子查詢。
有效範圍：只能在包含他CTE的語句中可使用。
舉例：有些複雜的查詢語句中，子查詢語句多次出現，這樣代碼顯得冗長，且執行效率也不高：

Select D.* From DInner Join (        Select id value, date From A        Inner Join B on A.data < B.date        Inner Join C on C.data > B.date    ) CTE a c1 on c1.id = D.id+1Inner Join (    Select id value, date From A    Inner Join B on A.data < B.date    Inner Join C on C.data > B.date) as c2 on c2.id = D.id-1

使用CTE替換子查詢,將複雜的子查詢結果儲存，減少查詢次數

with CTE as (    Select id value, date From A    Inner Join B on A.data < B.date    Inner Join C on C.data > B.date)Select D.* From DInner Join CTE as c1 on c1.id = D.id+1Inner Join CTE as c2 on c2.id = D.id-1

暫存資料表

在sql server中，暫存資料表是在運行時建立的，您可以執行在“普通表”上可以執行的所有操作。這些表是在temdb資料庫中建立的。根據範圍和行為，暫存資料表分為兩種類型，如下所示

1. 本地暫存資料表
   本地暫存資料表只對建立表的SQL伺服器會話（串連）可用，在串連關閉時自動刪除。聲明時以‘#’為首碼。
   注意這裡的會話，資料庫指：同一個開啟的查詢時段；調用它的asp.net程式：一次sqlconnection串連（並不是指asp.net中的會話）。
   建立本地暫存資料表並插入資料

create Table #LocalTemp(    UserID int,    Name varchar(50),    Address varchar(150))goinsert into #LocalTemp values(1,‘Shailendra‘,‘Noida‘)

在當前視窗中指向查詢
select * from #LocalTemp;

1. 全域暫存資料表
   全域暫存資料表可供所有SQL伺服器會話或串連(即所有使用者)使用。這些表可以由任何SQL伺服器串連使用者建立，當建立該暫存資料表的串連關閉時會自動刪除這些表。聲明時以‘##’為首碼。
   建立全域暫存資料表並插入資料

create Table ##GlobalTemp(    UserID int,    Name varchar(50),    Address varchar(150))goinsert into ##GlobalTemp values(1,‘Shailendra‘,‘Noida‘)

另外開啟一個新的視窗（或新換一使用者串連）同樣可正常查詢
select * from ##GlobalTemp;

表變數

這就像一個變數，並存在於特定的一批查詢執行中。一旦它從批中出來，它就會被刪除。這也是在temdb資料庫中建立的，而不是記憶體。這也允許您在表變數聲明時建立主鍵、標識，而不是非叢集索引。
這裡示範下它就像一個變數，可以當做參數傳遞到預存程序中
聲明一個表變數類型

CREATE TYPE table_type_list AS TABLE (    name varchar(50))GO

建立接收表變數的預存程序

Create Proc test(    @id int,    @list table_type_list READONLY)asbegin    set nocount on    select * from @listend

聲明表變數，並向表變數中插入資料，執行預存程序

Declare @t table_type_listInsert into @t(name) values(‘a‘), (‘b‘), (‘c‘)Exec test 1, @t

SQL Server暫存資料表、表標量和CTE