比较表变量和临时表
来源:互联网 发布:刮刮乐试刮软件 编辑:程序博客网 时间:2024/05/18 00:57
1、回滚事务对表变量无效,回滚事务对临时表有效
![](http://img.my.csdn.net/uploads/201301/03/1357228569_8288.jpg)
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 tranINSERT INTO @TestTable (Name, Age) VALUES ('Roy', 25),('中国风', 21);rollback tranSELECT 'Insert rows' as Action, * FROM @TestTable;/*ActionRowIDNameAgerowguidInsert rows1Roy2584C4F6CC-3AB3-4D7D-8779-0DCF3414AB61Insert rows2中国风211DB02A77-F20E-4326-A4E4-023CE9F5DEA7*/goif OBJECT_ID('Tempdb..#TestTable') is not nulldrop table #TestTableCREATE 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 tranSELECT 'Insert rows' as Action,* FROM #TestTable/*无记录*/
2、临时表支持用into生成临时表/TRUNCATE清空表/set identity_insert/drop table,表变量不支持
use tempdbgoif OBJECT_ID('Tempdb..#TestTable') is not nulldrop table #TestTable--intoselect 1 as ID into #TestTable--TRUNCATETRUNCATE table #TestTablegoif OBJECT_ID('Tempdb..#TestTable2') is not nulldrop table #TestTable2Create table #TestTable2(ID int identity,Num int)goset identity_insert #TestTable2 on;insert into #TestTable2(ID,Num) values(1,20)set identity_insert #TestTable2 off;godrop table #TestTable,#TestTable23、表变量可以在创建表时定义主健PRIMARY KEY和唯一约束UNIQUE和检查check
use tempdbgoSET NOCOUNT ON;-- 记录当前tempdb里的表对象IDif OBJECT_ID('Tempdb..#test') is not nulldrop table #testSELECT object_id INTO #test FROM tempdb.sys.tables;GO--创建表变量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');--获取表变量对象的IDDECLARE @object_id INT;SELECT @object_id = (SELECT object_id FROM tempdb.sys.tables EXCEPT SELECT object_id FROM #test);-- 获取表变量的索引名;with IXCas(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)select 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 IXCROSS APPLY(SELECT index_columns = (SELECT N', ' + QUOTENAME(column_name)+ CASE is_descending_key WHEN 1 THEN N' DESC' ELSE N'' ENDFROM IXCWHERE 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_COLwhere IX.object_id= @object_id/*object_idindex_nameindex_type_descis_uniqueis_primary_keyis_unique_constraintis_disabledindex_columns1572200651UQ__#5DB5E0C__D87C70A35F9E293DCLUSTERED1010[ColB], [ColC]1572200651UQ__#5DB5E0C__287C70A3627A95E8NONCLUSTERED1010[ColC], [ColD]1572200651PK__#5DB5E0C__A5CBEAB465570293NONCLUSTERED1100[RowID], [ColA]*/GOdrop table #test
4、临时表名116个字符,表变量支持128个字符
CREATE TABLE #TestTable12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (-- ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^-- 10 20 30 40 50 60 70 80 90 100 110 120 RowID INT IDENTITY PRIMARY KEY CLUSTERED,);GO/*消息 193,级别 15,状态 1,第 2 行以 '#TestTable12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' 开头的对象名或列名太长。最大长度为 116 个字符。*/declare @TestTable1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678 TABLE(-- ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^-- 10 20 30 40 50 60 70 80 90 100 110 120 RowID INT IDENTITY PRIMARY KEY CLUSTERED);
5、查看表变量的日志变化
CHECKPOINT将当前数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚未写入磁盘的数据页。
USE tempdb GO---- 截断日志,测试开始1CHECKPOINT -- 记录当前tempdb里的表对象ID if OBJECT_ID('Tempdb..#test') is not null drop table #test SELECT object_id INTO #test FROM tempdb.sys.tables;goDECLARE @TransactionTest TABLE (ID INT PRIMARY KEY,SomeCol VARCHAR(20));--获取表变量对象的IDDECLARE @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 @TransactionTest;SELECT Operation, AllocUnitName, [Begin Time], [End Time]FROM fn_dblog(NULL, NULL)where AllocUnitName like '%'+object_Name(@object_id)+'%'GO
![](http://img.my.csdn.net/uploads/201301/03/1357228569_8288.jpg)
最后三条记录都是插入记录日志
USE tempdb GO-- 截断日志,测试开始2CHECKPOINT -- 记录当前tempdb里的表对象ID if OBJECT_ID('Tempdb..#test') is not null drop table #test SELECT object_id INTO #test FROM tempdb.sys.tables;goDECLARE @TransactionTest TABLE (ID INT PRIMARY KEY,SomeCol VARCHAR(20));--获取表变量对象的IDDECLARE @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'),(1,'Row2'),(1,'Row3');--主健重复SELECT * FROM @TransactionTest;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 nulldrop table #1exec('select 1 as ID into #1')select * from #1/*(1 行受影响)消息 208,级别 16,状态 0,第 4 行对象名 '#1' 无效。*/--全局临时表if OBJECT_ID('Tempdb..##1') is not nulldrop table #1exec('select 1 as ID into ##1')select * from ##1/*ID1*/
- 比较表变量和临时表
- 表变量和临时表的比较
- 表变量和临时表的比较
- 表变量和临时表的比较
- 表变量和临时表的比较
- 比较表变量和临时表(很不错)
- 临时表与表变量的比较
- 临时表、表变量、CTE的比较
- 临时表、表变量、CTE的比较
- 临时表、表变量、CTE的比较
- 临时表和表变量
- 临时表 和 表变量
- 临时表和表变量
- 表变量和临时表
- 临时表和表变量
- 表变量和临时表
- 表变量和临时表
- 一篇很棒的比较表变量和临时表的文章
- Power Management in Linux-Based Systems
- Ecshop二次开发:给商品添加自定义字段(三)
- Linux > More on USER ID, Password, and Group management
- 双网卡 分别支持 ipv4 和 ipv6
- Twitter Snowflake
- 比较表变量和临时表
- 如何使用crash工具分析Linux内核崩溃转储文件
- linux磁盘管理
- [夜记]夜难眠4
- Linux内核启动过程和Bootloader(总述)
- 2013年的财务预算
- 滑雪记
- [宅]最近3个月的宅生活
- 程序员面试题精选100题(47)-数组中出现次数超过一半的数字