临时表和表变量（整理）
临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。
例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。
临时表
临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用.
我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享.
局部临时表
局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如:
CREATE TABLE [#DimCustomer_test]
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’
the Result is:
name
#DimCustomer_test___________________________________________________________________________________________________000000000005
全局临时表
下面我们来看一下全局临时表:
CREATE TABLE [##DimCustomer_test]
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’
The Result are:
#DimCustomer_test___________________________________________________________________________________________________000000000005
##DimCustomer_test
--Drop test temp tables
DROP TABLE [##DimCustomer_test]
DROP TABLE [#DimCustomer_test]
可以看到我们刚才创建的全局临时表名字并没有被加上标识.
表变量
表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些!
另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如:
USE AdventureWorksDW
GO
DECLARE @DimCustomer_test TABLE
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
---insert data to @DimCustomer_test
INSERT @DimCustomer_test
(
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
)
SELECT
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
FROM DimCustomer
SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)
FROM @DimCustomer_test INNER JOIN FactInternetSales ON
@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey
Group BY CustomerKey
Result:
Server: Msg 137, Level 15, State 2, Line 32
Must declare the variable ’@DimCustomer_test’.
如果我们对上面的查询进行更改,对查询使用别名(并且找开IO):
-----in the follow script,we used the table alias.
DECLARE @DimCustomer_test TABLE
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
INSERT @DimCustomer_test
(
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
)
SELECT
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
FROM DimCustomer
SELECT t.CustomerKey,f.OrderQuantity
FROM @DimCustomer_test t INNER JOIN FactInternetSales f ON
t.CustomerKey = f.CustomerKey
where t.CustomerKey=13513
表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
表变量主要开销系统 的内存，而临时表则使用tempdb。对于小数据量的中间数据存储，可以使用表变量，而当需要临时保存的数据量很庞大时，建议使用临时表。具体使用表变量还是临时表，可以根据系统的运行状况来调整。
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。
例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。
临时表
临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用.
我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享.
局部临时表
局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如:
CREATE TABLE [#DimCustomer_test]
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’
the Result is:
name
#DimCustomer_test___________________________________________________________________________________________________000000000005
全局临时表
下面我们来看一下全局临时表:
CREATE TABLE [##DimCustomer_test]
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’
The Result are:
#DimCustomer_test___________________________________________________________________________________________________000000000005
##DimCustomer_test
--Drop test temp tables
DROP TABLE [##DimCustomer_test]
DROP TABLE [#DimCustomer_test]
可以看到我们刚才创建的全局临时表名字并没有被加上标识.
表变量
表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些!
另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如:
USE AdventureWorksDW
GO
DECLARE @DimCustomer_test TABLE
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
---insert data to @DimCustomer_test
INSERT @DimCustomer_test
(
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
)
SELECT
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
FROM DimCustomer
SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)
FROM @DimCustomer_test INNER JOIN FactInternetSales ON
@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey
Group BY CustomerKey
Result:
Server: Msg 137, Level 15, State 2, Line 32
Must declare the variable ’@DimCustomer_test’.
如果我们对上面的查询进行更改,对查询使用别名(并且找开IO):
-----in the follow script,we used the table alias.
DECLARE @DimCustomer_test TABLE
(
[CustomerKey] [int]
, [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
INSERT @DimCustomer_test
(
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
)
SELECT
[CustomerKey]
, [FirstName]
,[MiddleName]
,[LastName]
FROM DimCustomer
SELECT t.CustomerKey,f.OrderQuantity
FROM @DimCustomer_test t INNER JOIN FactInternetSales f ON
t.CustomerKey = f.CustomerKey
where t.CustomerKey=13513
表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
表变量主要开销
