34,576
社区成员
发帖
与我相关
我的任务
分享表变量存在于内存中难道一直是被误解的！！
今天看《sqlserver2005技术内幕》作者在文中写到：表变量被误解的两个理解，一种是表变量存在于内存中，另外一种是表变量比临时表更好。
因此我还网上特意再搜索一下表变量与临时表的区别，几乎都是写表变量存在于内存中，怎么会有这样的误传？？？
该书中写到其实临时表与表变量都是物理的形式存在于tempdb中，当表足够小，sqlserver有足够内存时临时表与表变量的页都会驻留在缓存中
因此我还网上特意再搜索一下表变量与临时表的区别，几乎都是写表变量存在于内存中，怎么会有这样的误传？？？
该书中写到其实临时表与表变量都是物理的形式存在于tempdb中，当表足够小，sqlserver有足够内存时临时表与表变量的页都会驻留在缓存中
...全文
请发表友善的回复…
发表回复
吉普赛的歌 版主 2015-12-15
- 打赏
- 举报
放不掉， 没有drop table @t 的语法
asuccessyang 2015-03-18
- 打赏
- 举报
难得各位大神都在，顺便请教下表变量使用后要不要drop掉释放空间
Trouble___Maker 2014-10-21
- 打赏
- 举报
长知识了！！！！！！
Kingdee_alex 2013-10-10
- 打赏
- 举报
MARK 学习一下
习惯性蹭分 2013-08-15
- 打赏
- 举报
下次也看看技术内幕。。
guan_tu 2013-08-15
- 打赏
- 举报
收藏下，以后学习！
sunylf 2013-08-13
- 打赏
- 举报
了解就好。。。。
Neo_whl 2013-08-07
- 打赏
- 举报
都是大神，小弟学习，可大家争论这么多，感觉没有定论啊。
tcmakebest 2013-08-03
- 打赏
- 举报
就连内存的东西，都很有可能被缓存到硬盘，还用纠结于些，这些不重要，都是浮云。
joyment2010 2013-08-02
- 打赏
- 举报
我是来学习的
拥抱开源 2013-06-18
- 打赏
- 举报
自由小菜园 2013-06-12
- 打赏
- 举报
@SQL_Beginner
三角星等级，就这么牛逼，不像嘛
魔者侠心之明星级程序员 2013-06-10
- 打赏
- 举报
如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
[/quote]
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。[/quote]
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？[/quote]
你虽问的不是小菜鸟的我。
但是这个我正好见过。不知道是不是你想要的答案。我回答的也不一定对撒？
其实你问的是数据，所有的数据都要在内存中才能被使用啊，修改插入其他数据页都是一样的啊。整个事务唯一写入磁盘的是LOG文件，数据是先写在内存里，而后经过checkpoint或懒惰写来刷入磁盘的。 至于你说的回收那我感觉它既然在tempdb里就要遵守tempdb回收的策略。 比如版本存储区60秒回收一次，当然它不回收活动的事务，如你这个事务已经提交了sqlserver会通过跟踪保存在保存在版本存储区中最小事务序列号来完成这个工作，对序列号小于这个值的事务进行批量删除。sys.dm_tran_current_transaction 中first_useful_sequence_num来告诉我们事务序列号。 当然上面只是回答了版本存储区回收机制，
tempdb 放三种对象：用户临时对象（临时表，表变量），内部临时对象（排序，散列连接，游标），版本存储区（触发器，快照隔离和读提交快照隔离，联机索引，多活动结果集）
用户临时对象我感觉session对开后释放。
排序，散列我感觉语句执行完成后释放。
版本存储区 上面已经说了
[/quote]
如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
[/quote]
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。[/quote]
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？[/quote]
上面自己说的有错误，能不能从新解释一下子。tempDB是不参与checkpoint的，我上面却说checkpoint的时候表变量里的脏数据页刷入磁盘。这里说声 i'm sorry。tempdb只在内存压力比较大的写入磁盘而不是checkpoint。
tempdb虽然只记录undo，也就是只能undo不能redo，就是只能回滚。至于表变量为何不能回滚是：表变量是不参与日志记录的。不维护统计信息，不能生成并行的查询计划，不允许DDL运行，不能参与事务处理和锁定。
[/quote]
怎么说呢，说2个点吧。
1，checkpoint也有多种类型 Automatic checkpoint不会刷，但是manual checkpoint是会耍的。
2，关于表变量跟日志的关系，表变量肯定是参与日志记录，也是肯定参与lock的。
关于表变量跟事务的关系的话，事务也分为user transaction跟system transaction,表变量不参与user transaction，但是肯定是参与system transaction的，这就是为什么不参与user transaction，但是还是生成日志的原因，因为他参与system transaction嘛。
大力水手 2013-06-10
- 打赏
- 举报
31楼是我自己，32楼某有看懂，呵呵。不吝赐教！
俺是小菜鸟！[/quote]
兄弟，我在楼上（55L）已经说了呀，32楼改为34楼，他里面有manual checkpoint 跟表变量会日志的例子。http://blog.csdn.net/roy_88/article/details/8461942
另外，1，你引用的“出至微软亚太 ”的文章是翻译自“微软雷蒙德”（就用你的话来说话啦：-），而后者的文章中关于那点就是错的，所以“出至微软亚太 ”的那篇文章自然也错了。
MSDN上关于表变量跟locking跟logging的关系的用字我觉得满准确的
“•Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.”
http://msdn.microsoft.com/en-us/library/ms175010.aspx[/quote]
多谢。嘿嘿！
shit，
微软中文技术博客网站，这个网站上关于数据库上的帖子我看完了，还有好多打印出来了。不喜欢电子版。竟然是错的。还有那些是错的呀？我还感觉那网站不错！哎。
TempDB 中表变量和局部临时表的compare
http://blogs.msdn.com/b/apgcdsd/archive/2012/03/27/tempdb-compare.aspx
的文章是翻译自“微软雷蒙德” 这篇文章谁写的你都知道有内幕啊？你在哪哈工作呀！求推荐！
魔者侠心之明星级程序员 2013-06-10
- 打赏
- 举报
31楼是我自己，32楼某有看懂，呵呵。不吝赐教！
俺是小菜鸟！[/quote]
兄弟，我在楼上（55L）已经说了呀，32楼改为34楼，他里面有manual checkpoint 跟表变量会日志的例子。http://blog.csdn.net/roy_88/article/details/8461942
另外，1，你引用的“出至微软亚太 ”的文章是翻译自“微软雷蒙德”（就用你的话来说话啦：-），而后者的文章中关于那点就是错的，所以“出至微软亚太 ”的那篇文章自然也错了。
MSDN上关于表变量跟locking跟logging的关系的用字我觉得满准确的
“•Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.”
http://msdn.microsoft.com/en-us/library/ms175010.aspx
大力水手 2013-06-10
- 打赏
- 举报
31楼是我自己，32楼某有看懂，呵呵。不吝赐教！
俺是小菜鸟！
魔者侠心之明星级程序员 2013-06-10
- 打赏
- 举报
@public0011 ，
“1,有关manual checkpoint跟表变量跟日志的关系，可以参考32楼。”改为“1,有关manual checkpoint跟表变量跟日志的关系，可以参考34楼。”
魔者侠心之明星级程序员 2013-06-10
- 打赏
- 举报
@public0011
1,有关manual checkpoint跟表变量跟日志的关系，可以参考32楼。
2,表变量跟LOCK
DBCC TRACEOFF(1200,-1)
GO
DECLARE @TV TABLE (CL int)
DBCC TRACEON(1200,-1,3604)
INSERT INTO @TV (CL) VALUES (1)
DBCC TRACEOFF(1200,-1)
GO
大力水手 2013-06-10
- 打赏
- 举报
如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
[/quote]
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。[/quote]
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？[/quote]
你虽问的不是小菜鸟的我。
但是这个我正好见过。不知道是不是你想要的答案。我回答的也不一定对撒？
其实你问的是数据，所有的数据都要在内存中才能被使用啊，修改插入其他数据页都是一样的啊。整个事务唯一写入磁盘的是LOG文件，数据是先写在内存里，而后经过checkpoint或懒惰写来刷入磁盘的。 至于你说的回收那我感觉它既然在tempdb里就要遵守tempdb回收的策略。 比如版本存储区60秒回收一次，当然它不回收活动的事务，如你这个事务已经提交了sqlserver会通过跟踪保存在保存在版本存储区中最小事务序列号来完成这个工作，对序列号小于这个值的事务进行批量删除。sys.dm_tran_current_transaction 中first_useful_sequence_num来告诉我们事务序列号。 当然上面只是回答了版本存储区回收机制，
tempdb 放三种对象：用户临时对象（临时表，表变量），内部临时对象（排序，散列连接，游标），版本存储区（触发器，快照隔离和读提交快照隔离，联机索引，多活动结果集）
用户临时对象我感觉session对开后释放。
排序，散列我感觉语句执行完成后释放。
版本存储区 上面已经说了
[/quote]
如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
[/quote]
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。[/quote]
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？[/quote]
上面自己说的有错误，能不能从新解释一下子。tempDB是不参与checkpoint的，我上面却说checkpoint的时候表变量里的脏数据页刷入磁盘。这里说声 i'm sorry。tempdb只在内存压力比较大的写入磁盘而不是checkpoint。
tempdb虽然只记录undo，也就是只能undo不能redo，就是只能回滚。至于表变量为何不能回滚是：表变量是不参与日志记录的。不维护统计信息，不能生成并行的查询计划，不允许DDL运行，不能参与事务处理和锁定。
[/quote]
怎么说呢，说2个点吧。
1，checkpoint也有多种类型 Automatic checkpoint不会刷，但是manual checkpoint是会耍的。
2，关于表变量跟日志的关系，表变量肯定是参与日志记录，也是肯定参与lock的。
关于表变量跟事务的关系的话，事务也分为user transaction跟system transaction,表变量不参与user transaction，但是肯定是参与system transaction的，这就是为什么不参与user transaction，但是还是生成日志的原因，因为他参与system transaction嘛。[/quote]
1. manual checkpoint 这个我该怎么理解呀，呵呵
不会是，use DBNAME go checkpoint 吧。只是把DBNAME换成TEMPDB吗？
这个checkpoint和自动的checkpoint原理还一样吗？
2. 有没有他参与lock和参与日志记录的例子啊？不参与lock和不参与日志记录的离职倒是很多。
书上也有拉个论坛的吧。不想抄代码了哈：
--出至微软亚太
第三，事务处理和锁定语句。表变量不能参与事务处理和锁定，以下示例说明了这一点
-- create a source table
create table
tv_source(c1 int, c2 char(100))
go
declare @i int
select @i = 0
while (@i < 100)
begin
insert into tv_source values (@i, replicate ('a', 100))
select @i = @i + 1
end
-- using #table
create table #tv_target (c11 int, c22 char(100))
go
BEGIN TRAN
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM
tv_source
--
using table variable
DECLARE @tv_target TABLE (c11 int, c22 char(100))
BEGIN TRAN
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
-- Now if I look at the locks, you will see that
only
-- #table takes locks. Here is the query that
used
-- to check the locks
select
t1.request_session_id as spid,
t1.resource_type as type,
t1.resource_database_id as dbid,
(case
resource_type
WHEN 'OBJECT' then object_name(t1.resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
t1.resource_description as description,
t1.request_mode as mode,
t1.request_status as status,
t2.blocking_session_id
from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
另一个有趣的现象是，如果回滚的事务里涉及表变量，表变量的数据不会被回滚。
Rollback
-- this
query will return 100 for table variable but 0 for #table.
SELECT COUNT(*) FROM @tv_target
• 第四，表变量上的操作不被日志文件记录。请看下面这个例子：
--
create a table variable, insert bunch of rows and update
DECLARE @tv_target TABLE (c11 int, c22 char(100))
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
-- update all the rows
update @tv_target set c22 = replicate ('b', 100)
-- look at the top 10 log records. I get no
records for this case
select top 10 operation,context, [log record fixed length], [log record length],
AllocUnitId, AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%tv_target%'
order by [Log Record Length] Desc
-- create a local temptable
drop table #tv_target
go
create table #tv_target (c11 int, c22 char(100))
go
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
--
update all the rows
update #tv_target set c22 = replicate ('b', 100)
-- look
at the log records. Here I get 100 log records for update
select
operation,context, [log
record fixed length], [log record length], AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%tv_target%'
order by [Log
Record Length] Desc
u011008359 2013-06-08
- 打赏
- 举报
貌似很牛逼的样子，有点理解了
加载更多回复（49）
