数据库中临时表,表变量和CTE使用优势极其区别，变量cte
| 1 |
在写SQL时经常会用到临时表,表变量和CTE,这三者在使用时各有优势:
|
1. 临时表:分为局部临时表和全局临时表.
1.1局部临时表,创建时以#开头,在系统数据库tempdb中存储. 在当前的链接可见,链接断开则临时表就自动被释放,也可以手动drop table #tmptable
在使用不同的链接同时创建相同的临时表时,互不影响,系统在tempdb中会自动附加以特定的session为标识的名字来区分. 常常在SP中使用,把需要操作的数据或者共同的数据取出放在临时表中,后续可以进行其他的操作(SELECT,UPDATE,DELETE,DROP等).
可以像创建永久表一样创建临时表:
CREATE TABLE #tmpTable
(
ID INT,
NAME VARCHAR(10),
COMPANY VARCHAR(50)
)
SELECT * FROM #tmpTable JOIN ...
DROP TABLE #tmpTable
也可以使用INTO创建临时表,如查询EmployeeID=1的所有订单,放在临时表中,以备后续的处理.
SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate
INTO #tmpTable
FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeID
WHERE E.EmployeeID=1
1.2全局临时表,创建时以##开头. 在tempdb中存储,对所有的session都可见.
CREATE TABLE ##tmpTable2
(
ID INT,
NAME VARCHAR(20),
COMPANY VARCHAR(50)
)
SELECT * FROM ##tmpTable2 JOIN ...
DROP TABLE ##tmpTable2
2.表变量:在内存中存储,比临时表执行速度快. 在SP或者function越过有效scope之后会自动释放,不用显式的写drop.表变量只可用在DML的操作中,会有比较多的限制.
--直接声明表变量
DECLARE @varTable TABLE
(
ID INT,
NAME VARCHAR(20),
COMPANY VARCHAR(50)
)
--先创建表类型
CREATE TYPE [dbo].[T_TEMP] AS TABLE(
ID INT,
NAME VARCHAR(20),
COMPANY VARCHAR(50)
)
--在声明表变量
DECLARE @varTable T_TEMP
3.CTE(Common Table Expressions)通用表表达:是一个可以由定义语句引用的临时命名的结果集,在它们的简单形式中,可将 CTE 视为类似于非持续性类型视图的派生表.只须定义 CTE 一次，即可在查询中多次引用.
WITH CTE_NAME
AS
(
SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate
FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeID
WHERE E.EmployeeID=1
)
SELECT * FROM CTE_NAME
CTE最强大之处在于递归查询,如要仔细研究可以参考微软的文章.
我记得以前在哪些地方看到过。
表变量和临时表是两个不同的东西，但是他们是可以用于一些共同的用途的。不过要注意一下使用的场景。
临时表
临时表有两种类型：本地表和全局表。本地临时表的名称都是以“#”为前缀，全局临时表的名称都是以“##”为前缀。临时表的访问是有可能造成物理IO的。临时表可以有索引、数据统计这些。
表变量
表变量是变量的一种，表变量也分为本地及全局的两种。表变量存放在内存中，但是并不是没有限制而是有一定限制的，如果表变量数据量超过阈值，会把内存耗尽，然后使用硬盘空间，这时再访问他就会增加了内存调入调出的机会，反而降低速度。表变量是不能有索引的。
大概就是这些，其他的记不住了。
你把联合查询的结果insert到一张表中，然后通过imp 按表导出不就行了吗（oracle数据库）？