表变量和临时表自定义类型的区别

表变量用自定义类型时类型在当前库有效
临时表用自定义类型时类型在Tempdb库有效

以下测试3个例子：

 

--测试例子1：在Test创建自定义类型SSN
use tempdb
go
if object_id('tempdb..#TestTable') IS NOT NULL 
   DROP TABLE #TestTable;
if exists (select 1 from sys.types WHERE name = 'SSN') 
   DROP TYPE dbo.SSN;
go
use Test
go
if exists (select 1 from sys.types WHERE name = 'SSN') 
   DROP TYPE dbo.SSN;
go
--在Test创建自定义类型
CREATE TYPE dbo.SSN 
  FROM CHAR(9) NOT NULL 
GO


RAISERROR('定义表变量成功',10,1) WITH NOWAIT;
DECLARE @TestTable TABLE (
  RowID INT IDENTITY,
  SSN SSN)
  
go

RAISERROR('创建临时表失败',10,1) WITH NOWAIT;
CREATE TABLE #TestTable (
  RowID INT IDENTITY,
  SSN SSN)
  
/*
定义表变量成功
消息 2715，级别 16，状态 7，第 3 行
第 2 个列、参数或变量: 找不到数据类型 SSN。

*/

go

 

--测试例子2：在Tempdb创建自定义类型SSN

use Test
go
if exists (select 1 from sys.types WHERE name = 'SSN') 
   DROP TYPE dbo.SSN;
go
use tempdb
go
if object_id('tempdb..#TestTable') IS NOT NULL 
   DROP TABLE #TestTable;
if exists (select 1 from sys.types WHERE name = 'SSN') 
   DROP TYPE dbo.SSN;
go
--在Tempdb创建自定义类型
CREATE TYPE dbo.SSN 
  FROM CHAR(9) NOT NULL; 

go
use Test
go

RAISERROR('定义表变量失败',10,1) WITH NOWAIT;
DECLARE @TestTable TABLE (
  RowID INT IDENTITY,
  SSN SSN)
go
RAISERROR('创建临时表成功',10,1) WITH NOWAIT;
CREATE TABLE #TestTable (
  RowID INT IDENTITY,
  SSN SSN)
 /*
消息 2715，级别 16，状态 7，第 3 行
第 2 个列、参数或变量: 找不到数据类型 SSN。
创建临时表成功

*/


go

 

--测试例子3：同时在Tempdb和Test创建自定义类型SSN
use tempdb
go
if object_id('tempdb..#TestTable') IS NOT NULL 
   DROP TABLE #TestTable;
if exists (select 1 from sys.types WHERE name = 'SSN') 
   DROP TYPE dbo.SSN;
go
CREATE TYPE dbo.SSN 
  FROM CHAR(9) NOT NULL; 

go
use Test
go
if exists (select 1 from sys.types WHERE name = 'SSN') 
   DROP TYPE dbo.SSN;
go
CREATE TYPE dbo.SSN 
  FROM CHAR(9) NOT NULL; 
go
RAISERROR('定义表变量成功',10,1) WITH NOWAIT;
DECLARE @TestTable TABLE (
  RowID INT IDENTITY,
  SSN SSN)
go
RAISERROR('创建临时表成功',10,1) WITH NOWAIT;
CREATE TABLE #TestTable (
  RowID INT IDENTITY,
  SSN SSN)
/*
定义表变量成功
创建临时表成功
*/