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涓存椂琛ㄧ殑涓嶅悓镣瑰规瘆镄勭浉鍏崇煡璇嗗氨浠嬬粛鍒拌繖閲屼简锛屽笇链涙湰娆＄殑浠嬬粛鑳藉熷规偍链夋墍鏀惰幏锛