当前位置：首页 -> 基础 -> 数据库编程

比较表变量和临时表(二)

2014-11-24 11:14:20 · 作者: · 浏览: 2
标签: 比较 变量 临时
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
1572200651 UQ__#5DB5E0C__D87C70A35F9E293D CLUSTERED 1 0 1 0 [ColB],
[ColC]
1572200651 UQ__#5DB5E0C__287C70A3627A95E8 NONCLUSTERED 1 0 1 0
[ColC], [ColD]
1572200651 PK__#5DB5E0C__A5CBEAB465570293 NONCLUSTERED 1 1 0 0
[RowID], [ColA]
*/
GO
drop table #test
4、临时表名116个字符，表变量支持128个字符
[sql]
CREATE TABLE #TestTable123456789012345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890 (
-- ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
-- 10 20 30 40 50 60 70 80 90 100 110 120
RowID INT IDENTITY PRIMARY KEY CLUSTERED,
);
GO
/*
消息 193，级别 15，状态 1，第 2 行
以 '#TestTable1234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890' 开头的对象名或列名太长。最大长度为 116 个字符。
*/
declare @TestTable12345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678 TABLE(
-- ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
-- 10 20 30 40 50 60 70 80 90 100 110 120
RowID INT IDENTITY PRIMARY KEY CLUSTERED);
5、查看表变量的日志变化
CHECKPOINT将当前 数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚
未写入磁盘的数据页。
[sql]
USE tempdb
GO
---- 截断日志，测试开始1
CHECKPOINT
-- 记录当前tempdb里的表对象ID
if OBJECT_ID('Tempdb..#test') is not null
drop table #test
SELECT object_id
INTO #test
FROM tempdb.sys.tables;
go
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 @TransactionTest;
SELECT Operation, AllocUnitName, [Begin Time], [End Time]
FROM fn_dblog(NULL, NULL)
where AllocUnitName like '%'+object_Name(@object_id)+'%'
GO


最后三条记录都是插入记录日志
[sql] www.2cto.com
USE tempdb
GO
-- 截断日志，测试开始2
CHECKPOINT
-- 记录当前tempdb里的表对象ID
if OBJECT_ID('Tempdb..#test') is not null
drop table #test
SELECT object_id
INTO #test
FROM tempdb.sys.tables;
go
DECLARE @TransactionTest TABLE (
ID INT PRIMARY KEY,
SomeCol VARCHAR(20)
);
--获取表变量对象的ID www.2cto.com
DECLARE @o