SQL 使用TEMP TABLE 跟使用 WITH 差異

ms sql

Luke 2019-09-12 15:37:35 ‧ 16381 瀏覽

分享至

SQL 使用TEMP TABLE 跟使用 WITH 差異?

TEMP TABLE 也有分
區域性的暫存資料表(Local)，在 tempdb 資料庫中
資料型別的暫存資料表，在記憶體中

SELECT EMP_ID,EMP_NAME ...略
INTO #TEMP_ABC
FROM dbo.SKY_USER WHERE ...略

SELECT DEP_ID,EMP_NAME ...略
INTO #TEMP_DEF
FROM dbo.SKY_DEP AS H
JOIN #TEMP_ABC AS D ON D.EMP_ID= H.ID
WHERE ...略

DROP TABLE #TEMP_ABC;
DROP TABLE #TEMP_DEF;


WITH TEMP_ABC AS (SELECT EMP_ID,EMP_NAME ...略
FROM dbo.SKY_USER WHERE ...略),
TEMP_DEF AS(SELECT DEP_ID,EMP_NAME ...略
FROM dbo.SKY_DEP AS H
JOIN #TEMP_ABC AS D ON D.EMP_ID= H.ID
WHERE ...略)

Zed_Yang iT邦新手 3 級 ‧ 2019-09-12 16:49:06 檢舉

似乎是差在效率上
不管用@table 或是 #table 都額外增加I/O開銷
因此在2005年提供公用表表達式(CTE)
也就是問題中的WITH AS
至於詳細可能要請其他神人回答

參考來源 http://daidos200211.blogspot.com/2012/05/sql-with.html

小魚 iT邦大師 1 級 ‧ 2019-09-12 22:29:08 檢舉

資料庫的效能優化是一門很深的學問,
這只能靠經驗來學習了.

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

4 個回答

暐翰

iT邦大師 1 級 ‧ 2019-09-13 20:20:04

最佳解答

WITH .. AS技術被稱為CTE(Common Table Expression),在我的經驗中有三個重要差別 :

1.CTE可以使用`原表格索引`,temptable不行

舉例:

with cte as (
    select T1.Column,T1.Id from [tableA] T1
    join [TableB] T2 on T1.Id = T2.Id
    where 條件篩選
)
select * from cte
where Id = xxx

2.CTE可以`批量`對原有表格做DML動作(update,delete,insert)

舉例:
使用CTE更新符合條件欄位資料

with cte as (
    select T1.Column from [tableA] T1
    join [TableB] T2 on T1.Id = T2.Id
    where 條件篩選
)
update cte set Column = 新值;

3.CTE支援`遞歸`

特別適合查詢有階層關係的資料,像是傳統產業公司展BOM表需求
舉例:
SQL Server 2012 | db<>fiddle


create table t
	([bom_no] varchar(5), [prd_no] varchar(1), [child_bom_no] varchar(5))
;
	
insert into t
	([bom_no], [prd_no], [child_bom_no])
values
	('a->a0', 'b', null),
	('a->a0', 'c', 'c->a0'),
	('a->a0', 'd', 'd->a0'),
	('c->a0', 'e', null),
	('c->a0', 'f', 'f->a0'),
	('d->a0', 'g', null),
	('f->a0', 'h', null),
	('f->a0', 'i', 'i->a0'),
	('i->a0', 'j', null),
	('i->a0', 'k', null),
	('l->a0', 'm', null)	
;

declare @bom_no varchar(55) = 'c->a0';
with cte as (
    select * from T 
    where bom_no = @bom_no
    union all
    select T1.* from T T1
    inner join cte T2 on T1.bom_no = T2.Child_BOM_NO
    where T2.bom_no is not null
)
select @bom_no as top_bom_no,* from cte;