SQL临时表和表变量

本文转载至：https://www.cnblogs.com/ericli-ericli/p/6826646.html

 

表变量：存储在内存中，作用域是脚本的执行过程中，脚本执行完毕之后就会释放内存，适合短时间内存储数据量小的数据集。

优点：使用灵活，使用完之后立即释放，不占用物理存储空间

缺点：只适合较小数据量的暂时存储，不能建索引，数据量稍大时查询效率慢，占内存

使用临时表和表变量的数据量大小没有具体的临界值，DBA建议1000条数据，查询列不要太多的情况下。

临时表：临时表是存储在物理硬盘中的，建表位置在tempdb库中， 可以长久存储数据

优点：能够长久存储数据，可以建立索引，和普通的物理表一样，能存储大量数据

缺点：不方便使用，使用完之后要手动的drop,不然就会一直存在（此次连接关闭后就没了）

 

具体使用：

临时表：

1.SELECT into #filterList  from table 。。。。。

2.CREATE table #tegeb ( number int , amount decimal, ststus varchar ) 

insert into #tegeb SELECT TOP 10 number, amount,atatus  FROM business.dbo.order with(NOLOCK)

SELECT * FROM #tegeb //临时表一直存在，直到链接关闭

DROP TABLE dbo.#tegeb //必须手动drop

表变量：

1. DECLARE @temp table (  number int , amount decimal, ststus varchar ) insert into @temp SELECT top 10  number, amount,status  FROM business.dbo.order

SELECT * FROM @temp    //已经不存在了

 

查看SQL执行效率的语句：

SET STATISTICS PROFILE ON：显示分析、编译和执行查询所需的时间（以毫秒为单位）。 
SET STATISTICS IO ON：报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。 
SET STATISTICS TIME ON：显示每个查询执行后的结果集，代表查询执行的配置文件。

 

使用方法：打开SQL SERVER 查询分析器，输入以下语句：

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
GO /*--SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect] 
GO /*--SQL脚本结束*/
SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF

 

SET STATISTICS IO ON
--关闭有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO OFF
--显示[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL  ON
--关闭[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL  OFF