本文導(dǎo)讀：定義一個(gè)sql server表變量的方法和定義一個(gè)用戶自定義表的方法是一樣的；然而，定義了一個(gè)表變量之后，你就不能再改變?cè)摫淼亩x了。而且你必須在定義這個(gè)表變量的時(shí)候給出它所有屬性的定義.

定義一個(gè)sql server表格變量的方法和定義一個(gè)用戶自定義表格的方法是一樣的；例如

1.

DECLARE @T_ChartsIdeaItem1 Table(XData char(20) null,YData char(20) null)

INSERT INTO @T_ChartsIdeaItem1(XData,YData)VALUES ('BU9876', 'Creating')

select * from @T_ChartsIdeaItem1

2.

DECLARE@News table

(

News_id int NOT NULL,

NewsTitle varchar(100)，

NewsContent varchar(2000)，

NewsDateTime datetime

)

INSERT INTO @News (News_id, NewsTitle, NewsContent,NewsDateTime)

VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())

SELECT News_id, NewsTitle, NewsContent,NewsDateTime FROM @News

不是所有的INSERT命令都可以對(duì)表格變量執(zhí)行操作。比如，當(dāng)你向一個(gè)表格變量插入變量值時(shí)，你就不能用EXEC命令。通過(guò)執(zhí)行一個(gè)已保存的程序或是一系列命令來(lái)為一個(gè)表格變量插入變量值將會(huì)返回錯(cuò)誤的結(jié)果。而且，也不是所有的SELECT命令都可以對(duì)表格變量執(zhí)行操作，你不能用SELECT INTO命令來(lái)為一個(gè)表格變量插入數(shù)據(jù)。

對(duì)于sql server表格變量，你不能使用某些系統(tǒng)程序，比如sp_depends。運(yùn)行時(shí)產(chǎn)生的并被隨機(jī)命名的表格變量的對(duì)象名被存儲(chǔ)在tempdb數(shù)據(jù)庫(kù)中。由于大多數(shù)系統(tǒng)功能和系統(tǒng)程序需要對(duì)象名和標(biāo)識(shí)代碼，所以對(duì)一個(gè)表格變量執(zhí)行系統(tǒng)功能和系統(tǒng)程序就是一件很困難的事。為了對(duì)一個(gè)表格變量執(zhí)行系統(tǒng)功能和系統(tǒng)程序，我們需要先進(jìn)行一些復(fù)雜的調(diào)整以獲取該表格變量的對(duì)象名和/或標(biāo)識(shí)代碼。

一個(gè)表格變量的有效范圍就是整個(gè)批處理，程序，或函數(shù)的處理過(guò)程。當(dāng)在一段程序中發(fā)出GO命令時(shí)，表格變量就不再起作用了。

1. 為什么要使用表變量

表變量是從2000開始引入的，微軟認(rèn)為與本地臨時(shí)表相比，表變量具有如下優(yōu)點(diǎn)：

 a.與其他變量的定義一樣，表變量具有良好的定義范圍，并會(huì)被自動(dòng)清除；

 b.在存儲(chǔ)過(guò)程中使用表變量會(huì)減少存儲(chǔ)過(guò)程重新編譯的發(fā)生；

 c.表變量需要更少的鎖請(qǐng)求和日志資源；

 d.可以在表變量上使用UDF,UDDT,XML。

 

2、表變量的限制

與臨時(shí)表相比，表變量存在著如下缺點(diǎn)：

 a.在表變量上沒有統(tǒng)計(jì)信息，查詢優(yōu)化器根據(jù)固定的預(yù)估值來(lái)選擇執(zhí)行計(jì)劃，在數(shù)據(jù)很多的情況下，會(huì)導(dǎo)致查詢優(yōu)化器選擇很差的執(zhí)行計(jì)劃；

 b.不能直接在表變量上創(chuàng)建索引，但可以通過(guò)創(chuàng)建約束（主鍵、唯一）來(lái)建立索引；

 c.在DECLARE后，不能再對(duì)表變量進(jìn)行更改；

 d.不能對(duì)表變量執(zhí)行INSERT EXEC,SELECT INTO語(yǔ)句；

 e.不能通過(guò)EXEC或sp_executesql來(lái)執(zhí)行牽涉到表變量的動(dòng)態(tài)SQL語(yǔ)句，但如果表變量是在動(dòng)態(tài)SQL語(yǔ)句內(nèi)定義的，則可以。

 

3、那什么時(shí)候可以使用表變量

要使用表變量應(yīng)該根據(jù)如下規(guī)則來(lái)判斷：

 a.表的行數(shù)；

 b.使用表變量能夠減少的重新編譯次數(shù)；

 c.查詢的類型和對(duì)索引或者統(tǒng)計(jì)信息的依賴程度；

 d.需要生用UDF,UDDT,XML的時(shí)候。

其實(shí)也就說(shuō)，得從實(shí)際出發(fā)，根據(jù)具體的查詢，作出具體的選擇。但是，其中很關(guān)鍵的一點(diǎn)，如果表的行數(shù)非常多，使用表變量其實(shí)是更費(fèi)資源的。有人提出了這樣的建議：對(duì)于行數(shù)較少的情況下(小于1000行）可以使用表變量；如果行數(shù)很多（有幾萬(wàn)行），則使用臨時(shí)表。

 

4、使用表變量的誤區(qū)

對(duì)于表變量，很多人認(rèn)為，表變量和其他變量一樣，只存在內(nèi)存中，其實(shí)這是不正確的，表變量也存在tempdb中。

代碼示例:

CREATETABLE dbo.#News

(

News_id int NOT NULL,

NewsTitle varchar(100)，

NewsContent varchar(2000)，

NewsDateTime datetime

)

INSERT INTO dbo.#News (News_id, NewsTitle,NewsContent, NewsDateTime)

VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())

SELECT News_id, NewsTitle, NewsContent,NewsDateTime FROM dbo.#News

DROP TABLE dbo.[#News]

比較臨時(shí)表及表變量都可以通過(guò)SQL的選擇、插入、更新及刪除語(yǔ)句，它們的的不同主要體現(xiàn)在以下這些：

1)當(dāng)用戶在訪問(wèn)表變量時(shí)，SQLServer是不產(chǎn)生日志的，而在臨時(shí)表中是產(chǎn)生日志的;

2)在表變量中，是不允許有非聚集索引的;

3)表變量是不允許有DEFAULT默認(rèn)值，也不允許有約束;

4)臨時(shí)表上的統(tǒng)計(jì)信息是健全而可靠的，但是表變量上的統(tǒng)計(jì)信息是不可靠的;

5)臨時(shí)表中是有鎖的機(jī)制，而表變量中就沒有鎖的機(jī)制。