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 ]
      
} [ ; ]

查询语句

SELECT
	t.name  [类型名],
	tt.name  [源类型],
	t.max_length,
	t.precision,
	t.scale,
	t.is_nullable ,
	SUSER_SNAME( u.sid ) [类型所有者],
	s.name  [所处架构]
FROM
	sys.types t
	JOIN sys.types tt ON t.system_type_id = tt.user_type_id
	JOIN sys.schemas s ON s.schema_id = t.schema_id 
	JOIN sys.sysusers u ON s.principal_id = u.uid 
WHERE
	t.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 ] ) ]
 [ ; ]

查询语句

SELECT
	t.name [自定义类型名],
	s.name [所处架构],
	c.name [列名],
	y.name [列类型],
	SUSER_SNAME( u.sid ) [所有者],
	c.max_length,
	c.precision,
	c.scale,
	c.is_identity,
	c.is_nullable 
FROM
	sys.table_types t
	INNER JOIN sys.columns c ON c.object_id = t.type_table_object_id
	INNER JOIN sys.types y ON y.user_type_id = c.user_type_id
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
	LEFT JOIN sys.sysusers u ON s.principal_id = u.uid 
WHERE
	t.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 的用户定义类型
SELECT
	t.name [自定义类型名],
	a.name  [assembly类型名] ,
	s.name [所处架构],
	SUSER_SNAME( u.sid ) [所有者]
FROM
	sys.types t
	JOIN sys.assemblies a ON t.principal_id = a.assembly_id 
	JOIN sys.schemas s ON s.schema_id = t.schema_id
	JOIN sys.sysusers u ON s.principal_id = u.uid 
WHERE
	t.is_user_defined = 1 
	AND t.is_assembly_type = 1;