SQL Server中的临时表

最新推荐文章于 2024-11-09 21:51:58 发布

coco3600

最新推荐文章于 2024-11-09 21:51:58 发布

阅读量292

点赞数

文章标签：数据库面试

SQL Server中的临时表

官方： https://technet.microsoft.com/zh-cn/library/ms177399(v=sql.105).aspx 、 https://technet.microsoft.com/zh-cn/library/ms186986(v=sql.105).aspx

临时表

临时表有两种类型：本地表和全局表。在与首次创建或引用表时相同的 SQL Server 实例连接期间，本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与 SQL Server 实例断开连接后，将删除全局临时表。

临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。

临时表有两种类型：本地表和全局表。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。

如果创建的临时表带有命名约束并且是在用户定义的事务范围内创建的，则每次只能有一个用户执行创建临时表的语句。例如，如果某个存储过程创建一个带有命名主键约束的临时表，则该存储过程不能由多个用户同时执行。

临时表的许多用途可由具有 table 数据类型的变量替换。有关使用 table 变量的详细信息，请参阅表 (Transact-SQL)。

本文导读：临时对象都以#或##为前缀，临时表是临时对象的一种，临时对象都存储在tempdb中。以#前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问。临时表以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，当然用户在会话中可以通过DROP TABLE命令提前销毁临时表。

一。临时表介绍

临时表在运行被创建的，虽说它的名字叫做临时表，可是对它的操作和我们平时操控表的操作基本一样，比如最简单增、删、改、查等等。但是，需要注意的是临时表的创建是有范围限制的。

二、临时表分类

临时表分为2种，一种是本地临时表，另外一种就是全局临时表。

2.1本地临时表

以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表，链接断开时临时表即被删除（本地临时表为创建它的该链接的会话所独享）或者这样说局部临时表是有当前用户创建的，并且只有当前用户的会话才可以访问。

2.2全局临时表

以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表或者这样说只要这个全局临时表存在，那么用户创建会话后对所有的用户都是可见的。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们（换句话说旧的任务还何以引用）。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。

三。临时表的创建

3.1 创建本地临时表

方法一：

create table #临时表名(字段1 约束条件,
字段2 约束条件,
.....)

方法二：
select * into #临时表名 from 你的表;

代码如下：

Create table #Users(ID int,UserName varchar(50),Password varchar(50),RegisterDate Datetime);

上面这段代码很简单，也很好理解，因为有一个# 所以它表示创建的是本地临时表。

那这个临时表创建之后，它被放到哪里了呢?它在tempdb表中的Temporary Tables 中，意思很明了就是“临时”的意思。

比如在我的SQL Server上面：

画黄色那个就是路径，有一条长长的线条，然后后面跟着数字！~对，你没错这就是临时表的表名。

临时表的全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的 table_name 不能超过 116 个字符。

添加数据操作：

insert into #Users values(1,'Lan','123456','2011.04.23')

通过“Select * from #Users”语句，执行查询如下结果：

但是，如果你把现在的查询窗口关闭了，在重新打开，然后在在查询里输入，则会进行报错，提示 #Users无效。因为本地临时表只是用在当前用户的当前连接中。所以如果当前的连接退出，会自动销毁自己的表。

3.2创建全局临时表

方法一：

create table ##临时表名(字段1 约束条件,
字段2 约束条件,
.....)

方法二：
select * into ##临时表名 from 你的表;

全局临时表的代码基本和本地临时的代码一致，唯一的区域是，本地临时表的表明前面是一个#，而全局临时表的表名是2个##。

如：

Create Table ##Users(ID int,UserName varchar(50),Password varchar(50));

它也存储在老地方----Temporary Tables中，

四。什么时候用临时表及注意事项

在下面的3个场景中，可以考虑用到临时表：

在存储过程中，我们需要对大量数据进行操作
这是游标使用的替代的解决方案，我们可以把结果输入到临时表中，然后在在从临时表中抽取我们所需要的数据。
有一个复杂的comple操作的时候，也可以考虑用临时表。

注意事项：

在SQL Server中，临时表是被创建在Temporary的tempdb中，所以，在一定程度上，它还是会影响数据库执行时候的性能，也就是性能问题。
当临时表做完了它的本职工作之后，临时表会自动删除。

About Me

.............................................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的QQ群小麦苗的微店

.............................................................................................................................................