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

最新推荐文章于 2022-07-29 12:55:15 发布

weixin_30537391

最新推荐文章于 2022-07-29 12:55:15 发布

阅读量103

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/Roy_88/archive/2013/01/03/5463058.html

本文通过三个测试案例详细介绍了SQL Server中自定义类型的使用范围：表变量使用自定义类型时，该类型必须在当前数据库中有效；而临时表使用自定义类型时，则类型需在Tempdb数据库中有效。测试包括了不同场景下自定义类型的创建及应用。

表变量用自定义类型时类型在当前库有效
临时表用自定义类型时类型在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)
/*
定义表变量成功
创建临时表成功
*/

转载于:https://www.cnblogs.com/Roy_88/archive/2013/01/03/5463058.html