用户定义表类型

转载：https://blog.csdn.net/czmagic/article/details/6750285

sql server2008微软数据库系统,

它在数据库引擎中的可编程性增强功能包括

新数据存储功能、

新数据类型、

新全文搜索体系结构以及

对 Transact-SQL 所做的许多改进和添加。

本篇文章是本系列文章的第1篇,本系列文章主要总结其中与我们开发工作息息相关的一些新功能.

1.用户定义表类型

1.1 简介

SQL Server 2008中，用户定义表类型是指用户所定义的表示表结构定义的类型。

您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。 
若要创建用户定义表类型，请使用CREATE TYPE语句。为了确保用户定义表类型的数据满足特定要求，您可以对用户定义表类型创建唯一约束和主键。

1.2 使用例题 

在创建用户定义表类型前先建立一个测试表TestTable

create table TestTable( 
ids int identity, 
name nvarchar(100), 
age int, 
zip nvarchar(100) 
) 

然后创建一个用户定义表类型 AType 
CREATE TYPE AType AS TABLE 
( 
name nvarchar(100) default(''), 
age INT 
)

在Microsoft SQL Server 2008的SQL Server Management Studio看到的结果如下：

现在我们开始使用用户定义表类型

（1）简单应用

--声明一个变量@t,类型为AType 
declare @t AType

--往变量@t中插入2条数据 
insert into @t (name,age) 
select 'a',22 
union all 
select 'b',33

--显示变量@t中数据 
select * from @t

--利用类型为AType的变量@t往TestTable表中插入数据 
insert into TestTable(name,age,zip) 
select name,age,'zip' 
from @t

--查询表TestTable 
select * from TestTable

（2）结合存储过程应用

先创建一个存储过程

create proc pr_inset 
@type AType READONLY 
as 
insert into TestTable(name,age,zip) 
select name,age,'zip' 
from @type 
return

注意：@type参数必须带关键字READONLY,指示不能在过程的主体中更新或修改参数。如果参数类型为用户定义的表类型，则必须指定 READONLY。

--声明一个变量@t1,类型为AType 
declare @t1 AType

--往变量@t1中插入2条数据 
insert into @t1 (name,age) 
select 'a1',11 
union all 
select 'b2',44

--调用过程pr_inset,并传递类型为AType的变量@t1 
execute pr_inset @t1

--查询表TestTable 
select * from TestTable

1.3 使用限制

请注意,用户定义表类型使用有以下限制： 
1.在创建用户定义表类型定义后不能对其进行修改。(没搞懂为什么不可以修改) 
2.不能在用户定义表类型的计算列的定义中调用用户定义函数。 
3.无法对用户定义表类型创建非聚集索引，除非该索引是对用户定义表类型创建PRIMARY KEY 或UNIQUE约束的结果。 
4.用户定义表类型不能用作表中的列或结构化用户定义类型中的字段。

2.日期,时间数据类型

SQL Server 2008增加了四个新的日期和时间数据类型: 
(1) time定义一天中的某个时间。此时间不能感知时区且基于 24 小时制 
(2) date定义一个日期 
(3) datetime2是对现有datetime类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度 
(4) datetimeoffset用于定义一个与采用 24 小时制并可识别时区的一日内时间相组合的日期。其数据范围与datetime2一致，但是其多了一个时区偏移量

详细情况可以看如下图片:

来段sql，看看各类型的效果：

select convert(time, '01:01:01.1234567 PM') AS 'time' , 
       convert(date,'2008-08-08') AS 'date' , 
       convert(datetime2(7), '2008-08-08 20:00:00. 1234567 +12:15'  ) AS 'datetime2', 
       convert(datetimeoffset(7), '2008-08-08 20:00:00.1234567 +12:15' )  AS      'datetimeoffset'

结果如下：

再看看几个新增加的函数：

--SYSDATETIME:返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。 
--时区偏移量未包含在内。 
SELECT SYSDATETIME() AS 'SYSDATETIME';

--SYSDATETIMEOFFSET:返回包含计算机的日期和时间的 datetimeoffset(7) 值，SQL Server 的实例正在该计算机上运行。 
--时区偏移量包含在内。 
SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET';

--SYSUTCDATETIME:返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。 
--日期和时间作为 UTC 时间（通用协调时间）返回。 
SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME';

--CURRENT_TIMESTAMP:返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 
--GETDATE:返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 
--GETUTCDATE:返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。 
SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP',GETDATE() AS 'GETDATE',GETUTCDATE() AS 'GETUTCDATE';

运行结果如下：