sql臨時表索引

『壹』 sql如何把查詢出來的多個表創建成一個臨時表

SELECT * INTO #TEMPTABLENAME

FROM

(

SELECT xxxxxx //你的查詢語句

)AS table_source //這個別名是必須的

WHERE xxxxxxxx //你需要的where判斷;

COMMIT或ROLLBACK後可自動刪除該臨時表

1、sql server使用select into會自動生成臨時表，不需要事先創建。

select * into #temp from sysobjects

2、sql要把多個表合並成一個要用到union或union all的關鍵字。

3、union或union all的區別是：union會自動壓縮多個結果集合中的重復結果，而union all則將所有的結果全部顯示出來。

(1)sql臨時表索引擴展閱讀

sql語言特點如下：

1、一體化：SQL集數據定義DDL、數據操縱DML和數據控制DCL於一體，可以完成資料庫中的全部工作。

2、使用方式靈活：它具有兩種使用方式，即可以直接以命令方式交互使用；也可以嵌入使用，嵌入到C、C++、FORTRAN、COBOL、JAVA等主語言中使用。

3、語言簡潔，語法簡單，好學好用：在ANSI標准中，只包含了94個英文單詞，核心功能只用6個動詞，語法接近英語口語。

『貳』 sql server 臨時表怎樣加索引

你說的是臨時表還是表變數？
如果是臨時表的話跟普通表沒有區別
比如
CREATE TABLE #Test(a int,b int)
CREATE INDEX IX_test ON #Test(a)
如果是表變數不支持直接創建索引，但是可疑在聲明表變數的時候設置主鍵
比如
declare @table table(id int IDENTITY PRIMARY KEY,a int,b int);

『叄』 sql如何創建臨時表

網上其實很多相關文章，你可以搜一下，並不一定需要在這里提問。
轉帖一篇給你吧

drop table #Tmp --刪除臨時表#Tmp
create table #Tmp --創建臨時表#Tmp
(
ID int IDENTITY (1,1) not null, --創建列ID,並且每次新增一條記錄就會加1
WokNo varchar(50),
primary key (ID) --定義ID為臨時表#Tmp的主鍵
);
Select * from #Tmp --查詢臨時表的數據
truncate table #Tmp --清空臨時表的所有數據和約束

相關例子：

Declare @Wokno Varchar(500) --用來記錄職工號
Declare @Str NVarchar(4000) --用來存放查詢語句
Declare @Count int --求出總記錄數
Declare @i int
Set @i = 0
Select @Count = Count(Distinct(Wokno)) from #Tmp
While @i < @Count
Begin
Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
Select @WokNo,@i --一行一行把職工號顯示出來
Set @i = @i + 1
End

臨時表
可以創建本地和全局臨時表。本地臨時表僅在當前會話中可見；全局臨時表在所有會話中都可見。
本地臨時表的名稱前面有一個編號符 (#table_name)，而全局臨時表的名稱前面有兩個編號符 (##table_name)。

SQL 語句使用 CREATE TABLE 語句中為 table_name 指定的名稱引用臨時表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

如果本地臨時表由存儲過程創建或由多個用戶同時執行的應用程序創建，則 SQL Server 必須能夠區分由不同用戶創建的表。為此，SQL
Server 在內部為每個本地臨時表的表名追加一個數字後綴。存儲在 tempdb 資料庫的 sysobjects 表中的臨時表，其全名由
CREATE TABLE 語句中指定的表名和系統生成的數字後綴組成。為了允許追加後綴，為本地臨時表指定的表名 table_name 不能超過
116 個字元。

除非使用 DROP TABLE 語句顯式除去臨時表，否則臨時表將在退出其作用域時由系統自動除去：

當存儲過程完成時，將自動除去在存儲過程中創建的本地臨時表。由創建表的存儲過程執行的所有嵌套存儲過程都可以引用此表。但調用創建此表的存儲過程的進程無法引用此表。

所有其它本地臨時表在當前會話結束時自動除去。

全局臨時表在創建此表的會話結束且其它任務停止對其引用時自動除去。任務與表之間的關聯只在單個 Transact-SQL 語句的生存周期內保持。換言之，當創建全局臨時表的會話結束時，最後一條引用此表的 Transact-SQL 語句完成後，將自動除去此表。
在
存儲過程或觸發器中創建的本地臨時表與在調用存儲過程或觸發器之前創建的同名臨時表不同。如果查詢引用臨時表，而同時有兩個同名的臨時表，則不定義針對哪
個表解析該查詢。嵌套存儲過程同樣可以創建與調用它的存儲過程所創建的臨時表同名的臨時表。嵌套存儲過程中對表名的所有引用都被解釋為是針對該嵌套過程所
創建的表，例如：

CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO

下面是結果集：

(1 row(s) affected)

Test1Col
-----------
1

(1 row(s) affected)

Test2Col
-----------
2

當創建本地或全局臨時表時，CREATE TABLE 語法支持除 FOREIGN KEY 約束以外的其它所有約束定義。如果在臨時表中指定
FOREIGN KEY 約束，該語句將返回警告信息，指出此約束已被忽略，表仍會創建，但不具有 FOREIGN KEY 約束。在 FOREIGN
KEY 約束中不能引用臨時表。

考慮使用表變數而不使用臨時表。當需要在臨時表上顯式地創建索引時，或多個存儲過程或函數需要使用表值時，臨時表很有用。通常，表變數提供更有效的查詢處理。

『肆』 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

『伍』 sql 如何用臨時表優化性能

InnoDB 類型的臨時表存在的潛在問題

盡管使用 InnoDB 是性能最佳的，但可能會出現新的潛在問題。在某些特定情況下，您可能會出現磁碟耗盡和伺服器中斷。

與資料庫中的任何其他 InnoDB 表一樣，臨時表具有自己的表空間文件。新文件與通用表空間一起位於數據目錄中，名稱為 ibtmp1。它存儲所有 tmp 表。不運行手動運行 OPTIMIZE TABLE，表空間文件就會不斷增長。如果你不能使用 OPTIMIZE，那麼唯一能將 ibtmp1 大小縮小為零的方法，就是重新啟動伺服器。幸運的是，即使文件無法減小，在執行查詢後，臨時表也會自動刪除，表空間可回收使用。現在，我們想一想以下情境：

• 存在未優化的查詢，需要在磁碟上創建非常大的的臨時表

• 存在優化的查詢，但他們正在磁碟上創建非常大的臨時表，因為你正在對此數據集進行計算（統計，分析）

• 高並發連接時，運行相同的查詢，伴隨臨時表的創建

• 沒有很多可用空間

• 在這些情況下，文件 ibtmp1 大大增加，很容易耗盡可用空間。這種情況每天發生幾次，並且必須重啟伺服器才能完全縮小 ibtmp1 表空間。使用不可收縮的文件可以輕松耗盡磁碟空間！

那麼，如何避免磁碟耗盡和中斷呢？




簡單的解決方案：使用更大的磁碟

• 雖然可以暫時解決問題，但這不是最佳解決方案。實際上，您可以通過逐步增加磁碟大小，來猜測具體需要的空間。如果環境位於雲中，或者在非常大的虛擬平台，這很容易實現。但是使用這種解決方案，您可能會面臨不必要的開支。您還可以通過設置以下配置變數將 ibtmp1 文件移動到專用大型磁碟上： [mysqld] innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend



需要重啟 MySQL 。注意，必須將路徑指定為相對於數據目錄。

設置 ibtmp1 大小的上限

• 例如： [mysqld] innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G



在這種情況下，文件不能超過 10GB。可以降低宕機概率，但也是一個危險的解決方案。當數據文件達到最大值時，會查詢失敗並顯示一個錯誤，提示表已滿。



• 退回 MyISAM 將臨時表存儲在磁碟上

這個解決方案似乎違反直覺，但它可能是快速避免中斷的最佳方法，並保證使用所有需要的臨時表。

• internal_tmp_disk_storage_engine = MYISAM


• 由於變數是動態的，您也可以在運行時設置它： SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;


• 回到 MyISAM，您將大大降低寫滿磁碟空間的可能性。實際上，臨時表將創建到不同的文件中，並在查詢結束時立即刪除。

雖然總是有可能看到相同的問題，以防你可以在同一時間運行查詢或非常接近。在我的實際案例中，這是避免所有中斷的解決方案。

優化你的查詢

• 在將存儲引擎退回到 MyISAM 以減輕中斷發生後，必須花時間分析查詢。目標是減小磁碟上臨時表的大小。本文的目的不是解釋如何調查查詢，而是可以依賴慢速日誌，像 pt-query-digest 和 EXPLAIN 這樣的工具。一些技巧：

• 在表上創建缺少的索引

• 如果不需要，可以在查詢中添加更多過濾條件以更少收集的數據

• 重寫查詢以優化執行計劃

• 可以在應用程序中使用隊列管理器來序列化它們的執行或減少並發性

但希望在所有優化之後，您可以返回將臨時存儲引擎設置為 InnoDB 以獲得更好的性能。

結論

有時這些改進會產生意想不到的副作用。用於磁碟上臨時表的 InnoDB 存儲引擎是一個很好的改進，但在某些特定情況下，例如，如果您有未優化查詢和很少的可用空間，則可能因「磁碟已滿」錯誤而中斷。將 tmp 存儲引擎退回到 MyISAM 是避免中斷的最快方法，但是為了返回到 InnoDB，查詢的優化是更重要的事情。更大或專用的磁碟也可能有所幫助。但這是一個微不足道的建議。

『陸』 SQL 臨時表創建索引會提高效率嗎

只要你索引設置合理，當然會提升效率，其實臨時表你也可以理解為存儲在TempDB資料庫中的物理表。
我在做復雜業務流程處理時，經常用到臨時表，這樣就能避免對原始大數據表的頻繁檢索，明顯對速度有提升

『柒』 SQL Server中怎樣在臨時表中創建索引

你說的是臨時表還是表變數？
如果是臨時表的話跟普通表沒有區別：
比如
CREATE TABLE #Test(a int,b int)
CREATE INDEX IX_test ON #Test(a)
如果是表變數不支持直接創建索引，但是可疑在聲明表變數的時候設置主鍵。
比如
declare @table table(id int IDENTITY PRIMARY KEY,a int,b int);

『捌』 SQL Server 表變數和臨時表的區別

臨時表與表變數都可以起到「臨時」的作用，那麼兩者主要的區別是什麼呢？
這里不討論創建方式，以及全局臨時表、會話臨時表這些，主要記錄一下個人對兩者的主要區別以及適用情況的看法，有什麼不對或補充的地方，歡迎討論。
區別：
1. 表變數存儲在內存中，當創建表變數時，sql server不會產生日誌，也不會維護統計信息；表變數的欄位不能建立索引；不能有約束和默認值。sql server 認為表變數通常只有很少量的數據。
2. 當創建臨時表時，sql server會產生日誌，統計信息；臨時表的欄位可以建立索引，所以它可以存儲相對較多的數據；可以有約束和默認值，並且有鎖機制。可以說臨時表與實際的表幾乎是一樣的，只不過它是輕量和臨時的，可以在想要的時候創建和銷毀。
適用情況：
表變數： 如果是很少量數據，那麼就用表變數。使用表變數的開銷要小於臨時表。實際項目用到的地方，例如批量刪除，需要傳遞多個id 作為參數，有的人會拼接id字元串，然後在資料庫去解析；我的做法通常是傳遞xml，然後用xml生成表變數，然後鏈接表刪除。
臨時表：有多張表鏈接查詢得到一個小結果集，該結果集需要在本次會話或多個會話里多次使用。

『玖』 mysql臨時表會用到索引嗎

、UNION查詢；
2、用到TEMPTABLE演算法或者是UNION查詢中的視圖；
3、ORDER BY和GROUP BY的子句不一樣時；
4、表連接中，ORDER BY的列不是驅動表中的；
5、DISTINCT查詢並且加上ORDER BY時；
6、SQL中用到SQL_SMALL_RESULT選項時；
7、FROM中的子查詢；
8、子查詢或者semi-join時創建的表；