SQL Server 2014允许用户自定义数据类型,即允许数据库开发人员根据需要定义符合自己开发需求的数据类型。用户自定义数据类型是建立在SQL Server 2014系统数据类型基础之上的。当多个表的列中要存储相同类型的数据,且想确保这些列具有完全相同的数据类型、长度和是否为空属性时,可以使用用户自定义数据类型。需注意的是,用户自定义数据类型虽然使用比较方便,但是需要大量的性能开销,所以使用时要谨慎。
用户可以使用SQL Server Management Studio或Transact-SQL语句来创建用户自定义数据类型。创建用户自定义数据类型时必须提供名称、新数据类型所依据的系统数据类型、数据类型是否允许空值。用户自定义数据类型一旦创建成功,用户可以像使用系统数据类型一样使用它。
1.创建用户自定义数据类型
(1)使用SQL Server Management Studio创建用户自定义数据类型
【例3.1】 为数据库“sales”定义一个基于varchar类型的数据类型telephone_code(长度为15,允许为空值),用于说明表中电话号码列的数据类型。
使用SQL Server Management Studio创建用户自定义数据类型的操作步骤如下:
1)启动SQL Server Management Studio,在“对象资源管理器”中,展开“数据库”→“sales”→“可编程性”→“类型”节点。
2)右键单击“用户定义数据类型”,从弹出的快捷菜单中选择“新建用户定义数据类型”命令,如图3-2所示。
3)弹出“新建用户定义数据类型”窗口,如图3-3所示。
在“名称”文本框中输入用户自定义数据类型的名称telephone_code。
在“数据类型”下拉列表框中选择用户定义类型所依据的系统数据类型varchar。
在“长度”数值框中输入15。
选择“允许NULL值”复选框。
设置完毕后,单击“确定”按钮,即可创建自定义的数据类型telephone_code。
(2)使用Transact-SQL语句创建用户自定义数据类型
可以使用CREATE TYPE创建用户自定义数据类型,其语法格式为:
CREATE TYPE type_name
FROM system_type [NULL | NOT NULL]
其中:
type_name:用户自定义数据类型的名称。
system_type:用户自定义数据类型所依据的系统数据类型名,如varchar、int等。
NUL| NOT NULL:是否可以为空值。如果缺省该项,则默认为NULL。
【例3.2】 为sales数据库创建一个用户自定义数据类型zip,定长字符型,长度为6,不允许为空。
使用Transact-SQL语句创建用户自定义数据类型的步骤为:
1)单击工具栏上的“新建查询”按钮,新建一个使用当前连接进行的查询,在打开的查询编辑器中输入下面的语句:
CREATE TYPE zip
FROM char(6) NOT NULL
2)输入完成之后,单击工具栏上的“执行”按钮,即可完成用户自定义数据类型的创建。执行结果如图3-4所示。
这时刷新“对象资源管理器”中的“用户定义数据类型”节点,将会看到新增的数据类型,如图3-5所示。
在SQL Server 2014中,也可以使用系统存储过程sp_addtype创建用户自定义数据类型,其语法格式为:
sp_addtype [@typename=] type,[@phystype=] system_data_type[,[@nulltype=] 'null_type']
其中:
type:用于指定用户自定义数据类型的名称。
system_data_type:用于指定自定义数据类型所依据的系统数据类型的名称。
null_type:用于指定用户自定义数据类型的NULL属性,其值可以为NULL、NOT NULL或NONULL。默认时与系统默认的NULL属性相同。
【例3.3】 为sales数据库创建一个用户自定义数据类型home_address,变长字符型,长度为128,允许为空。
sp_addtype home_address,'varchar(128)',null
在查询编辑器中输入上面的语句,输入完成之后,单击工具栏上的“执行”按钮,完成用户自定义数据类型的创建。
注意
如果用户自定义数据类型是在model数据库中创建的,它将作用于所有用户定义的新数据库中;如果用户自定义数据类型是在用户自己定义的某个数据库中创建的,则该数据类型只作用于此数据库。
用户自定义数据类型创建以后,可以像普通的系统数据类型一样应用到表中的列或变量的定义中。
2.删除用户自定义数据类型
删除用户自定义数据类型的方法也有两种。
1)在“对象资源管理器”中右键单击想要删除的用户自定义数据类型,在弹出的快捷菜单中选择“删除”命令,如图3-6所示。
这时,系统弹出“删除对象”窗口,该窗口中列出要删除的对象,单击“确定”按钮即可完成删除操作,如图3-7所示。
2)使用DROP TYPE命令或系统存储过程sp_droptype,可以删除用户自定义数据类型。DROP TYPE语句语法格式为:
DROP TYPE type_name
使用存储过程sp_droptype删除用户自定义数据类型的语法格式为:
sp_droptype type_name
其中,参数type_name表示已经定义好的用户自定义数据类型。
【例3.4】 删除在例3.1中定义的数据类型telephone_code。
DROP TYPE telephone_code
或
sp_droptype telephone_code
注意 只能删除已经定义,但未被使用的用户自定义数据类型,不能删除正在被表或其他数据库对象使用的用户自定义数据类型。