8.3.1 SQL Server的数据类型

数据是信息的数字表现形式，信息的加工和处理是以大量结构化的数据为载体进行的。数据库管理系统的核心是数据库，数据库的主要对象是表，表是结构化数据存储的地方。现实世界是一个多样化的世界，现实世界的信息也是多种多样的。为了准确地表示信息的类型，给代表信息的数据赋予了不同的类型属性。例如，为了表示时间和日期信息，可以使用date time数据类型；为了表示某一个人的姓名，可以使用char或varchar数据类型；为了表示数值信息，可以使用int、real、numeric等数据类型。

在SQL Server系统中，有两种数据类型，一种是系统提供的数据类型，另外一种是用户基于系统数据类型自定义的用户数据类型。

1. 系统数据类型

数据类型是数据的一种属性，表示数据所代表信息的类型。在SQL Server 2012系统中，提供了7种类别的数据类型，即精确数字数据类型、近似数字数据类型、日期和时间数据类型、字符串数据类型、unicode字符串数据类型、二进制字符串数据类型、其他数据类型。

下面简单描述系统提供的数据类型的特点和作用。

1）精确数字数据类型

精确数字数据类型包含bigint、int、smallint、tinyint、numeric、decimal、money、smallmoney和bit。

整数由正整数和负整数组成，如39、0、－2和33967。在SQL Server中，整数存储的数据类型是bigint、int、smallint和tinyint。bigint是一个8字节的整数类型，可以存储非常大的整数数据。使用 int 数据类型时，存储数据的范围是从－2147483648 到2147483647，占4个字节的存储空间。使用smallint数据类型时，存储数据的范围是从－32768到32767，占2个字节。使用tinyint数据类型时，存储数据的范围是从0到255，占1个字节。

精确小数数据类型是decimal[(p[,s])]和numeric[(p[,s])]。p（精度）表示最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是从1到最大精度38之间的值，默认精度为18。s（小数位数）表示小数点右边可以存储的十进制数字的最大位数。小数位数必须是从 0到p之间的值，仅在指定精度后才可以指定小数位数，默认的小数位数为 0。这种数据所占的存储空间根据该数据的位数和小数点后的位数来确定。这两种数据类型在功能上等价。

货币数据的数据类型是money和smallmoney，用于表示与货币有关的数据。实际上，这种数据类型也是一种小数，但是在这种类型的数据中，小数点后的数据有4位，并且会自动地进行四舍五入。money占8个字节，smallmoney占4个字节。

bit可以取值为1、0或NULL的integer数据类型。字符串值TRUE和FALSE可转换为bit值：TRUE将转换为1，FALSE将转换为0。转换为bit会将任何非零值升为1。

2）近似数字数据类型

用于存储近似小数数据的数据类型是float和real。使用这种数据类型可以用科学计数法的形式表示数据。float和real的区别在于：float数据类型可以表示的数据范围远大于real数据类型表示的数据范围。real占4个字节，float（n）所占字节数取决于n的大小。

3）日期和时间数据类型

日期和时间数据类型包括 date、datetime2、datetime、datetimeoffset、smalldatetime、time。使用datetime数据类型时，所存储的日期范围是从1753年1月1日开始，到9999年12月31日结束，且精确到3.33毫秒。使用smalldatetime数据类型时，所存储的日期范围是从1900年1月1日开始，到2079年12月31日结束，且只精确到分钟。

datetime2数据类型是datetime数据类型的扩展，有更广的日期范围。时间总是用时、分钟、秒形式来存储。可以定义末尾带有可变参数的datetime2数据类型，如datetime2(3)，这个表达式中的3表示存储时秒的小数精度为3位或0.999。有效值在0~9之间，默认值为3。

datetimeoffset数据类型和datetime2数据类型一样，带有时区偏移量。该时区偏移量最大为＋/－14小时，包含UTC偏移量，因此可以合理化不同时区捕捉的时间。

date数据类型只存储日期，这是一直需要的一项功能。而time数据类型只存储时间，它也支持time(n)声明，因此可以控制小数秒的粒度。与datetime2和datetimeoffset一样，n的范围在0~7之间。

4）字符串数据类型

字符串数据类型包括char[(n)]、varchar[(n)]和text。字符串数据是由任意字母、符号和数字组合而成的数据。char是定长字符数据，其长度最长为8KB。超过8KB的数据可以使用text数据类型存储。varchar是变长度的数据类型，其长度最长为8KB。如果有一表列名为First Name且数据类型为varchar(20)，同时将值“John”存储到该列中，则物理上只存储4个字节。但如果在数据类型为char(20)的列中存储相同的值，则使用全部20个字节。

5）unicode字符串数据类型

unicode字符串数据类型也称为国际数据类型，包括nchar、nvarchar和ntext，可以用于存储世界上所有的字符。使用unicode数据类型，所占用的空间是非unicode数据类型所占用空间大小的两倍。当列的长度变化时，应该使用nvarchar字符类型，这时最多可以存储4000个字符。当列的长度固定不变时，应该使用nchar字符类型，同样，这时最多可以存储4000个字符。当使用ntext数据类型时，该列可以存储多于4000个字符的数据。

6）二进制字符串数据类型

二进制字符串数据类型包括binary、varbinary和image。二进制字符串既可以是固定长度的，也可以是变长度的。binary[(n)]是n位固定长度的二进制数据。其中，n的取值范围为1~8000。varbinary[(n)]是n位变长的二进制数据。其中，n的取值范围也为1~8000。在image数据类型中存储的数据是以位字符串存储的，可以把BMP、TIFF、GIF和JPEG等格式的数据存储在image数据类型中。

7）其他数据类型

其他数据类型包括前面没有提到过的数据类型。特殊的数据类型有7种，即cursor、hierarchyid、sql_variant、table、timestamp、uniqueidentifier、xml。

cursor是变量或存储过程OUTPUT参数的一种数据类型，这些参数包含对游标的引用。

hierarchyid数据类型是一种长度可变的系统数据类型。可使用hierarchyid表示层次结构中的位置。

sql_variant是一种允许存储不同数据类型的数据值的数据类型。当对将要存储的数据类型不能确认时，应该选用这种数据类型。

table数据类型主要在定义函数时使用。如果该函数的返回结果是一种结果集，那么可以把这种函数定义为table数据类型的函数。

timestamp 数据类型用于表示 SQL Server 活动的先后顺序，以二进制的格式表示。timestamp数据与插入数据或修改数据的日期和时间没有关系。

uniqueidentifier数据类型由16字节的十六进制数字组成，表示一个全局唯一的标识号（Global Uniqueldentifier，GUID）。当表中的记录要求唯一时，GUID是非常有用的。

xml用于存储XML数据的数据类型。可以在列中或xml类型的变量中存储xml实例。

2. 用户定义的数据类型

用户定义的数据类型是基于SQL Server提供的系统数据类型而创建的数据类型。当在几个表中必须存储同一种数据类型，且为了保证这些列有相同的数据类型、长度和空值性时，那么最好使用用户定义的数据类型。

当创建用户定义的数据类型时，必须提供3个参数：数据类型的名称、所基于的系统数据类型和数据类型的可控性。

创建用户定义的数据类型可以使用系统存储过程sp_addtype语句完成，其语法形式如下：

sp_addtype type，system_data_type，null_type

其中，type是用户定义的数据类型的名称；system_data_type是系统提供的数据类型，如decimal、int、char等；null_type表示该数据类型是如何处理空值的，必须使用单引号引起来，如'NULL'、'NOT NULL'。

【例8-4】创建用户定义的数据类型，并编写程序。

解：程序如下。

USE students

EXEC sp_addtype ssn,'varchar(15)','NOT NULL'

EXEC sp_addtype birthday,datetime,'NULL'

EXEC sp_addtype telephone,'varchar(24)','NOT NULL'

EXEC sp_addtype fax,'varchar(24)','NULL'

在例8-4中，在students数据库中创建了4个用户定义的数据类型。用户定义的数据类型 ssn，它基于的系统数据类型是变长为 15的字符，不允许空。用户定义的数据类型birthday，它基于的系统数据类型是datetime，允许空。用户定义的数据类型telephone和fax，这两种数据类型的基础类型是变长字符类型。

当不需要用户定义的数据类型时，可以删除它们。删除用户定义的数据类型的语句是sp_droptype。

【例8-5】删除用户定义的数据类型birthday，并编写程序。

解：程序如下。

USE students

EXEC sp_droptype birthday

注意：当用户定义的数据类型正在被某个表的定义引用时，这些数据类型不能被删除。