比较表变量和临时表
1、回滚事务对表变量无效，回滚事务对临时表有效
SET NOCOUNT ON; &
DECLARE @TestTable TABLE ( &
& RowID & INT IDENTITY PRIMARY KEY CLUSTERED, &
& Name & & VARCHAR(9) NOT NULL UNIQUE, &
& Age & & TINYINT NULL, &
& rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid())); &
begin tran &
INSERT INTO @TestTable (Name, Age) VALUES ('Roy', 25),('中国风', 21); &
rollback tran &
SELECT 'Insert rows' as Action, * FROM @TestT &
Action &RowID & Name & &Age rowguid &
Insert rows 1 & Roy 25 &84C4F6CC-3AB3-4D7D-8779-0DCF3414AB61 &
Insert rows 2 & 中国风 21 &1DB02A77-F20E--023CE9F5DEA7 &
if OBJECT_ID('Tempdb..#TestTable') is not null &
& & drop table #TestTable &
CREATE TABLE #TestTable ( &
& RowID & INT IDENTITY PRIMARY KEY CLUSTERED, &
& Name & & VARCHAR(9) NOT NULL UNIQUE, &
& Age & & TINYINT NULL, &
& rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid())); &
&begin tran &
INSERT INTO #TestTable (Name,Age) VALUES ('Roy', 25),('中国风', 21); &
rollback tran &
SELECT 'Insert rows' as Action,* FROM #TestTable &
2、临时表支持用into生成临时表/TRUNCATE清空表/set identity_insert/drop table,表变量不支持
use tempdb &
if OBJECT_ID('Tempdb..#TestTable') is not null &
& & drop table #TestTable &
select 1 as ID into #TestTable &
--TRUNCATE &
TRUNCATE table #TestTable &
if OBJECT_ID('Tempdb..#TestTable2') is not null &
& & drop table #TestTable2 &
Create table #TestTable2(ID int identity,Num int) &
set identity_insert #TestTable2 &
insert into #TestTable2(ID,Num) values(1,20) &
set identity_insert #TestTable2 &
drop table #TestTable,#TestTable2 &
3、表变量可以在创建表时定义主健PRIMARY KEY和唯一约束UNIQUE和检查check
use tempdb &
SET NOCOUNT ON; &
-- 记录当前tempdb里的表对象ID &
if OBJECT_ID('Tempdb..#test') is not null &
& & drop table #test &
SELECT object_id &&
& INTO #test &&
& FROM tempdb.sys. &
--创建表变量 &
DECLARE @temp TABLE ( &
& & RowID INT NOT NULL, &
& & ColA INT NOT NULL check(ColA&10), &
& & ColB char(1), &
& & ColC char(1), &
& & ColD char(1), &
& & PRIMARY KEY NONCLUSTERED (RowID, ColA), &
& & UNIQUE CLUSTERED (ColB, ColC), &
& & UNIQUE NONCLUSTERED (ColC, ColD)); &
INSERT INTO @temp VALUES (1, 1, 'B', NULL, 'D'); &
--获取表变量对象的ID &
DECLARE @object_id INT; &
SELECT @object_id = &&
& (SELECT object_id &&
& & &FROM tempdb.sys.tables &
& &EXCEPT &
& &SELECT object_id &&
& & &FROM #test); &
-- 获取表变量的索引名 &
;with IXC &
SELECT &IXC.object_id, &
& & & & IXC.index_id, &
& & & & IXC.index_column_id, &
& & & & IXC.is_descending_key, &
& & & & IXC.is_included_column, &
& & & & column_name = C.name &
& FROM tempdb.sys.index_columns IXC &
& & & &JOIN tempdb.sys.columns C &
& & & & &ON IXC.object_id = C.object_id &
& & & & AND IXC.column_id = C.column_id &
&WHERE IXC.object_id = @object_id &
& & & & IX.object_id, &
& & & & index_name = IX.name, &
& & & & index_type_desc = IX.type_desc, &
& & & & IX.is_unique, &
& & & & IX.is_primary_key, &
& & & & IX.is_unique_constraint, &
& & & & IX.is_disabled, &
& & & & index_columns = STUFF(IXC_COL.index_columns, 1, 2, N'') &
from tempdb.sys.indexes as IX &
CROSS APPLY( &
SELECT index_columns = &&
(SELECT &&
& & N', ' + QUOTENAME(column_name)+ CASE is_descending_key WHEN 1 THEN N'
DESC' ELSE N'' END &
FROM IXC &
WHERE object_id = IX.object_id AND index_id = IX.index_id AND is_included_column =
0 ORDER BY index_column_id FOR XML PATH(''), ROOT('r'), TYPE).value('/r[1]',
'nvarchar(max)') & & &
)IXC_COL &
where IX.object_id= @object_id &
object_id & index_name &index_type_desc is_unique & is_primary_key &is_unique_constraint
& &is_disabled index_columns &
&UQ__#5DB5E0C__D87C70A35F9E293D &CLUSTERED & 1 & 0 & 1 & 0 & [ColB],
&UQ__#5DB5E0C__287C70A &NONCLUSTERED & &1 & 0 & 1 & 0 &
[ColC], [ColD] &
&PK__#5DB5E0C__A5CBEAB &NONCLUSTERED & &1 & 1 & 0 & 0 &
[RowID], [ColA] &
drop table #test &
4、临时表名116个字符，表变量支持128个字符
CREATE TABLE #TestTable
-- & & & & & & & & & &^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ &
-- & & & & & & & & & 10 & & & & 20 & & & &30 & & & &40 & & & &50 & & & &60 & & & &70 & & & &80 & & & &90 & & & 100 & & & 110 & & & 120 &
& RowID INT IDENTITY PRIMARY KEY CLUSTERED, &
消息 193，级别 15，状态 1，第 2 行 &
以 '#TestTable
7890' 开头的对象名或列名太长。最大长度为 116 个字符。 &
declare & & &@TestTable12345
45678 TABLE( &
-- & & & & & & & & & &^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ & & & & ^ &
-- & & & & & & & & & 10 & & & & 20 & & & &30 & & & &40 & & & &50 & & & &60 & & & &70 & & & &80 & & & &90 & & & 100 & & & 110 & & & 120 &
& RowID INT IDENTITY PRIMARY KEY CLUSTERED); &
5、查看表变量的日志变化
CHECKPOINT将当前的全部脏页写入磁盘。&脏页&是已输入缓存区高速缓存且已修改但尚
未写入磁盘的数据页。
USE tempdb &&
---- 截断日志，测试开始1 &
CHECKPOINT &&
-- 记录当前tempdb里的表对象ID & &
if OBJECT_ID('Tempdb..#test') is not null & &
& & drop table #test & &
SELECT object_id & &&
& INTO #test & &&
& FROM tempdb.sys. &
DECLARE @TransactionTest TABLE ( &
ID INT PRIMARY KEY, &
SomeCol VARCHAR(20) &
--获取表变量对象的ID &
DECLARE @object_id INT; & &
SELECT @object_id = & &&
& (SELECT object_id & &&
& & &FROM tempdb.sys.tables & &
& &EXCEPT & &
& &SELECT object_id & &&
& & &FROM #test); &&
INSERT INTO @TransactionTest (ID, SomeCol) VALUES (0,'Row1'); &
INSERT INTO @TransactionTest (ID, SomeCol) VALUES (1,'Row2'); &
BEGIN TRANSACTION; &
INSERT INTO @TransactionTest (ID, SomeCol) VALUES (2,'Row3'); &
ROLLBACK TRANSACTION; &
SELECT * FROM @TransactionT &
SELECT Operation, AllocUnitName, [Begin Time], [End Time] &
FROM fn_dblog(NULL, NULL) &
where AllocUnitName like '%'+object_Name(@object_id)+'%' &
最后三条记录都是插入记录日志
USE tempdb &&
-- 截断日志，测试开始2 &
CHECKPOINT &&
-- 记录当前tempdb里的表对象ID & &
if OBJECT_ID('Tempdb..#test') is not null & &
& & drop table #test & &
SELECT object_id & &&
& INTO #test & &&
& FROM tempdb.sys. &
DECLARE @TransactionTest TABLE ( &
ID INT PRIMARY KEY, &
SomeCol VARCHAR(20) &
--获取表变量对象的ID & & &
DECLARE @object_id INT; & &
SELECT @object_id = & &&
& (SELECT object_id & &&
& & &FROM tempdb.sys.tables & &
& &EXCEPT & &
& &SELECT object_id & &&
& & &FROM #test); &&
INSERT INTO @TransactionTest (ID, SomeCol) &
(0,'Row1'), &
(1,'Row2'), &
(1,'Row3');--主健重复 &
SELECT * FROM @TransactionT &
SELECT Operation, AllocUnitName, [Begin Time], [End Time] &&
& FROM fn_dblog(NULL, NULL) &
& where AllocUnitName like '%'+object_Name(@object_id)+'%'; &
插入第3条记录出错，有删除前两条的动作
6、临时表有两种类型：本地表和全局表。它们在名称、可见性以及可用性上有区别。本地临时表
的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例
断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，
当所有引用该表的用户从 SQL Server 断开连接时被删除。
--本地临时表 &
if OBJECT_ID('Tempdb..#1') is not null &
& & drop table #1 &
exec('select 1 as ID into #1') &
select * from #1 &
(1 行受影响) &
消息 208，级别 16，状态 0，第 4 行 &
对象名 '#1' 无效。 &
--全局临时表 &
if OBJECT_ID('Tempdb..##1') is not null &
& & drop table #1 &
exec('select 1 as ID into ##1') &
select * from ##1 &我们在数据库中使用表的时候,经常会遇到两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候，我们如何灵活的在存储过程中运用它们，虽然它们实现的功能基本上是一样的，可如何在一个存储过程中有时候去使用临时表而不使用表变量，有时候去使用表变量……
声明：该文章系网友上传分享，此内容仅代表网友个人经验或观点，不代表本网站立场和观点；若未进行原创声明，则表明该文章系转载自互联网；若该文章内容涉嫌侵权，请及时向
论文写作技巧
上一篇：下一篇：
相关经验教程比特客户端
您的位置：
详解大数据
详解大数据
详解大数据
详解大数据
Oracle临时表和SQL Server临时表的不同点对比
　　创建临时表的过程以及和临时表的不同点的对比的相关知识是本文我们主要要介绍的内容，接下来就让我们一起来了解一下这部分内容吧，希望能够对您有所帮助。
　　1.简介
　　Oracle数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的清空，但是临时表的结构以及元数据还在用户的数据字典中。
　　2.详细介绍
　　Oracle临时表分为会话级临时表和事务级临时表。
　　会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。
　　事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束(commit or rollback)，Oracle自动清除临时表中数据。
　　临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，临时表不需要DML锁。
　　当一个会话结束(用户正常退出 用户不正常退出 ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的表执行 TRUNCATE 语句清空临时表数据.但不会清空其它会话临时表中的数据.
　　你可以索引临时表和在临时表基础上建立.同样,建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效. 临时表可以拥有触发器.
　　3.建立临时表
　　临时表的定义对所有会话SESSION都是可见的,但是表中的数据只对当前的会话或者事务有效.
　　建立方法：
　　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& SQL& select * from admin_work_ SQL& select * 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_ 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& SQL& select * A ---------- 1 2 SQL& select * from admin_work_ STARTDATE ENDDATE CLASS ---------- ---------- -------------------- 17-1?? -03 17-1?? -03 session temperary
　　会话2：
　　SQL& select * A ---------- 1 2 SQL& select * from admin_work_ 未选择行.
　　会话2看不见会话1中临时表的数据。
　　4.Oracle临时表和SQL Server临时表异同
　　SQL Server也可以创建临时表。临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。
　　有本地和全局两种类型的临时表，二者在名称、和可用性上均不相同。本地临时表的名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 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 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后引用此表的Transact-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
　　当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定 FOREIGN KEY 约束，该语句将返回警告信息，指出此约束已被忽略，表仍会创建，但不具有 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。
　　考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使用表值时，临时表很有用。通常，表变量提供的查询处理。
　　和Oracle的不同点：
　　1. SQL Server临时表是一种”内存表”,表是存储在内存中的.ORACLE临时表除非执行DROP TABLE,否则表定义会保留在数据字典中。
　　2. SQL Server临时表不存在类似ORACLE临时表事务级别上的功能。
　　3.SQL Server本地临时表(#) 与 ORACLE的会话级别临时表类似,但是在会话退出的时候,ORACLE不会删除表。
　　4.SQL Server的全局临时表(##) 是指多个连接共享同一片内存.当没有指针引用该内存区域时,SQL Server自动释放全局临时表。
　　5.由于Oracle不是一种内存中的数据库. 所以如果Oracle类似SQL Server 频繁的对临时表进行建立和删除,必定会影响性能.所以Oracle会保留临时表的定义直到用户DROP TABLE。
　　6.在Oracle中,如果需要多个用户共享一个表(类似SQL Server的全局临时表##).则可以利用永久表,并且在表中添加一些可以唯一标识用户的列.利用触发器和视图.当用户退出的时候,根据该登陆用户的唯一信息删除相应的表中的数据. 这种方法给Oracle带来了一定量的负载。
　　关于Oracle临时表和SQL Server临时表的不同点对比的相关知识就介绍到这里了，希望本次的介绍能够对您有所收获!
[ 责任编辑：之极 ]
去年，手机江湖里的竞争格局还是…
甲骨文的云战略已经完成第一阶段…
软件信息化周刊
比特软件信息化周刊提供以数据库、操作系统和管理软件为重点的全面软件信息化产业热点、应用方案推荐、实用技巧分享等。以最新的软件资讯，最新的软件技巧，最新的软件与服务业内动态来为IT用户找到软捷径。
商务办公周刊
比特商务周刊是一个及行业资讯、深度分析、企业导购等为一体的综合性周刊。其中，与中国计量科学研究院合力打造的比特实验室可以为商业用户提供最权威的采购指南。是企业用户不可缺少的智选周刊！
比特网络周刊向企业网管员以及网络技术和产品使用者提供关于网络产业动态、技术热点、组网、建网、网络管理、网络运维等最新技术和实用技巧，帮助网管答疑解惑，成为网管好帮手。
服务器周刊
比特服务器周刊作为比特网的重点频道之一，主要关注x86服务器，RISC架构服务器以及高性能计算机行业的产品及发展动态。通过最独到的编辑观点和业界动态分析，让您第一时间了解服务器行业的趋势。
比特存储周刊长期以来，为读者提供企业存储领域高质量的原创内容，及时、全面的资讯、技术、方案以及案例文章，力求成为业界领先的存储媒体。比特存储周刊始终致力于用户的企业信息化建设、存储业务、数据保护与容灾构建以及数据管理部署等方面服务。
比特安全周刊通过专业的信息安全内容建设，为企业级用户打造最具商业价值的信息沟通平台，并为安全厂商提供多层面、多维度的媒体宣传手段。与其他同类网站信息安全内容相比，比特安全周刊运作模式更加独立，对信息安全界的动态新闻更新更快。
新闻中心热点推荐
新闻中心以独特视角精选一周内最具影响力的行业重大事件或圈内精彩故事，为企业级用户打造重点突出，可读性强，商业价值高的信息共享平台；同时为互联网、IT业界及通信厂商提供一条精准快捷，渗透力强，覆盖面广的媒体传播途径。
云计算周刊
比特云计算周刊关注云计算产业热点技术应用与趋势发展，全方位报道云计算领域最新动态。为用户与企业架设起沟通交流平台。包括IaaS、PaaS、SaaS各种不同的服务类型以及相关的安全与管理内容介绍。
CIO俱乐部周刊
比特CIO俱乐部周刊以大量高端CIO沙龙或专题研讨会以及对明星CIO的深入采访为依托，汇聚中国500强CIO的集体智慧。旨为中国杰出的CIO提供一个良好的互融互通 、促进交流的平台，并持续提供丰富的资讯和服务，探讨信息化建设，推动中国信息化发展引领CIO未来职业发展。
IT专家新闻邮件长期以来，以定向、分众、整合的商业模式，为企业IT专业人士以及IT系统采购决策者提供高质量的原创内容，包括IT新闻、评论、专家答疑、技巧和白皮书。此外，IT专家网还为读者提供包括咨询、社区、论坛、线下会议、读者沙龙等多种服务。
X周刊是一份IT人的技术娱乐周刊，给用户实时传递I最新T资讯、IT段子、技术技巧、畅销书籍，同时用户还能参与我们推荐的互动游戏，给广大的IT技术人士忙碌工作之余带来轻松休闲一刻。
微信扫一扫
关注Chinabyte琛ㄥ彉閲忎笌涓存椂琛ㄧ殑浼樼己鐐