mssql sqlserver with as (cte)表达式同“临时表、表变量”区别


摘要:
下文详细讲述with as(cte表达式)同”表变量及临时表”的区别



一.WITH AS用途简介

with as 是生成cte表达式的语法;
with as 可快速根据sql脚本生成临时表;
with as 是对一组查询sql脚本的缩写;
with as 生成的临时表,在紧跟的sql代码中,可以多次重复使用;
with as 使用with as 关键字可使sql脚本更优雅。
例:

  ;
  with  [临时表名称] as [字段名称...] 
  as 
   (
     sql脚本
   )

   select * from [临时表名称]    ---with as 应用;
 ------------------------------------------

mssql sqlserver “with as”关键字用法举例说明

with as 使用注意事项:
with as (CTE表达式)后面必须直接跟使用with as (CTE表达式)的SQL语句(如select、insert、update等),否则,with as (CTE表达式)将失效,
with as (CTE表达式) 在紧跟的sql语句中,使用一次就会失效。


二.临时表及表变量的应用

临时表和表变量都会在tempdb或内存中创建物理表,并进行相关的数据存储。
当我们将子查询放入表变量或临时表中时,此时会增加系统额外的I/O开销,因此表变量及临时表 应该尽量不要应用于数据量大和频繁的查询中。

三.with as 及”临时表及表变量”的区别

3.1 数据持续的时间不同
with as:在后续的脚本使用后,则会消失
临时表及表变量:在一组批处理关闭后,才会释放或显式清除数据
3.2 执行效率不同
with as 由于查询数据存储在内存临时表中,所以效率高于 “临时表及表变量”
临时表及表变量:可能数据会存储在tempdb的物理文件上,所以理论上效率比with as 低
3.3 创建方式不同、生成数据的方式不同
with as 不能包含以下子句
COMPUTE(COMPUTE BY)、order by (包含top的order by 除外)、into 、for xml 、for browse 、带查询提示的option
临时表及表变量:只要能生成集合的脚本,都支持 ,临时表还支持 select into 快速创建