sql臨時表和表變數

㈠ 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);

㈡ CTE在遞歸查詢中的優勢：如何比SQL2000的臨時表方法更簡潔

CTE，即 公用表表達式（Common Table Expression）</，它是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句執行范圍內定義的一種臨時結果集。與派生表相似，它不作為存儲對象持久存在，僅在查詢期間有效。但CTE的獨特之處在於，它可以自引用並在同一個查詢中多次使用，這一點區別於派生表。

CTE在SQL查詢中有多種應用，其中 遞歸查詢</ 是其亮點，它能夠處理層次結構的數據，如組織架構或樹狀結構。例如，創建一個遞歸查詢來查找所有管理費用及其下屬節點的信息，以前可能需要復雜的臨時表或堆棧操作，但在SQL2005中，通過CTE的遞歸功能，只需兩步即可實現。

CTE的優點在於提高查詢的 可讀性和維護性</。它將復雜的查詢邏輯分解成易於理解的部分，每個部分可以生成臨時的 CTE，然後逐步組合成最終結果。在SQL2005中，如上所示的CTE示例，通過 WITH</ 關鍵字定義了一個遞歸查詢，其結構包括表達式名稱、列列表和查詢定義，可以輕松地替代臨時表或表變數的功能。

使用CTE時，可以在用戶定義的常式，如函數、存儲過程、觸發器或視圖中定義。下面是一個簡單的CTE例子，它從test表中找出salary最大的id記錄，然後在後續的查詢中引用這個臨時結果集。

with test_CTE(id,salary) as ( select id,max(salary) from test group by id ) select * from test_cte

總結來說，CTE通過其靈活的定義和引用機制，簡化了復雜查詢的編寫，提高了代碼的可讀性和效率，特別是在處理層次結構數據時，遞歸查詢功能尤其強大。

CTE是CustomerTerminalEquipment、Coefficient of thermal expansion和children of the earth 等的縮寫形式，CustomerTerminalEquipment主要是指用戶終端設備，，即計算機顯示終端，是計算機系統的輸入、輸出設備。計算機顯示終端伴隨主機時代的集中處理模式而產生，並隨著計算技術的發展而不斷發展。

㈢ 在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)

㈣ sql清空臨時表

你這不是臨時表
create table #tempTable (id int identity(1,1),name varchar(20))

insert into #temptable(name)values('1')
insert into #temptable(name)values('1')
insert into #temptable(name)values('1')
select * from #temptable--看這里id是從1開始的

delete #temptable
DBCC CHECKIDENT (#temptable, RESEED, 0)--關鍵

insert into #temptable(name)values('2')
insert into #temptable(name)values('3')
insert into #temptable(name)values('3')
select * from #temptable--id重新從1開始

㈤ Oracle涓存椂琛ㄥ拰SQL Server涓存椂琛ㄧ殑涓嶅悓鐐瑰規瘮

Oracle鏁版嵁搴撳壋寤轟復鏃惰〃鐨勮繃紼嬩互鍙婂拰SQL Server涓存椂琛ㄧ殑涓嶅悓鐐圭殑瀵規瘮鐨勭浉鍏崇煡璇嗘槸鏈鏂囨垜浠涓昏佽佷粙緇嶇殑鍐呭癸紝鎺ヤ笅鏉ュ氨璁╂垜浠涓璧鋒潵浜嗚В涓涓嬭繖閮ㄥ垎鍐呭瑰惂錛屽笇鏈涜兘澶熷規偍鏈夋墍甯鍔┿
1.綆浠
Oracle鏁版嵁搴撻櫎浜嗗彲浠ヤ繚瀛樻案涔呰〃澶栵紝榪樺彲浠ュ緩絝嬩復鏃惰〃temporary tables銆傝繖浜涗復鏃惰〃鐢ㄦ潵淇濆瓨涓涓浼氳瘽SESSION鐨勬暟鎹錛屾垨鑰呬繚瀛樺湪涓涓浜嬪姟涓闇瑕佺殑鏁版嵁銆傚綋浼氳瘽閫鍑烘垨鑰呯敤鎴鋒彁浜commit鍜屽洖婊歳ollback浜嬪姟鐨勬椂鍊欙紝涓存椂琛ㄧ殑鏁版嵁鑷鍔ㄦ竻絀猴紝浣嗘槸涓存椂琛ㄧ殑緇撴瀯浠ュ強鍏冩暟鎹榪樺瓨鍌ㄥ湪鐢ㄦ埛鐨勬暟鎹瀛楀吀涓銆
2.璇︾粏浠嬬粛
Oracle涓存椂琛ㄥ垎涓轟細璇濈駭涓存椂琛ㄥ拰浜嬪姟綰т復鏃惰〃銆
浼氳瘽綰т復鏃惰〃鏄鎸囦復鏃惰〃涓鐨勬暟鎹鍙鍦ㄤ細璇濈敓鍛藉懆鏈熶箣涓瀛樺湪錛屽綋鐢ㄦ埛閫鍑轟細璇濈粨鏉熺殑鏃跺欙紝Oracle鑷鍔ㄦ竻闄や復鏃惰〃涓鏁版嵁銆
浜嬪姟綰т復鏃惰〃鏄鎸囦復鏃惰〃涓鐨勬暟鎹鍙鍦ㄤ簨鍔＄敓鍛藉懆鏈熶腑瀛樺湪銆傚綋涓涓浜嬪姟緇撴潫錛坈ommit or rollback錛夛紝Oracle鑷鍔ㄦ竻闄や復鏃惰〃涓鏁版嵁銆
涓存椂琛ㄤ腑鐨勬暟鎹鍙瀵瑰綋鍓峉ession鏈夋晥錛屾瘡涓猄ession閮芥湁鑷宸辯殑涓存椂鏁版嵁錛屽苟涓斾笉鑳借塊棶鍏跺畠Session鐨勪復鏃惰〃涓鐨勬暟鎹銆傚洜姝わ紝涓存椂琛ㄤ笉闇瑕丏ML閿併
褰撲竴涓浼氳瘽緇撴潫(鐢ㄦ埛姝ｅ父閫鍑 鐢ㄦ埛涓嶆ｅ父閫鍑 ORACLE瀹炰緥宕╂簝)鎴栬呬竴涓浜嬪姟緇撴潫鐨勬椂鍊欙紝Oracle瀵硅繖涓浼氳瘽鐨勮〃鎵ц TRUNCATE 璇鍙ユ竻絀轟復鏃惰〃鏁版嵁.浣嗕笉浼氭竻絀哄叾瀹冧細璇濅復鏃惰〃涓鐨勬暟鎹.
浣犲彲浠ョ儲寮曚復鏃惰〃鍜屽湪涓存椂琛ㄥ熀紜涓婂緩絝嬭嗗浘.鍚屾牱,寤虹珛鍦ㄤ復鏃惰〃涓婄殑緔㈠紩涔熸槸涓存椂鐨,涔熸槸鍙瀵瑰綋鍓嶄細璇濇垨鑰呬簨鍔℃湁鏁. 涓存椂琛ㄥ彲浠ユ嫢鏈夎Е鍙戝櫒.
3.寤虹珛涓存椂琛
涓存椂琛ㄧ殑瀹氫箟瀵規墍鏈変細璇漇ESSION閮芥槸鍙瑙佺殑,浣嗘槸琛ㄤ腑鐨勬暟鎹鍙瀵瑰綋鍓嶇殑浼氳瘽鎴栬呬簨鍔℃湁鏁.
寤虹珛鏂規硶錛
1) ON COMMIT DELETE ROWS 瀹氫箟浜嗗緩絝嬩簨鍔＄駭涓存椂琛ㄧ殑鏂規硶. CREATE GLOBAL TEMPORARY TABLE admin_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT DELETE ROWS; EXAMPLE: SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2 (startdate DATE, 3 enddate DATE, 4 class CHAR(20)) 5 ON COMMIT DELETE ROWS; SQL> create table permernate( a number); SQL> insert into admin_work_area values(sysdate,sysdate,'temperary table'); SQL> insert into permernate values(1); SQL> commit; SQL> select * from admin_work_area; SQL> select * from permernate; A 1 2)ON COMMIT PRESERVE ROWS 瀹氫箟浜嗗壋寤轟細璇濈駭涓存椂琛ㄧ殑鏂規硶. CREATE GLOBAL TEMPORARY TABLE admin_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT PRESERVE ROWS; EXAMPLE:

浼氳瘽1錛
SQL> drop table admin_work_area; SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2 (startdate DATE, 3 enddate DATE, 4 class CHAR(20)) 5 ON COMMIT PRESERVE ROWS; SQL> insert into permernate values(2); SQL> insert into admin_work_area values(sysdate,sysdate,'session temperary'); SQL> commit; SQL> select * from permernate; A ---------- 1 2 SQL> select * from admin_work_area; STARTDATE ENDDATE CLASS ---------- ---------- -------------------- 17-1?? -03 17-1?? -03 session temperary

浼氳瘽2錛
SQL> select * from permernate; A ---------- 1 2 SQL> select * from admin_work_area; 鏈閫夋嫨琛.

浼氳瘽2鐪嬩笉瑙佷細璇1涓涓存椂琛ㄧ殑鏁版嵁銆
4.Oracle涓存椂琛ㄥ拰SQL Server涓存椂琛ㄥ紓鍚
SQL Server涔熷彲浠ュ壋寤轟復鏃惰〃銆備復鏃惰〃涓庢案涔呰〃鐩鎬技錛屼絾涓存椂琛ㄥ瓨鍌ㄥ湪tempdb涓錛屽綋涓嶅啀浣跨敤鏃朵細鑷鍔ㄥ垹闄ゃ
鏈夋湰鍦板拰鍏ㄥ矓涓ょ嶇被鍨嬬殑涓存椂琛錛屼簩鑰呭湪鍚嶇О銆佸彲瑙佹у拰鍙鐢ㄦт笂鍧囦笉鐩稿悓銆傛湰鍦頒復鏃惰〃鐨勫悕縐頒互鍗曚釜鏁板瓧絎﹀彿 (#) 鎵撳ご錛涘畠浠浠呭瑰綋鍓嶇殑鐢ㄦ埛榪炴帴鏄鍙瑙佺殑錛涘綋鐢ㄦ埛浠 Microsoft SQL Server瀹炰緥鏂寮榪炴帴鏃惰鍒犻櫎銆傚叏灞涓存椂琛ㄧ殑鍚嶇О浠ユ暟瀛︾﹀彿 (##) 鎵撳ご錛屽壋寤哄悗瀵逛換浣曠敤鎴烽兘鏄鍙瑙佺殑錛屽綋鎵鏈夊紩鐢ㄨヨ〃鐨勭敤鎴蜂粠 SQL Server 鏂寮榪炴帴鏃惰鍒犻櫎銆
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 璇鍙ョ殑鐢熷瓨鍛ㄦ湡鍐呬繚鎸併傛崲璦涔嬶紝褰撳壋寤哄叏灞涓存椂琛ㄧ殑浼氳瘽緇撴潫鏃訛紝鏈鍚庝竴鏉″紩鐢ㄦよ〃鐨凾ransact-SQL璇鍙ュ畬鎴愬悗錛屽皢鑷鍔ㄩ櫎鍘繪よ〃銆
渚嬪傦紝濡傛灉鍒涘緩鍚嶄負 employees 鐨勮〃錛屽垯浠諱綍浜哄彧瑕佸湪鏁版嵁搴撲腑鏈変嬌鐢ㄨヨ〃鐨勫畨鍏ㄦ潈闄愬氨鍙浠ヤ嬌鐢ㄨヨ〃錛岄櫎闈炲畠宸插垹闄ゃ傚傛灉鍒涘緩鍚嶄負 #employees 鐨勬湰鍦頒復鏃惰〃錛屽彧鏈夋偍鑳藉硅ヨ〃鎵ц屾搷浣滀笖鍦ㄦ柇寮榪炴帴鏃惰ヨ〃鍒犻櫎銆傚傛灉鍒涘緩鍚嶄負 ##employees 鐨勫叏灞涓存椂琛錛屾暟鎹搴撲腑鐨勪換浣曠敤鎴峰潎鍙瀵硅ヨ〃鎵ц屾搷浣溿傚傛灉璇ヨ〃鍦ㄦ偍鍒涘緩鍚庢病鏈夊叾浠栫敤鎴蜂嬌鐢錛屽垯褰撴偍鏂寮榪炴帴鏃惰ヨ〃鍒犻櫎銆傚傛灉璇ヨ〃鍦ㄦ偍鍒涘緩鍚庢湁鍏朵粬鐢ㄦ埛浣跨敤錛屽垯 SQL Server鍦ㄦ墍鏈夌敤鎴鋒柇寮榪炴帴鍚庡垹闄よヨ〃銆
鍦ㄥ瓨鍌ㄨ繃紼嬫垨瑙﹀彂鍣ㄤ腑鍒涘緩鐨勬湰鍦頒復鏃惰〃涓庡湪璋冪敤瀛樺偍榪囩▼鎴栬Е鍙戝櫒涔嬪墠鍒涘緩鐨勫悓鍚嶄復鏃惰〃涓嶅悓銆傚傛灉鏌ヨ㈠紩鐢ㄤ復鏃惰〃錛岃屽悓鏃舵湁涓や釜鍚屽悕鐨勪復鏃惰〃錛屽垯涓嶅畾涔夐拡瀵瑰摢涓琛ㄨВ鏋愯ユ煡璇銆傚祵濂楀瓨鍌ㄨ繃紼嬪悓鏍峰彲浠ュ壋寤轟笌璋冪敤瀹冪殑瀛樺偍榪囩▼鎵鍒涘緩鐨勪復鏃惰〃鍚屽悕鐨勪復鏃惰〃銆傚祵濂楀瓨鍌ㄨ繃紼嬩腑瀵硅〃鍚嶇殑鎵鏈夊紩鐢ㄩ兘琚瑙ｉ噴涓烘槸閽堝硅ュ祵濂楄繃紼嬫墍鍒涘緩鐨勮〃錛屼緥濡傦細
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

褰撳壋寤烘湰鍦版垨鍏ㄥ矓涓存椂琛ㄦ椂錛孋REATE TABLE 璇娉曟敮鎸侀櫎 FOREIGN KEY 綰︽潫浠ュ栫殑鍏跺畠鎵鏈夌害鏉熷畾涔夈傚傛灉鍦ㄤ復鏃惰〃涓鎸囧畾 FOREIGN KEY 綰︽潫錛岃ヨ鍙ュ皢榪斿洖璀﹀憡淇℃伅錛屾寚鍑烘ょ害鏉熷凡琚蹇界暐錛岃〃浠嶄細鍒涘緩錛屼絾涓嶅叿鏈 FOREIGN KEY 綰︽潫銆傚湪 FOREIGN KEY 綰︽潫涓涓嶈兘寮曠敤涓存椂琛ㄣ
鑰冭檻浣跨敤琛ㄥ彉閲忚屼笉浣跨敤涓存椂琛ㄣ傚綋闇瑕佸湪涓存椂琛ㄤ笂鏄懼紡鍦板壋寤虹儲寮曟椂錛屾垨澶氫釜瀛樺偍榪囩▼鎴栧嚱鏁伴渶瑕佷嬌鐢ㄨ〃鍊兼椂錛屼復鏃惰〃寰堟湁鐢ㄣ傞氬父錛岃〃鍙橀噺鎻愪緵鏇存湁鏁堢殑鏌ヨ㈠勭悊銆
鍜孫racle鐨勪笉鍚岀偣錛
1. SQL Server涓存椂琛ㄦ槸涓縐嶁濆唴瀛樿〃鈥,琛ㄦ槸瀛樺偍鍦ㄥ唴瀛樹腑鐨.ORACLE涓存椂琛ㄩ櫎闈炴墽琛孌ROP TABLE,鍚﹀垯琛ㄥ畾涔変細淇濈暀鍦ㄦ暟鎹瀛楀吀涓銆
2. SQL Server涓存椂琛ㄤ笉瀛樺湪綾諱技ORACLE涓存椂琛ㄤ簨鍔＄駭鍒涓婄殑鍔熻兘銆
3.SQL Server鏈鍦頒復鏃惰〃(#) 涓 ORACLE鐨勪細璇濈駭鍒涓存椂琛ㄧ被浼,浣嗘槸鍦ㄤ細璇濋鍑虹殑鏃跺,ORACLE涓嶄細鍒犻櫎琛ㄣ
4.SQL Server鐨勫叏灞涓存椂琛(##) 鏄鎸囧氫釜榪炴帴鍏變韓鍚屼竴鐗囧唴瀛.褰撴病鏈夋寚閽堝紩鐢ㄨュ唴瀛樺尯鍩熸椂,SQL Server鑷鍔ㄩ噴鏀懼叏灞涓存椂琛ㄣ
5.鐢變簬Oracle涓嶆槸涓縐嶅唴瀛樹腑鐨勬暟鎹搴. 鎵浠ュ傛灉Oracle綾諱技SQL Server 棰戠箒鐨勫逛復鏃惰〃榪涜屽緩絝嬪拰鍒犻櫎,蹇呭畾浼氬獎鍝嶆ц兘.鎵浠Oracle浼氫繚鐣欎復鏃惰〃鐨勫畾涔夌洿鍒扮敤鎴稤ROP TABLE銆
6.鍦∣racle涓,濡傛灉闇瑕佸氫釜鐢ㄦ埛鍏變韓涓涓琛(綾諱技SQL Server鐨勫叏灞涓存椂琛##).鍒欏彲浠ュ埄鐢ㄦ案涔呰〃,騫朵笖鍦ㄨ〃涓娣誨姞涓浜涘彲浠ュ敮涓鏍囪瘑鐢ㄦ埛鐨勫垪.鍒╃敤瑙﹀彂鍣ㄥ拰瑙嗗浘.褰撶敤鎴烽鍑虹殑鏃跺,鏍規嵁璇ョ櫥闄嗙敤鎴風殑鍞涓淇℃伅鍒犻櫎鐩稿簲鐨勮〃涓鐨勬暟鎹. 榪欑嶆柟娉曠粰Oracle甯︽潵浜嗕竴瀹氶噺鐨勮礋杞姐
鍏充簬Oracle涓存椂琛ㄥ拰SQL Server涓存椂琛ㄧ殑涓嶅悓鐐瑰規瘮鐨勭浉鍏崇煡璇嗗氨浠嬬粛鍒拌繖閲屼簡錛屽笇鏈涙湰嬈＄殑浠嬬粛鑳藉熷規偍鏈夋墍鏀惰幏錛