create table #temptable 临时表 和 declare @bianliang table ()表变量
来源:互联网 发布:python tkinter 计算器 编辑:程序博客网 时间:2024/05/17 01:50
在开发过程中,经常会遇到使用表变量和本地临时表的情况。下面是对二者的一个介绍:
1. 为什么要使用表变量
表变量是从2000开始引入的,微软认为与本地临时表相比,表变量具有如下优点:
a.与其他变量的定义一样,表变量具有良好的定义范围,并会被自动清除;
b.在存储过程中使用表变量会减少存储过程重新编译的发生;
c.表变量需要更少的锁请求和日志资源;
d.可以在表变量上使用UDF,UDDT,XML。
2.表变量的限制
与临时表相比,表变量存在着如下缺点:
a.在表变量上没有统计信息,查询优化器根据固定的预估值来选择执行计划,在数据很多的情况下,会导致查询优化器选择很差的执行计划;
b.不能直接在表变量上创建索引,但可以通过创建约束(主键、唯一)来建立索引;
c.在DECLARE后,不能再对表变量进行更改;
d.不能对表变量执行INSERT EXEC,SELECT INTO语句;
e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句,但如果表变量是在动态SQL语句内定义的,则可以。
3.那什么时候可以使用表变量
要使用表变量应该根据如下规则来判断:
a.表的行数;
b.使用表变量能够减少的重新编译次数;
c.查询的类型和对索引或者统计信息的依赖程度;
d.需要生用UDF,UDDT,XML的时候。
其实也就说,得从实际出发,根据具体的查询,作出具体的选择。但是,其中很关键的一点,如果表的行数非常多,使用表变量其实是更费资源的。有人提出了这样的建议:对于行数较少的情况下(小于1000行)可以使用表变量;如果行数很多(有几万行),则使用临时表。
因此,在实际的开发中,应通过分别使用临时表或表变量进行对比后,才作出决定。
下面是一个例子,插入临时表和表变量的数据有20多万行,可以看到,使用临时表的时间是使用表变量所花时间的1/5。
表 'SalesOrderHeader'。扫描计数 3,逻辑读取 130 次,物理读取 9 次,预读 43 次,lob 逻辑读取 0 次,lob 物理读取 0次,lob 预读 0 次。
表'#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。扫描计数 3,逻辑读取 12331 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 2281 毫秒,占用时间 = 19726 毫秒。
select with temporary table: 20140 ms
********************************************************************************
表 'SalesOrderHeader'。扫描计数 0,逻辑读取 764850 次,物理读取 17 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#4E88ABD4'。扫描计数 1,逻辑读取 12331 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 4375 毫秒,占用时间 = 107160 毫秒。
select with table variable: 107160 ms
4.使用表变量的误区
对于表变量,很多人认为,表变量和其他变量一样,只存在内存中,其实这是不正确的,表变量也存在tempdb中。可以通过下面例子进行对比。
CREATE TABLE #TempTable (TT_Col1 INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
SELECT TOP 2 *
FROM tempdb.sys.objects
ORDER BY create_date DESC
name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003
#03317E3D就是刚创建的表变量;
5.其他
表变量不受rollback影响,某些情况下会破坏数据的完整性。
CREATE TABLE #TempTable (TT_Col1 INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
INSERT #TempTable VALUES (1)
INSERT @TableVariable VALUES (1)
BEGIN TRANSACTION
INSERT #TempTable VALUES (2)
INSERT @TableVariable VALUES (2)
ROLLBACK
SELECT * FROM #TempTable
/*
TT_Col1
-------
1
*/
SELECT * FROM @TableVariable
--返回了两条记录
/*
TV_Col1
-------
1
2
*/
6.参考
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
www.sqlservercentral.com/articles/63472/databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
www.sqlservercentral.com/articles/Temporary+Tables/66720/
1. 为什么要使用表变量
表变量是从2000开始引入的,微软认为与本地临时表相比,表变量具有如下优点:
a.与其他变量的定义一样,表变量具有良好的定义范围,并会被自动清除;
b.在存储过程中使用表变量会减少存储过程重新编译的发生;
c.表变量需要更少的锁请求和日志资源;
d.可以在表变量上使用UDF,UDDT,XML。
2.表变量的限制
与临时表相比,表变量存在着如下缺点:
a.在表变量上没有统计信息,查询优化器根据固定的预估值来选择执行计划,在数据很多的情况下,会导致查询优化器选择很差的执行计划;
b.不能直接在表变量上创建索引,但可以通过创建约束(主键、唯一)来建立索引;
c.在DECLARE后,不能再对表变量进行更改;
d.不能对表变量执行INSERT EXEC,SELECT INTO语句;
e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句,但如果表变量是在动态SQL语句内定义的,则可以。
3.那什么时候可以使用表变量
要使用表变量应该根据如下规则来判断:
a.表的行数;
b.使用表变量能够减少的重新编译次数;
c.查询的类型和对索引或者统计信息的依赖程度;
d.需要生用UDF,UDDT,XML的时候。
其实也就说,得从实际出发,根据具体的查询,作出具体的选择。但是,其中很关键的一点,如果表的行数非常多,使用表变量其实是更费资源的。有人提出了这样的建议:对于行数较少的情况下(小于1000行)可以使用表变量;如果行数很多(有几万行),则使用临时表。
因此,在实际的开发中,应通过分别使用临时表或表变量进行对比后,才作出决定。
下面是一个例子,插入临时表和表变量的数据有20多万行,可以看到,使用临时表的时间是使用表变量所花时间的1/5。
表 'SalesOrderHeader'。扫描计数 3,逻辑读取 130 次,物理读取 9 次,预读 43 次,lob 逻辑读取 0 次,lob 物理读取 0次,lob 预读 0 次。
表'#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。扫描计数 3,逻辑读取 12331 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 2281 毫秒,占用时间 = 19726 毫秒。
select with temporary table: 20140 ms
********************************************************************************
表 'SalesOrderHeader'。扫描计数 0,逻辑读取 764850 次,物理读取 17 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#4E88ABD4'。扫描计数 1,逻辑读取 12331 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 4375 毫秒,占用时间 = 107160 毫秒。
select with table variable: 107160 ms
4.使用表变量的误区
对于表变量,很多人认为,表变量和其他变量一样,只存在内存中,其实这是不正确的,表变量也存在tempdb中。可以通过下面例子进行对比。
CREATE TABLE #TempTable (TT_Col1 INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
SELECT TOP 2 *
FROM tempdb.sys.objects
ORDER BY create_date DESC
name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003
#03317E3D就是刚创建的表变量;
5.其他
表变量不受rollback影响,某些情况下会破坏数据的完整性。
CREATE TABLE #TempTable (TT_Col1 INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
INSERT #TempTable VALUES (1)
INSERT @TableVariable VALUES (1)
BEGIN TRANSACTION
INSERT #TempTable VALUES (2)
INSERT @TableVariable VALUES (2)
ROLLBACK
SELECT * FROM #TempTable
/*
TT_Col1
-------
1
*/
SELECT * FROM @TableVariable
--返回了两条记录
/*
TV_Col1
-------
1
2
*/
6.参考
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
www.sqlservercentral.com/articles/63472/databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
www.sqlservercentral.com/articles/Temporary+Tables/66720/
0 0
- create table #temptable 临时表 和 declare @bianliang table ()表变量
- create table #temptable 临时表 和 declare @bianliang table ()表变量
- Create table #temptable 临时表 和 declare @bianliang table ()表变量
- create table #temptable 临时表 和 declare @bianliang table ()表变量
- create table #temptable 临时表 和 declare @bianliang table ()表变量
- SQL临时表和表变量 Declare @Tablename Table
- SQL Server中的临时表和表变量 Declare @Tablename Table
- Table变量和临时表区别
- Table变量和临时表区别
- Table变量和临时表区别
- oracle临时表CREATE GLOBAL TEMPORARY TABLE
- 关于临时表和Table表变量的注释
- Oracle创建临时表:CREATE GLOBAL TEMPORARY TABLE
- Oracle创建临时表:CREATE GLOBAL TEMPORARY TABLE
- mysql (create temporary table table_name )临时表创建
- ORACLE的临时表(CREATE GLOBAL TEMPORARY TABLE)以及和SQLServer的不同点
- 临时表操作(# table)
- create table 创建表
- Java/Android引用类型及其使用分析
- 数据类型强转_笔记
- qt窗口编程
- Android 带你玩转实现游戏2048 其实2048只是个普通的控件
- LeetCode-91.Decode Ways
- create table #temptable 临时表 和 declare @bianliang table ()表变量
- 如何创建一个RPM打包环境
- Matlab R2014a使用 mcc编译出错的问题
- 忽然遇到报错:ERROR spark.SparkContext: Error initializing SparkContext.
- eclipse 打不开,一闪而过
- 多数组上中位数
- javascript之string对象 以及字符串各种用法
- byte[ ]数组 转换成16进制 字符数组
- Android中文乱码的分析与解决