SqlServer自定义类型的使用

前言

最近接触了SqlServer的自定义类型–TYPE，在此记录一下所得

分类

在 SQL Server 中，用户定义的类型（User-Defined Types, UDT）是一种扩展 SQL Server 内置数据类型的功能，允许开发者创建自己的复杂数据类型。用户定义的类型可以基于标量类型（如 int、varchar 等）或者用户定义的表类型（table types），还可以基于** CLR**（Common Language Runtime），这类类型是通过 .NET Framework 编写的类来定义的。

基于标量类型

这种是最简单的，可以理解为将SqlServer支持的基本类型约束一下范围，取个别名。

新建

CREATE TYPE [ schema_name. ] type_name
{FROM base_type[ ( precision [ , scale ] ) ][ NULL | NOT NULL ]} [ ; ]

查询语句

SELECTt.name  [类型名],tt.name  [源类型],t.max_length,t.precision,t.scale,t.is_nullable ,SUSER_SNAME( u.sid ) [类型所有者],s.name  [所处架构]
FROMsys.types tJOIN sys.types tt ON t.system_type_id = tt.user_type_idJOIN sys.schemas s ON s.schema_id = t.schema_id JOIN sys.sysusers u ON s.principal_id = u.uid 
WHEREt.is_user_defined = 1 AND t.is_table_type = 0 AND t.is_assembly_type = 0;

用户定义的表类型

新建

CREATE TYPE [ schema_name. ] type_name
AS TABLE ( { <column_definition> [ , ...n ] }| [ <table_constraint> ] [ , ...n ]| [ <table_index> ] [ , ...n ] )[ WITH ( <table_option> [ , ...n ] ) ][ ; ]

查询语句

SELECTt.name [自定义类型名],s.name [所处架构],c.name [列名],y.name [列类型],SUSER_SNAME( u.sid ) [所有者],c.max_length,c.precision,c.scale,c.is_identity,c.is_nullable 
FROMsys.table_types tINNER JOIN sys.columns c ON c.object_id = t.type_table_object_idINNER JOIN sys.types y ON y.user_type_id = c.user_type_idINNER JOIN sys.schemas s ON s.schema_id = t.schema_idLEFT JOIN sys.sysusers u ON s.principal_id = u.uid 
WHEREt.is_user_defined = 1 AND t.is_table_type = 1

基于 CLR

该类型引用注册的 assembly 中定义的类型，新建反而比较简单。

新建

CREATE TYPE [ schema_name. ] type_name
{FROM  EXTERNAL NAME assembly_name [ .class_name ]
} [ ; ]

查询语句

-- 查询所有基于 CLR 的用户定义类型
SELECTt.name [自定义类型名],a.name  [assembly类型名] ,s.name [所处架构],SUSER_SNAME( u.sid ) [所有者]
FROMsys.types tJOIN sys.assemblies a ON t.principal_id = a.assembly_id JOIN sys.schemas s ON s.schema_id = t.schema_idJOIN sys.sysusers u ON s.principal_id = u.uid 
WHEREt.is_user_defined = 1 AND t.is_assembly_type = 1;