sqlserver表變數

1. sql Server 表變數和臨時表的區別

一、表變數

表變數在SQL Server 2000中首次被引入。表變數的具體定義包括列定義，列名，數據類型和約束。而在表變數中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外鍵約束不能在表變數中使用)。定義表變數的語句是和正常使用Create Table定義表語句的子集。只是表變數通過DECLARE @local_variable語句進行定義。

表變數的特徵：

表變數擁有特定作用域(在當前批處理語句中，但不在任何當前批處理語句調用的存儲過程和函數中)，表變數在批處理結束後自動被清除。

表變數較臨時表產生更少的存儲過程重編譯。

針對表變數的事務僅僅在更新數據時生效，所以鎖和日誌產生的數量會更少。

由於表變數的作用域如此之小，而且不屬於資料庫的持久部分，所以事務回滾不會影響表變數。

表變數可以在其作用域內像正常的表一樣使用。更確切的說，表變數可以被當成正常的表或者表表達式一樣在SELECT,DELETE,UPDATE,INSERT語句中使用，但是表變數不能在類似"SELECT select_listINTOtable_variable"這樣的語句中使用。而在SQL Server2000中，表變數也不能用於INSERTINTOtable_variable EXEC stored_procere這樣的語句中。

表變數不能做如下事情:

雖然表變數是一個變數，但是其不能賦值給另一個變數。

check約束，默認值和計算列不能引用自定義函數。

不能為約束命名。

不能Truncate表變數。

不能向標識列中插入顯式值(也就是說表變數不支持SET IDENTITY_INSERT ON)

下面來玩玩表變數吧。

定義一個表變數，插入一條數據，然後查詢：

DECLARE @tb1 Table
(
Id int,
Name varchar(20),
Age int
)

INSERT INTO @tb1 VALUES(1,'劉備',22)

SELECT * FROM @tb1

輸出結果如下：

SQL Server不支持定義表變數時對Constraint命名，也不支持定義表變數後，對其建Constraint。

更多的不允許，請查看上面的要求。

二、臨時表

在深入臨時表之前，我們要了解一下會話(Session)，一個會話僅僅是一個客戶端到數據引擎的連接。在SQL Server Management Studio中，每一個查詢窗口都會和資料庫引擎建立連接。一個應用程序可以和資料庫建立一個或多個連接，除此之外，應用程序還可能建立連接後一直不釋放知道應用程序結束，也可能使用完釋放連接需要時建立連接。

臨時表和Create Table語句創建的表有著相同的物理工程，但臨時表與正常的表不同之處有：

1、臨時表的名稱不能超過116個字元，這是由於資料庫引擎為了辨別不同會話建立不同的臨時表，所以會自動在臨時表的名字後附加一串。

2、局部臨時表(以"#"開頭命名的)作用域僅僅在當前的連接內，從在存儲過程中建立局部臨時表的角度來看，局部臨時表會在下列情況下被Drop：
a、顯示調用Drop Table語句
b、當局部臨時表在存儲過程內被創建時，存儲過程結束也就意味著局部臨時表被Drop。
c、當前會話結束，在會話內創建的所有局部臨時表都會被Drop。

3、全局臨時表(以"##"開頭命名的)在所有的會話內可見，所以在創建全局臨時表之前首先檢查其是否存在，否則如果已經存在，你將會得到重復創建對象的錯誤。
a、全局臨時表會在創建其的會話結束後被Drop，Drop後其他會話將不能對全局臨時表進行引用。
b、引用是在語句級別進行，如:
1.新建查詢窗口，運行語句：

CREATE TABLE ##temp(RowID int)
INSERT INTO ##temp VALUES(3)

2.再次新建一個查詢窗口，每5秒引用一次全局臨時表

While 1=1
BEGIN
SELECT * FROM ##temp
WAITFOR delay '00:00:05'
END

3.回到第一個窗口，關閉窗口。
4.下一次第二個窗口引用時，將產生錯誤。

4、不能對臨時表進行分區。

5、不能對臨時表加外鍵約束。

6、臨時表內列的數據類型不能定義成沒有在TempDb中沒有定義自定義數據類型(自定義數據類型是資料庫級別的對象，而臨時表屬於TempDb)。由於TempDb在每次SQL Server重啟後會被自動創建，所以你必須使用startup stored procere來為TempDb創建自定義數據類型。你也可以通過修改Model資料庫來達到這一目標。

7、XML列不能定義成XML集合的形式，除非這個集合已經在TempDb中定義。

臨時表既可以通過Create Table語句創建，也可以通過"SELECT <select_list> INTO #table"語句創建。你還可以針對臨時表用"INSERT INTO #table EXEC stored_procere"這樣的語句。
臨時表可以擁有命名的約束和索引。但是，當兩個用戶在同一時間調用同一存儲過程時，將會產生」There is already an object named 『<objectname>』 in the database」這樣的錯誤。所以最好的做法是不用為建立的對象進行命名，而使用系統分配的在TempDb中唯一的。

三、誤區

誤區1.表變數僅僅在內存中。

誤區2.臨時表僅僅存儲在物理介質中。

這兩種觀點都是錯誤的，只有內存足夠，表變數和臨時表都會在內存中創建和處理。他們也同樣可以在任何時間被存入磁碟。

注意表變數的名字是系統分配的，表變數的第一個字元」@」並不是一個字母，所以它並不是一個有效的變數名。系統會在TempDb中為表變數創建一個系統分配的名稱，所以任何在sysobjects或sys.tables查找表變數的方法都會失敗。

正確的方法應該是我前面例子中的方法，我看到很多人使用如下查詢查表變數：

select * from sysobjects where name like'#tempTables%'

上述代碼看上去貌似很好用，但會產生多用戶的問題。你建立兩個連接，在第一個連接中創建臨時表，在第二個窗口中運行上面的語句能看到第一個連接創建的臨時表，如果你在第二個連接中嘗試操作這個臨時表，那麼可能會產生錯誤，因為這個臨時表不屬於你的會話。

誤區3.表變數不能擁有索引。

這個誤區也同樣錯誤。雖然一旦你創建一個表變數之後，就不能對其進行DDL語句了，這包括Create Index語句。然而你可以在表變數定義的時候為其創建索引)比如如下語句。

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

這個語句將會創建一個擁有聚集索引的表變數。由於主鍵有了對應的聚集索引，所以一個系統命名的索引將會被創建在RowID列上。

下面的例子演示你可以在一個表變數的列上創建唯一約束以及如何建立復合索引。

declare @temp TABLE (
RowID int NOT NULL,
ColA int NOT NULL,
ColB char(1)UNIQUE,
PRIMARY KEY CLUSTERED(RowID, ColA))

1) SQL 並不能為表變數建立統計信息，就像其能為臨時表建立統計信息一樣。這意味著對於表變數，執行引擎認為其只有1行，這也意味著針對表變數的執行計劃並不是最優。雖然估計的執行計劃對於表變數和臨時表都為1，但是實際的執行計劃對於臨時表會根據每次存儲過程的重編譯而改變。如果臨時表不存在，在生成執行計劃的時候會產生錯誤。

2) 一旦建立表變數後就無法對其進行DDL語句操作。因此如果需要為表建立索引或者加一列，你需要臨時表。

3) 表變數不能使用select …into語句，而臨時表可以。

4) 在SQL Server 2008中，你可以將表變數作為參數傳入存儲過程。但是臨時表不行。在SQL Server 2000和2005中表變數也不行。

5) 作用域:表變數僅僅在當前的批處理中有效,並且對任何在其中嵌套的存儲過程等不可見。局部臨時表只在當前會話中有效，這也包括嵌套的存儲過程。但對父存儲過程不可見。全局臨時表可以在任何會話中可見，但是會隨著創建其的會話終止而DROP，其它會話這時就不能再引用全局臨時表。

6) 排序規則：表變數使用當前資料庫的排序規則，臨時表使用TempDb的排序規則。如果它們不兼容，你還需要在查詢或者表定義中進行指定。

7) 你如果希望在動態SQL中使用表變數，你必須在動態SQL中定義表變數。而臨時表可以提前定義，在動態SQL中進行引用。

四、如何選擇

微軟推薦使用表變數，如果表中的行數非常小，則使用表變數。很多」網路專家」會告訴你100是一個分界線,因為這是統計信息創建查詢計劃效率高低的開始。但是我還是希望告訴你針對你的特定需求對臨時表和表變數進行測試。很多人在自定義函數中使用表變數，如果你需要在表變數中使用主鍵和唯一索引，你會發現包含數千行的表變數也依然性能卓越。但如果你需要將表變數和其它表進行join,你會發現由於不精準的執行計劃，性能往往會非常差。

為了證明這點，請看本文的附件。附件中代碼創建了表變數和臨時表.並裝入了AdventureWorks資料庫的Sales.SalesOrderDetail表。為了得到足夠的測試數據，我將這個表中的數據插入了10遍。然後以ModifiedDate 列作為條件將臨時表和表變數與原始的Sales.SalesOrderDetail表進行了Join操作，從統計信息來看IO差別顯著。從時間來看錶變數做join花了50多秒，而臨時表僅僅花了8秒。

如果你需要在表建立後對表進行DLL操作，那麼選擇臨時表吧。

臨時表和表變數有很多類似的地方。所以有時候並沒有具體的細則規定如何選擇哪一個。對任何特定的情況，你都需要考慮其各自優缺點並做一些性能測試。下面的表格會讓你比較其優略有了更詳細的參考。

參考：http://www.cnblogs.com/kissdodog/archive/2013/07/03/3169470.html

2. sql server 存儲過程 表變數名無法用在查詢字元串中

要注意一點：用execute執行的動態SQL中是無法直接使用外部所定義的變數的(就好像執行動態SQL內與外是兩個不同的會話。用存儲過程sp_executesql倒是可以傳遞其他標量類型的參數，但是也無法傳遞表變數)。而你要實現的功能是在執行動態SQL之前，已經把數據查詢到一個表變數中了，然後要在動態SQL中再查詢此表變數，目前來說是不能實現的。

可以使用臨時表，或者游標，這兩個可以在動態SQL中保持有效狀態。

使用游標的示例如下:

declare@tbtable(usernamenvarchar(20))
insertinto@tb
select'Andy'
union
select'Jack'
declarecurcursorfor
selecttop10usernamefrom@tb
declare@sqlnvarchar(1000),@usernamenvarchar(20)
set@sql='opencur
fetchnextfromcur
into@v_user_name'
executesp_executesql@sql,N'@v_user_namenvarchar(20)output',@usernameout
closecur
deallocatecur
select@username

而對於你的要在動態SQL中執行select語句，直接使用臨時表會比較方便些。

3. sqlserver存儲過程中 如何把表變數轉換成數值

直接將表名也改為varchar(50) 就行了
要不你就用預執行

if exists(select * from sysobjects where name='proc_select')
drop proc proc_select
go
create proc proc_select
@table varchar(50)
as
declare @sql varchar(8000)
set @sql='select * from '+@table
exec(@sql)
go

exec proc_select 'Types'

4. 在sql Server，臨時表和表變數之間的區別

表變數在SQL Server 2000中首次被引入。表變數的具體定義包括列定義，列名，數據類型和約束。而在表變數中可以使用的約束包括主鍵約束，唯一約束，NULL約束和CHECK約束(外鍵約束不能在表變數中使用)。定義表變數的語句是和正常使用Create Table定義表語句的子集。只是表變數通過DECLARE @local_variable語句進行定義。

表變數的特徵：

• 表變數擁有特定作用域(在當前批處理語句中，但不在任何當前批處理語句調用的存儲過程和函數中)，表變數在批處理結束後自動被清除。

• 表變數較臨時表產生更少的存儲過程重編譯。

• 針對表變數的事務僅僅在更新數據時生效，所以鎖和日誌產生的數量會更少。

• 由於表變數的作用域如此之小，而且不屬於資料庫的持久部分，所以事務回滾不會影響表變數。

表變數可以在其作用域內像正常的表一樣使用。更確切的說，表變數可以被當成正常的表或者表表達式一樣在SELECT,DELETE,UPDATE,INSERT語句中使用，但是表變數不能在類似"SELECT select_listINTOtable_variable"這樣的語句中使用。而在SQL Server2000中，表變數也不能用於INSERTINTOtable_variable EXEC stored_procere這樣的語句中。

表變數不能做如下事情:

• 雖然表變數是一個變數，但是其不能賦值給另一個變數。

• check約束，默認值和計算列不能引用自定義函數。

• 不能為約束命名。

• 不能Truncate表變數。

• 不能向標識列中插入顯式值(也就是說表變數不支持SET IDENTITY_INSERT ON)

5. SQL用一個變數來代表多個表，所有操作都直接對這個表變數進行操作，省去反復修改表名的麻煩，如何編寫

一個表變數只能存一個表的數據，不能多個的

6. sql臨時表表變數的使用方法與什麼時候用最好

臨時表、表變數的比較
1、臨時表
臨時表包括：以#開頭的局部臨時表，以##開頭的全局臨時表。
a、存儲
不管是局部臨時表，還是全局臨時表，都會放存放在tempdb資料庫中。
b、作用域
局部臨時表：對當前連接有效，只在創建它的存儲過度、批處理、動態語句中有效，類似於C語言中局部變數的作用域。
全局臨時表：在所有連接對它都結束引用時，會被刪除，對創建者來說，斷開連接就是結束引用；對非創建者，不再引用就是結束引用。
但最好在用完後，就通過drop table 語句刪除，及時釋放資源。
c、特性
與普通的表一樣，能定義約束，能創建索引，最關鍵的是有數據分布的統計信息，這樣有利於優化器做出正確的執行計劃，但同時它的開銷和普通的表一樣，一般適合數據量較大的情況。
有一個非常方便的select ... into 的用法，這也是一個特點。

2、表變數
a、存儲
表變數存放在tempdb資料庫中。
b、作用域
和普通的變數一樣，在定義表變數的存儲過程、批處理、動態語句、函數結束時，會自動清除。
c、特性
可以有主鍵，但不能直接創建索引，也沒有任何數據的統計信息。表變數適合數據量相對較小的情況。
必須要注意的是，表變數不受事務的約束，

7. 在SQL Server 2008 R2中SSIS怎麼傳表名變數

一、SSIS變數簡介
SSIS(SQL
Server
Integration
Services,SQL
Server整合服務)變數分分為兩種，一種是系統變數，一種用戶定義的變數。系統變數包括有關包、容器、任務或事件處理程序的非常有用的信息。例如，在運行時，MachineName系統變數包含運行包含的計算機的名稱，StartTime變數包開始運行的時間。系統變數是只讀的。在SSIS中，用戶變數是用戶在開發的過程中根據需要申明的變數。用戶變數可以使用在腳本中，在由優先約束、For循環容器、派生列轉換和條件性拆分轉換使用的表達式中，以及在更新屬性值的屬性表達式中。
在各種編程語言中申明的變數一般情況下都會有作用范圍的，SSIS變數也不例外，SSIS變數也是有作用范圍的。根據作用范圍分類，變數分為包變數和組件變數。包變數在包任何一個組件中都可以調用，組件變數只能夠在聲明變數的組件中有效。在變數的窗口中可以看到變數的作用域。
可以看到i變數的作用域是整個Package1包，而myconfig變數作用域是數據流任務組件。
二、SSIS用戶變數的聲明、賦值、使用
1、申明變數
申明變數是非常簡單、如果你要申明包變數，只要單擊控制流選項卡，然後在包開發區域空白處單擊右鍵選擇變數命令，或者新建變數按鈕就新建一個變數，輸入名稱，選擇數據類型，賦初值就完成了。
如果要聲明某數據流任務組件使用的變數，只要雙擊該數據流任務組件，在流控制控制選項卡空白的地方單擊右鍵選擇變數命名，在變數窗口中新建一個變數命令，在變數窗口中新建一個變數，這時變數的作用域就是你選擇的流任務組件。
2、賦值
在實際開發中，除了在變數聲明的時候給變數賦值外，還有兩種方式，一種是通過執行SQL任務組件返回值的方式給變數賦值，一種是通過腳本組件來給變數賦值。
在執行SQL任務組件方法是先設置好組件的資料庫連接屬性，然後輸入從資料庫取數據的SQL語句，設置組件返回的結果集為單行。在結果集界面中單擊「新建」，在結果集那一列輸入你剛才SQL返回列的名稱，在變數名稱列選擇你要賦值的變數
圖中紅色方框中的SQL語句非常簡單，返回單行，結果是1。在圖4中，將返回的result列的一行賦值給用戶變數i。
利用腳本組件賦值變數比較簡單，只需要設置腳本組件的ReadOnlyvariable或者ReadWriteVariable，將變數的名稱設置他們的值（多個變數以逗號分割），它們的區別是前者在腳本組件只能夠讀，或者可以讀寫。然後在腳本組件中通過
Dts.Varables("變數名稱").Value=值
3、變數的使用
變數在ssis中使用的地方很多，筆者介紹兩個典型的應用。
（1）
執行
SQL
任務組件的參數
假定申明了一個日期類型變數StartDate，用戶需要通過從某個表中選擇在StartDate日期之前的數據，這個時候需要將StartDate作為參數傳給執行
SQL
任務組件。在執行
SQL
任務組件輸入SQL的地方輸入如下命令語句：
SELECT
*
FROM
TABLE_a
WHERE
日期欄位
<
?
然後在參數據映射界面新增映射,在變數名稱列選擇用戶變數StartDate,選擇類型為DATE,在參數名稱列輸入給參數取的名稱。這樣就可以將StartDate變數傳給SQL任務組件的SQL語句了。
（2）
在腳本組件中賦值
可以在腳本組件中通過Dts.Variables("i").Value
=
1方式賦值給變數，也可以通過這種方式來使用變數。比如Dts.Variables("other_variable").Value
=
Dts.Variables("i").Value+1,這個語句是可以在腳本組件中執行的,將i變數加1後賦值給另外一個變數。

8. sql server2005 表變數 現在我想定義一個表變數，里邊的欄位結構，想和己有的表一樣，怎麼定義

把已存在的表生成SQL腳本，然後更改腳本里的表名為新表，運行即可新建一個一模一樣的表

9. SQL中update語句添加表中數據時，表名是變數，語句要怎麼寫

使用exec(sql語句)
例：使用變數拼接SQL語句，然後用exec運行
declare @table varchar(200)
declare @sql varchar(200)
set @table = 'aaa'
set @sql='select * from '+@table
exec(@sql)
例中的變數@table
即傳遞表名
sql='select
*
from
'+@table
即
select
*
from
aaa