SQL Server 表變量和暫時表的差別(具體彌補篇)。本站提示廣大學習愛好者：（SQL Server 表變量和暫時表的差別(具體彌補篇)）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server 表變量和暫時表的差別(具體彌補篇)正文
1、表變量
表變量在SQL Server 2000中初次被引入。表變量的詳細界說包含列界說，列名，數據類型和束縛。而在表變量中可使用的束縛包含主鍵束縛，獨一束縛，NULL束縛和CHECK束縛(外鍵束縛不克不及在表變量中應用)。界說表變量的語句是和正常應用Create Table界說表語句的子集。只是表變量經由過程DECLARE @local_variable語句停止界說。
表變量的特點：
1.表變量具有特定感化域(在以後批處置語句中，但不在任何以後批處置語句挪用的存儲進程和函數中)，表變量在批處置停止後主動被消除。
2.表變量較暫時表發生更少的存儲進程重編譯。
3.針對表變量的事務僅僅在更新數據時失效，所以鎖和日記發生的數目會更少。
4.因為表變量的感化域如斯之小，並且不屬於數據庫的耐久部門，所以事務回滾不會影響表變量。
表變量可以在其感化域內像正常的表一樣應用。更確實的說，表變量可以被當做正常的表或許表表達式一樣在SELECT,DELETE,UPDATE,INSERT語句中應用，然則表變量不克不及在相似"SELECT select_list INTO table_variable"如許的語句中應用。而在SQL Server2000中，表變量也不克不及用於INSERT INTO table_variable EXEC stored_procedure如許的語句中。
表變量不克不及做以下工作:
1.固然表變量是一個變量，然則其不克不及賦值給另外一個變量。
2.check束縛，默許值和盤算列不克不及援用自界說函數。
3.不克不及為束縛定名。
4.不克不及Truncate表變量。
5.不克不及向標識列中拔出顯式值(也就是說表變量不支撐SET IDENTITY_INSERT ON)
上面來玩玩表變量吧。
界說一個表變量，拔出一條數據，然後查詢：
DECLARE @tb1 Table ( Id int, Name varchar(20), Age int ) INSERT INTO @tb1 VALUES(1,'劉備',22) SELECT * FROM @tb1
輸入成果以下：
再來嘗嘗一些不相符請求的情形，例如添加表變量後，添加束縛，並對束縛定名：
再來嘗嘗一些不相符請求的情形，例如添加表變量後，添加束縛，並對束縛定名：
ALTER TABLE @tb1 ADD CONSTRAINT CN_AccountAge CHECK (Account_Age > 18); -- 拔出年紀必需年夜於18
SQL Server提醒毛病以下：
SQL Server不支撐界說表變量時對Constraint定名，也不支撐界說表變量後，對其建Constraint。
更多的不許可，請檢查下面的請求。
2、暫時表
在深刻暫時表之前，我們要懂得一下會話(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 procedure來為TempDb創立自界說數據類型。你也能夠經由過程修正Model數據庫來到達這一目的。
7、XML列不克不及界說成XML聚集的情勢，除非這個聚集曾經在TempDb中界說。
暫時表既可以經由過程Create Table語句創立，也能夠經由過程"SELECT <select_list> INTO #table"語句創立。你還可以針對暫時表用"INSERT INTO #table EXEC stored_procedure"如許的語句。
暫時表可以具有定名的束縛和索引。然則，當兩個用戶在統一時光挪用統一存儲進程時，將會發生”There is already an object named ‘<objectname>' in the database”如許的毛病。所以最好的做法是不消為樹立的對象停止定名，而應用體系分派的在TempDb中獨一的。
3、誤區
誤區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中停止援用。
4、若何選擇
微軟推舉應用表變量，假如表中的行數異常小，則應用表變量。許多”收集專家”會告知你100是一個分界限,由於這是統計信息創立查詢籌劃效力高下的開端。然則我照樣願望告知你針對你的特定需求對暫時表和表變量停止測試。許多人在自界說函數中應用表變量，假如你須要在表變量中應用主鍵和獨一索引，你會發明包括數千行的表變量也仍然機能卓著。但假如你須要將表變量和其它表停止join,你會發明因為不精准的履行籌劃，機能常常會異常差。
為了證實這點，請看本文的附件。附件中代碼創立了表變量和暫時表.並裝入了AdventureWorks數據庫的Sales.SalesOrderDetail表。為了獲得足夠的測試數據，我將這個表中的數據拔出了10遍。然後以ModifiedDate 列作為前提將暫時表和表變量與原始的Sales.SalesOrderDetail表停止了Join操作，從統計信息來看IO差異明顯。從時光來看表變量做join花了50多秒，而暫時表僅僅花了8秒。
假如你須要在表樹立後對表停止DLL操作，那末選擇暫時表吧。
暫時表和表變量有許多相似的處所。所以有時刻並沒有詳細的細則劃定若何選擇哪個。對任何特定的情形，你都須要斟酌其各自優缺陷並做一些機能測試。上面的表格會讓你比擬其優略有了更具體的參考。
特征 表變量 暫時表 感化域 以後批處置 以後會話，嵌套存儲進程，全局：一切會話 應用場景 自界說函數，存儲進程，批處置 自界說函數，存儲進程，批處置 創立方法 DECLARE statement only.只能經由過程DECLEARE語句創立
CREATE TABLE 語句
SELECT INTO 語句.
表名長度 最多128字節 最多116字節 列類型可使用自界說數據類型
可使用XML聚集
自界說數據類型和XML聚集必需在TempDb內界說 Collation 字符串排序規矩繼續自以後數據庫 字符串排序規矩繼續自TempDb數據庫 索引 索引必需在表界說時樹立 索引可以在表創立後樹立 束縛 PRIMARY KEY, UNIQUE, NULL, CHECK束縛可使用，但必需在表樹立時聲明 PRIMARY KEY, UNIQUE, NULL, CHECK. 束縛可使用，可以在任什麼時候後添加，但不克不及有外鍵束縛 表樹立後應用DDL (索引，列) 不許可 許可. 數據拔出方法 INSERT 語句 (SQL 2000: 不克不及應用INSERT/EXEC).INSERT 語句, 包含 INSERT/EXEC.
SELECT INTO 語句.
Insert explicit values into identity columns (SET IDENTITY_INSERT). 不支撐SET IDENTITY_INSERT語句 支撐SET IDENTITY_INSERT語句 Truncate table 不許可 許可 析構方法 批處置停止後主動析構 顯式挪用 DROP TABLE 語句.
