表变量存在于内存中难道一直是被误解的！！

Question

是的，大量数据，会使用tempdb缓存数据。定义表变量，SQL内部肯定会定义临时表：declare @tb table (id int)
select * from tempdb.sys.tables -- 有一个临时表
goselect * from tempdb.sys.tables -- 没了
go表变量与临时表的主要区别是生命周期不同，另外索引方面表变量不如临时表。

Answer 1

表变量是在内存中的,不是在tempdb,测试过的.

Answer 2

这是除了存储位置之外的第二大误解了。表变量绝对是存在于tempdb的。

Answer 3

这是除了存储位置之外的第二大误解了。表变量绝对是存在于tempdb的。这个问题不知道有多少人争吵过，但是表变量存放在TempDB中是绝对可以肯定的。还有我始终不认为作用域不同时表变量和临时表最重要的区别。最重要的区别肯定是难以发现的。什么作用域不同，能建索引和不能建索引，sp_exectesql临时表支持，表变量不支持，insert into exec 临时表可以，表变量不可以。 都是直接能看到和平时用的时候碰到的。 我感觉他们最大的区别是 一个会产生统计信息一个不会。
create table #t(id int);
go
insert into #t
select 1goselect 
session_id,
database_id,
user_objects_alloc_page_count
from sys.dm_db_session_space_usage
where session_id>50/*
52 2 9
*/declare @t table (id int)
insert into @t select 1goselect 
session_id,
database_id,
user_objects_alloc_page_count
from sys.dm_db_session_space_usage
where session_id>50/*
52 2 10
*/
为什么又多分配了一页数据，因为表变量数据写在TempDB中。
像这样的问题以后还是少提吧。网上以前就有好多因为这个吵架的！
不知道有没有人攻击我类。 先闪人！

Answer 4

嗯，public0011的例子也可以，还有个更加明显的例子，在一个干净的系统来，运行下面的例子，看看是不是2个值差了1000左右。 SELECT COUNT(*)AS cached_pages_count
    ,CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(database_id) 
        END AS database_name
FROM sys.dm_os_buffer_descriptors where database_id=2
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;   declare @i int;
  set @i=0;
  declare @t table (id NCHAR(4000))
   while @i<1000
   begin
  insert into @t select replicate('A',4000)
  set @i=@i+1;
  end;
  
  SELECT COUNT(*)AS cached_pages_count
    ,CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(database_id) 
        END AS database_name
FROM sys.dm_os_buffer_descriptors where database_id=2
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;

Answer 5

对表变量跟临时表的误解真的很多；
甚至SQL server也是个被严重误解、低估的数据库系统（主要是在国内）。

Answer 6

谢谢6,7楼的测试案例,证明表变量跟tempdb有关.
案例中用到的DMV: sys.dm_os_buffer_descriptors 应该是跟内存(buffer pool)有关的喔.
参考 http://msdn.microsoft.com/en-us/library/ms173442(v=sql.105).aspx之前做过测试: 用大循环,给一个表变量循环插入记录, 用windows性能监视器, 可以看到从程序执行那一刻起, 内存是线性增长的..

Answer 7

对SQL Server的误解简直不计其数，在这一点上微软具有不可推卸的责任

Answer 8

你说得这个“sys.dm_os_buffer_descriptors 应该是跟内存(buffer pool)有关的喔 ”提得太好了，我正愁没有这样的机会对这点进行解释，我在吃饭，吃完后会详细解释表变量，临时表的第三大误解。

Answer 9

搭车问一个：
如果中止sql引擎的自动优化？select * from x where a=1
只有几十条结果，花了3秒钟select * from x where a=1 and b=2
结果更少，但是花了90秒！select * from (
select * from x where a=1) a
where b=2
仍然花了90秒！
感觉是sql编译器自作主张把b=2优化为与a=1一起执行了只有先把a=1的结果插入临时表，才能阻断sql的自作主张的优化：
select * into #t from x where a=1
select * from #t where b=2
只需3秒！（当然，实际的sql要复杂很多，但是的确可以、也应该分步执行的）这个问题遇到过几次了，不知道t-sql有没有什么语法让b=2不参与a=1的执行？

Answer 10

建一个a and b 的复合索引不就好了？

Answer 11

建一个a and b 的复合索引不就好了？a、b都只是一个严重简化的例子，实际上并不是一个字段

Answer 12

我用了6L的测试方法好像得到的结果一模一样啊tempdb 应该是肯定要放的， 至于什么情况下放就不知道了，这个也是我比较好奇的我的测试方法比较土，没有你么的看着牛逼。
比如我把数据内存最大值设置到100M，定义一个1G的表变量
创建成功了，而且tempdb变大了，文件就涨 2G，而且通过性能计数器明显的看到了
tempdb 文件有明显的写入。
这个可以说明它放到tempdb里面去了吧我是比较好奇它什么时候会存在于内存中MS没有明确的文档说明，搞的大家全靠猜，议论纷纷也很正常

Answer 13

我该从哪里说起呢？
好吧，还是从我上面的举的例子说起，
declare @i int;  
set @i=0;  
declare @t table (id NCHAR(4000))   
while @i<1000   
begin  
insert into @t select replicate('A',4000)  
set @i=@i+1;  
end;
上面的例子产生了1000个页面，那@t是在哪里产生的呢？ 
@t在tempdb 实例化，并且最初在BPOOL中产生，并且在checkpoint发生之前，它就已经存在于tempdb 中了，虽然它还没有刷入磁盘，但是它还是存在于tempdb中。 当然在checkpoint后生之后，BPOOL中的数据被输入磁盘，但是不管有没有发生checkpoint，数据有没有被输入磁盘，换句话说，无论@t只存是在BPOOL中，还是同时存在于BPOOL与磁盘，或者只存在于磁盘，@t它都存在于tempdb中。
理解上面这句话是理解准确存储引擎的关键之一。这也就是为什么上面使用DMV: sys.dm_os_buffer_descriptors 中，它对于的database_id为2，2就是tempdb嘛这也就可以解释MSDN上的那句话了，“Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache). 
”
http://support.microsoft.com/kb/305977/en-us
当然假如把上面的表变量变成用户表，USE useRDB
GO
declare @i int;  
set @i=0;  
CREATE TABLE (id NCHAR(4000))   
while @i<1000000   
begin  
insert into t select replicate('A',4000)  
set @i=@i+1;  
end;
假如你的系统是干净的，比如说刚重启，你也会通过管理器看到working set在 不断增长，很简单嘛，数据一开始是放入BPOOL的，而BPOOL就是内存的一部分，随着记录的增多，BPOOL的需求量越来越大，working set就越来越高咯。

Answer 14

感觉表达的不是很清晰，关键的地方重新描述一下，无论@t只存是在BPOOL中（记录在BPOOL生成后未发生checkpoint），还是同时存在于BPOOL与磁盘(记录在BPOOL生成后，发生了checkpoint后，并且继续往BPOOL中生成记录)，或者只存在于磁盘（lazy writer），@t它都存在于tempdb中。上面的描述同样适用于用户数据库中的用户表，就好像是在用户数据库USERDB中用户表usertable生成1000条记录，那它肯定是首先在BPOOL中生成的，但是在checkpoint之前，它也是不会被刷入磁盘的，那这个1000条记录属于数据库USERDB吗？当然拉。

Answer 15

还没有深入了解存储引擎方面的知识，这个以后慢慢补吧~
前辈可否有空看下我提的关于进程状态dormant的问题呢~不胜感激啊~

Answer 16

这是除了存储位置之外的第二大误解了。表变量绝对是存在于tempdb的。这个问题不知道有多少人争吵过，但是表变量存放在TempDB中是绝对可以肯定的。还有我始终不认为作用域不同时表变量和临时表最重要的区别。最重要的区别肯定是难以发现的。什么作用域不同，能建索引和不能建索引，sp_exectesql临时表支持，表变量不支持，insert into exec 临时表可以，表变量不可以。 都是直接能看到和平时用的时候碰到的。 我感觉他们最大的区别是 一个会产生统计信息一个不会。
create table #t(id int);
go
insert into #t
select 1goselect 
session_id,
database_id,
user_objects_alloc_page_count
from sys.dm_db_session_space_usage
where session_id>50/*
52 2 9
*/declare @t table (id int)
insert into @t select 1goselect 
session_id,
database_id,
user_objects_alloc_page_count
from sys.dm_db_session_space_usage
where session_id>50/*
52 2 10
*/
为什么又多分配了一页数据，因为表变量数据写在TempDB中。
像这样的问题以后还是少提吧。网上以前就有好多因为这个吵架的！
不知道有没有人攻击我类。 先闪人！
看你们争吵那么激烈我又回来了，
还引用自己上面的例子来回答9楼， 使用表变量导致 数据库id为2 的tempdb中又多分配了一页，
就能证明表变量不是在内存中创建的。  你的另一个问题（DMV: sys.dm_os_buffer_descriptors 应该是跟内存(buffer pool)有关的喔）
你说的很对这个视图是跟bpool有关，表变量和临时表也是要被缓存的呀，跟其他表一样啊，除非服务器内存很紧张和使用特别大的表，不管怎么样都将在内存中使用这些表。表变量在tempdb里被缓存了就能在这个视图里找到它。也能够得到它的数据库ID为2

Answer 17

如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中

Answer 18

如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。

Answer 19

如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？

Answer 20

如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？
你虽问的不是小菜鸟的我。
但是这个我正好见过。不知道是不是你想要的答案。我回答的也不一定对撒？其实你问的是数据，所有的数据都要在内存中才能被使用啊，修改插入其他数据页都是一样的啊。整个事务唯一写入磁盘的是LOG文件，数据是先写在内存里，而后经过checkpoint或懒惰写来刷入磁盘的。 至于你说的回收那我感觉它既然在tempdb里就要遵守tempdb回收的策略。 比如版本存储区60秒回收一次，当然它不回收活动的事务，如你这个事务已经提交了sqlserver会通过跟踪保存在保存在版本存储区中最小事务序列号来完成这个工作，对序列号小于这个值的事务进行批量删除。sys.dm_tran_current_transaction 中first_useful_sequence_num来告诉我们事务序列号。  当然上面只是回答了版本存储区回收机制，
tempdb 放三种对象：用户临时对象（临时表，表变量），内部临时对象（排序，散列连接，游标），版本存储区（触发器，快照隔离和读提交快照隔离，联机索引，多活动结果集）用户临时对象我感觉session对开后释放。
排序，散列我感觉语句执行完成后释放。
版本存储区 上面已经说了

Answer 21

比较表变量和临时表
http://blog.csdn.net/roy_88/article/details/8461942

Answer 22

对这个问题大家还满热情的，
那考考大家，根据34楼，
表变量的insert操作是产生日志的，但是回滚事务对表变量无效，既然回滚事务对表变量是无效的 ，那还要产生日志干什么？

Answer 23

如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？
你虽问的不是小菜鸟的我。
但是这个我正好见过。不知道是不是你想要的答案。我回答的也不一定对撒？其实你问的是数据，所有的数据都要在内存中才能被使用啊，修改插入其他数据页都是一样的啊。整个事务唯一写入磁盘的是LOG文件，数据是先写在内存里，而后经过checkpoint或懒惰写来刷入磁盘的。 至于你说的回收那我感觉它既然在tempdb里就要遵守tempdb回收的策略。 比如版本存储区60秒回收一次，当然它不回收活动的事务，如你这个事务已经提交了sqlserver会通过跟踪保存在保存在版本存储区中最小事务序列号来完成这个工作，对序列号小于这个值的事务进行批量删除。sys.dm_tran_current_transaction 中first_useful_sequence_num来告诉我们事务序列号。  当然上面只是回答了版本存储区回收机制，
tempdb 放三种对象：用户临时对象（临时表，表变量），内部临时对象（排序，散列连接，游标），版本存储区（触发器，快照隔离和读提交快照隔离，联机索引，多活动结果集）用户临时对象我感觉session对开后释放。
排序，散列我感觉语句执行完成后释放。
版本存储区 上面已经说了

如果没有被刷入磁盘，那么tempdb为什么能看到有新的Page产生，没刷入磁盘，怎么不算内存中的，却要说是以物理的形式存在于tempdb中
没有被刷入磁盘，算内存中的，但是也算在tempdb中的，并不是说被刷入磁盘才算存在于tempdb中，tempdb的存在形式并不是只算磁盘，上面已经强调过了。
貌似很牛逼的样子，有点理解了，再问一个问题啊，如果是一个表变量，但是被刷入磁盘后，然后超出了这个变量作用域后，变量就被销毁了，那是不是要删除原先写入磁盘中的数据，临时表应该也是一样的吧？    上面自己说的有错误，能不能从新解释一下子。tempDB是不参与checkpoint的，我上面却说checkpoint的时候表变量里的脏数据页刷入磁盘。这里说声 i'm sorry。tempdb只在内存压力比较大的写入磁盘而不是checkpoint。
    tempdb虽然只记录undo，也就是只能undo不能redo，就是只能回滚。至于表变量为何不能回滚是：表变量是不参与日志记录的。不维护统计信息，不能生成并行的查询计划，不允许DDL运行，不能参与事务处理和锁定。