SQL Server数据库之数据类型

一、数据存储单位

1.位（bit）

数据存储的最小单位。每个二进制数字0或者1就是个位。

2.字节（B、byte）

• 1 byte（字节） = 8 bit（位） 8个位构成一个字节
• 1 KB = 1024 B (2^10B)
• 1 MB = 1024 KB (2^20B)
• 1 GB = 1024 MB (2^30B)
• 1 TB = 1024 GB (2^40B)

3.字符

单个字母、汉字、特殊符号（a、A、中、+、*、の…）均表示一个字符。
一般 utf-8 编码下，一个汉字字符占用 3 个字节；
一般 gbk 编码下，一个汉字字符占用 2 个 字节。

一、系统数据类型

（一）精确数字

1.整数

数据类型	范围	大小
bigint	从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）	8bit
int	从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）	4bit
smallint	从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整数数据	2bit
tinyint	从 0 到 255 的整数数据	1bit
bit	整型数据 1、0 或 NULL	1bit

2.小数

• 精度：数中的数字个数。123.45的精度是5
• 小数位数：数中小数点右边的数字个数。123.45小数位数是2
• 数字数据类型的长度：存储此数据类型所占用的字节数

decimal[(p[, s])] 和 numeric[(p[, s])]

使用最大精度时，有效值从 - 10^38 +1 到 10^38 - 1。

1° p（精度）

指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是从 1 到最大精度之间的值。最大精度为 38。

2° s（小数位数）

指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 p 之间的值。默认小数位数是 0，因而 0 <= s <= p。最大存储大小基于精度而变化。

精度	存储字节数
1 - 9	5
10 - 19	9
20 - 28	13
29 - 38	17

3.货币

数据类型	范围	大小
money	货币数据值介于 -2^63 (-922,337,203,685,477.5808) 与 2^63 - 1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十	8bit
smallmoney	货币数据值介于 -214,748.3648 与 +214.748,3647 之间，精确到货币单位的千分之十	4bit

（二）近似数字

用于表示浮点数字数据的近似数字数据类型。浮点数据为近似值；并非数据类型范围内的所有数据都能精确地表示。

1.float [ ( n ) ]

从 - 1.79E + 308 到 1.79E + 308 之间的浮点数字数据。n 为用于存储科学记数法 float 数尾数的位数，同时指示其精度和存储大小。n 必须为从 1 到 53 之间的值。

n所在范围	精度	存储大小
1 - 24	7位数	4字节
25 - 53	15位数	8字节

2.real

从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。存储大小为 4 字节。

（三）日期和时间数据

1.datetime

从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。
注：Microsoft SQL Server 用两个 4 字节的整数内部存储 datetime 数据类型的值。第一个 4 字节存储 base date（即 1900 年 1 月 1 日）之前或之后的天数。基础日期是系统参考日期。不允许早于 1753 年 1 月 1 日的 datetime 值。另外一个 4 字节存储以午夜后毫秒数所代表的每天的时间。

2.smalldatetime

从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据精确到分钟。

• 不高于29.998 秒的 smalldatetime 值向下舍入为最接近的分钟
• 超过29.999 秒的 smalldatetime 值向上舍入为最接近的分钟

--returns time as 12:35
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)
GO
--returns time as 12:36
SELECT CAST('2000-05-08 12:35:29.999' AS smalldatetime)
GO

注：smalldatetime 数据类型存储日期和每天的时间，但精确度低于 datetime。SQL Server 将 smalldatetime 的值存储为两个 2 字节的整数。第一个 2 字节存储 1900 年 1 月 1 日后的天数。另外一个 2 字节存储午夜后的分钟数。日期范围从1900 年 1 月 1 日到 2079 年 6 月 6 日，精确到分钟。

（四）二进制数据

1.binary [ ( n ) ]

固定长度的 n 个字节二进制数据。N 必须从 1 到 8,000。存储空间大小为 n+4 字节。

2.varbinary [ ( n ) ]

n 个字节可变二进制数据。n 必须从 1 到 8,000。存储空间大小为实际输入数据长度 +4 个字节，而不是 n 个字节。输入的数据长度可能为 0 字节。
注：如果在数据定义或变量声明语句中没有指定 n，默认长度为 1。如果没有用 CAST 函数指定 n，默认长度为 30。
当列数据项大小一致时应使用 binary。
当列数据项大小不一致时应使用 varbinary。

3.image

可变长度的二进制数据，其最大长度为 2^31 - 1 (2,147,483,647) 个字节。

（五）字符数据

1.char[(n)]

长度为 n 个字节的固定长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为 n 个字节。

2.varchar[(n)]

长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 可以是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n 个字节。所输入的数据字符长度可以为零。

3.text

可变长度的非 Unicode 数据，最大长度为 2^31 - 1 (2,147,483,647) 个字符。

（六）Unicode 数据

双字节

1.nchar(n)

固定长度的 Unicode 数据，最大长度为 4,000 个字符。

2.nvarchar(n)

可变长度 Unicode 数据，其最大长度为 4,000 字符。sysname 是系统提供用户定义的数据类型，在功能上等同于 nvarchar(128)，用于引用数据库对象名。

3.ntext

可变长度 Unicode 数据，其最大长度为 2^30 - 1 (1,073,741,823) 个字符。

二、用户自定义数据类型

用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。

（一）创建用户自定义数据类型

1. sp_addtype

创建用户定义的数据类型可以使用系统存储过程 sp_addtype

（1）语法

sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] ‘null_type’ ]
[ , [ @owner = ] ‘owner_name’ ]

（2）参数

1° [@typename =] type

用户定义的数据类型的名称。数据类型名称必须遵照标识符的规则，而且在每个数据库中必须是唯一的。type 的数据类型为 sysname，没有默认值。

2° [@phystype =] system_data_type

是用户定义的数据类型所基于的物理数据类型或 Microsoft® SQL Server™ 提供的数据类型（decimal、int 等等）。system_data_type 的数据类型是 sysname，没有默认值。

3° [@nulltype =] ‘null_type’

指明用户定义的数据类型处理空值的方式。null_type 的数据类型为 varchar(8)，默认值为 NULL，并且必须用单引号引起来（‘NULL’、‘NOT NULL’ 或 ‘NONULL’）。如果没有用 sp_addtype 显式定义 null_type，则将其设置为当前默认的为空性。使用 GETANSINULL 系统函数可确定当前默认的为空性，可以使用 SET 语句或 sp_dboption 对该为空性进行调整。应显式定义为空性。

4° [@owner =] ‘owner_name’

指定新数据类型的创建者或所有者。owner_name 的数据类型为 sysname。当没有指定时，owner_name 为当前用户。

（3）示例

例：为数据库teachingDataA创建一个用户定义的数据类型Sname，该数据类型为基于系统数据类型变长为8的字符，且不允许为空。

use teachingDataA
Exec sp_addtype Sname, 'Varchar(8)','Not Null'

注：varchar(8) 由单引号引了起来，这是因为它包含了标点符号（圆括号）。

2.CREATE TYPE

在SQL-Server 2005之后的后续版本中，sp_addtype将不再可用，取而代之的是CREATE TYPE语句。

（1）语法

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

（2）参数

typename：用户自定义的数据类型的名称。
base_type：表示建立的数据类型所基于的由SQL Server提供的数据类型。当建立decimal或numeric类型时，需要用precision来指定总位数，用scale来指定小数位数。
NULL | NOT NULL：指定此类型是否可容纳空值。如果未指定，则默认为NULL。

（3）示例

例：为数据库teachingDataB自定义一个工作部门myDept的数据类型为varchar(20)，允许为空。

CREATE TYPE myDept FROM varchar(20) 

3.界面设置

在Microsoft SQL Server Management Studio的对象资料管理器中展开【可编程性】|【类型】，右击【用户定义数据类型】，选择【新建用户定义数据类型】命令，打开【新建用户定义数据类型】，输入自定义的数据类型名，选择数据类型，设置长度设定，确定是否允许空值（通过勾选【允许空值】进行设置），完成后单击【确定】。

（二）删除用户自定义数据类型

1.sp_droptype

当用户定义的数据类型不需要时，可以用命令sp_droptype将其删除。

exec sp_droptype {’type_name’} 

2.界面设置

在Microsoft SQL Server Management Studio的对象资料管理器中展开【可编程性】|【类型】|【用户定义数据类型】，右击要删除的数据类型，选择“删除”。

三、数据类型的转换

（一）数据类型的优先顺序

当两个不同数据类型的表达式用运算符组合后，数据类型的优先顺序规则确定哪种数据类型要向另一种转换。优先顺序低的数据类型向优先顺序高的数据类型转换。如果此转换不是所支持的固有转换，则返回错误。当两个操作数表达式有相同的数据类型时，运算的结果就为那种数据类型。

sql_variant > datetime > smalldatetime > float > real > decimal > money > smallmoney > bigint > int > smallint > tinyint > bit > ntext > text > image > timestamp > uniqueidentifier > nvarchar > nchar > varchar > char > varbinary > binary

（二）排序规则的优先顺序

排序规则的优先顺序规则只适用于字符串数据类型：char、varchar、text、nchar、nvarchar 和 ntext。

操作数强制标志	显性X	隐性X	强制默认	无排序规则
显性Y	生成错误	结果为显性Y	结果为显性Y	结果为显性Y
隐性Y	结果为显性X	结果为无排序规则	结果为隐性Y	结果为无排序规则
强制默认	结果为显性X	结果为隐性X	结果为强制默认	结果为无排序规则
无排序规则	结果为显性X	结果为无排序规则	结果为无排序规则	结果为无排序规则