数据库表使用区别

1.表变量：
 概念：变量都以@或@@为前缀，表变量是变量的一种，以@前缀的表变量是本地的，因此只有在当前用户会话中才可以访问，而@@前缀的表变量是全局的，表变量存放在内存中，正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。表变量不允许有约束，当然也就不能有Default的默认值约束了，表变量另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了（也就是以为着编译阶段后就没有优化阶段了），这一特性有的时候是件好事，而有些时候却会造成一些麻烦。
 使用：
DECLARE @TmpTable TABLE(
  Tmp_id int NOT NULL,
  TmpContent varchar(50) null)

INSERT INTO @TmpTable(Tmp_id, TmpContent)
 VALUES(0,Cast(GETDATE() as varchar(50)))

SELECT [@TmpTable].Tmp_id FROM @TmpTable
　　

2.临时表：
 概念：临时表与永久表相似，只是它的创建是在Tempdb中，它只有在一个数据库连接结束后或者由SQL命令DROP掉，才会消失，否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志，虽它们在Tempdb中体现，是分配在内存中的，它们也支持物理的磁盘，但用户在指定的磁盘里看不到文件。临时表分为本地和全局两种，本地临时表的名称都是以“#”为前缀，只有在本地当前的用户连接中才是可见的，以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，因此只有在当前用户会话中才可以访问，当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀，创建后对任何用户都是可见的，当所有引用该表的用户断开连接时被删除,也可以通过DROP TABLE命令提前销毁临时表。临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。跟表变量另外一个显著去别就是临时表可以创建索引，也可以定义统计数据，因此SQL Server在处理访问临时表的语句时需要考虑执行计划优化的问题。
 使用跟普通数据表一样，只是在定义与使用时加了"#"或"##"
CREATE TABLE dbo.#TmpTable (
  Tmp_id int NOT NULL,
  TmpContent varchar(50) default '无内容')

INSERT INTO dbo.#TmpTable (Tmp_id, TmpContent)
 VALUES (0,Cast(GETDATE() as varchar(50)))

SELECT  dbo.#TmpTable.Tmp_id  FROM dbo.#TmpTable

DROP TABLE dbo.[#TmpTable]

3.普通表：正常使用的标准表，就是通常提到的作为数据库中存储数据的表，是最经常使用也是最重要最基本的表，其它类型的表都是有特殊用途的表，它们往往是在特殊应用环境下，为了提高系统的使用效率而派生出来的表，它是关系模型中表示实体的方式，是用来组织和存储数据、具有行列结构的数据库对象，能代表实体，由行、列组成。

简单地总结：对于较小的临时计算用数据集推荐使用表变量，或者如果临时结果集仅仅需要往里面写数据，比如通过一个循环多次查找相关数据并合成一个临时结果集，那么就可以使用表变量。(结果有人提到了返回结果集的时候需要有排序，但是表变量不支持索引阿。其实这个不要紧，因为表变量虽然不支持索引，但是表变量支持主键阿，所以可以利用主键来替代索引)，
如果临时结果集不太多需要更改，而是更多地充当一个临时的关联数据集去参加各种数据集的连接(JOIN)，那么索引和统计数据可能会更加适合一些(当然这个临时结果集要足够大，这样索引和统计数据带来的代价才可以被弥补掉)，由于表变量不支持统计数据，因此在一个存储过程中使用表变量可以减少由于数据变化而导致的重新编译问题。如果数据集比较大，如在代码中用于临时计算，同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合（比如说COUNT、SUM、AVERAGE、MAX等），也可以考虑使用表变量，当然，除了索引和统计数据这个明显的限制外，表变量同时也不支持并行执行计划，因此对于大型的临时结果集，表变量并不是一个好的选择。

使用表变量另外一个考虑因素是应用环境的内存压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。一般对于大的数据集推荐使用临时表，同时创建索引，或者通过SQL Server的统计数据（Statisitcs）自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据，当然临时表就是唯一的选择了。需要提及的是，由于临时表存放在tempdb中，因此要注意tempdb的调优。

　对于函数中不能支持临时表是由于函数不能对函数作用域外部的资源状态造成永久性的更改，在SQL Server中也称为副作用(side effect)。不过如果在函数中使用大型的临时结果集是不推荐的，因为如果将这样的函数放置到一个查询中会造成很明显的性能问题，因此这种情况一般都采用存储过程之类的批处理脚本。对于动态脚本不支持表变量的原因是因为存储过程不接受表类型的参数。不过如果表变量的声明和赋值都在sp_executesql的参数中的话，sp_executesql就可以执行了，因为这个时候表变量就存在sp_executesql的stmt参数里面，不需要传入。

 

补充：

在临时表create table #T (…)和表变量declare @T table (…)
之间主要有3个理论上的不同。
第一个不同使事务日志不会记录表变量。因此，它们脱离了事务机制的范围，从下面的例子可显而易见：
create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
    update #T set s='new value #'
    update @T set s='new value @'
rollback transaction
select * from #T
select * from @T

s
---------------
old value #

s
---------------
new value @

在声明临时表#T和表变量@T之后，给它们分配一个相同的值为old value字符串。然后，开始一个事务去更新它们。此时，它们都将有新的相同的值new value字符串。但当事务回滚时，正如你所看到的，表变量@T保留了这个新值而没有返回old value字符串。这是因为即使表变量在事务内被更新了，它本身不是事务的一部分。

第二个主要的不同是任何一个使用临时表的存储过程都不会被预编译，然而使用表变量的存储过程的执行计划可以预先静态的编译。预编译一个脚本的主要好处在于加快了执行的速度。这个好处对于长的存储过程更加显著，因为对它来说重新编译代价太高。
最后，表变量仅存在于那些变量能存在的相同范围内。和临时表相反，它们在内部存储过程和exec（string）语句里是不可见的。它们也不能在insert/exec语句里使用。
 

    　　　　　　　　  　　表变量   　　　　临时表 
 数据集的存储位置  内存(不考虑被换到  磁盘(不考虑访
    　　　　　　　　页面文件这种情况)  问后被缓存到内存中)
 是否需要日志  　　　　 　　否   　　　　　　是
 是否可以创建索引  　　　　否   　　　　　　是
 是否可以使用统计数据  　　否   　　　　　　是
 是否可以在多会话中访问  　否   　　　　　   是
 是否需要锁机制   　　　　   否   　　　　　　是