SQL SERVER 数据类型详解（SQL Server 2008）

数据类型类别

SQL Server 中的数据类型归纳为下列类别：

数字类型
- 1.精确数字
- 2.近似数字
3.日期和时间
字符串类型
6.其他数据类型
7.CLR

在 SQL Server 中，根据其存储特征，某些数据类型被指定为属于下列各组：

大值数据类型：varchar(max)、nvarchar(max) 和 varbinary(max)
大型对象数据类型：text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml

1.精确数字

如decimal和numeric等数值数据类型可存储小数点右边或左边的变长位数。Scale是小数点右边的位数。精度(Precision)定义了总位数，包括小数点右边的位数。例如，由于14.88531可为numeric(7,5)或decimal(7,5)。如果将14.25插入到numeric(5,1)列中，它将被舍入为14.3。

数据类型

描述

存储空间

注释

bit

0、1或Null的整数数据类型

1字节(8位)

SQL Server 数据库引擎可优化 bit 列的存储。如果表中的列为 8 bit 或更少，则这些列作为 1 个字节存储。如果列为 9 到 16 bit，则这些列作为 2 个字节存储，以此类推。

字符串值 TRUE 和 FALSE 可以转换为以下 bit 值：TRUE 转换为 1，FALSE 转换为 0。

bigint

-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)

8 字节

int 数据类型是 SQL Server 中的主要整数数据类型。bigint 数据类型用于整数值可能超过 int 数据类型支持范围的情况。

在数据类型优先次序表中，bigint 介于 smallmoney 和 int 之间。

只有当参数表达式为 bigint 数据类型时，函数才返回 bigint。SQL Server 不会自动将其他整数数据类型（tinyint、smallint 和 int）提升为 bigint。

int

-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)

4字节

smallint

-2^15 (-32,768) 到 2^15-1 (32,767)

2字节

tinyint

0 到 255

1字节

decimal[ (p[ ,s] )]

固定精度和小数位数。使用最大精度时，有效值从 - 10^38 +1 到 10^38 - 1。decimal 的 ISO 同义词为 dec 和 dec(p, s)。numeric 在功能上等价于 decimal。p（精度）

最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是从 1 到最大精度 38 之间的值。默认精度为 18。

s（小数位数）

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

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

numeric[ (p[ ,s] )]

money

-922,337,203,685,477.5808 到 922,337,203,685,477.5807

8字节

money 和 smallmoney 数据类型精确到它们所代表的货币单位的万分之一。

smallmoney

-214,748.3648 到 214,748.3647

4字节

2.近似数字

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

这个分类中包括数据类型float和real。它们用于表示浮点数据。但是，由于它们是近似的，因此不能精确地表示所有值。

float(n)中的n是用于存储该数尾数(mantissa)的位数。SQL Server对此只使用两个值。如果指定位于1~24之间，SQL就使用24。如果指定25~53之间，SQL就使用53。当指定float()时(括号中为空)，默认为53。

数据类型

描述

存储空间

注释

float [ (n) ]

其中 n 为用于存储 float 数值尾数的位数（以科学记数法表示），因此可以确定精度和存储大小。如果指定了 n，则它必须是介于 1 和 53 之间的某个值。n 的默认值为 53。

-1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308

取决于 n 的值

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

SQL Server 将 n 视为下列两个可能值之一。如果 1<=n<=24，则将 n 视为 24。如果 25<=n<=53，则将 n 视为 53。
SQL Server float[(n)]
数据类型从 1 到 53 之间的所有 n 值均符合
ISO 标准。double precision 的同义词为 float(53)。

real

-3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38

4 字节

real 的
ISO 同义词为 float(24)。

3.日期和时间类型

对于新的工作，请使用 time、date、datetime2 和 datetimeoffset 数据类型。这些类型符合
SQL 标准。它们更易于移植。time、datetime2 和 datetimeoffset 提供更高精度的秒数。datetimeoffset 为全局部署的应用程序提供时区支持。

datetime和smalldatetime数据类型用于存储日期和时间数据。smalldatetime为4字节，存储1900年1月1日~2079年6月6日之间的时间，且只精确到最近的分钟。datetime数据类型为8字节，存储1753年1月1日~9999年12月31日之间的时间，且精确到最近的3.33毫秒。

SQL Server 2008有4种与日期相关的新数据类型：datetime2、dateoffset、date和time。通过SQL Server联机丛书可找到使用这些数据类型的示例。

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之间。

数据类型	描述	存储空间	注释
time	00:00:00.0000000 到 23:59:59.9999999 精确度：100纳秒字符长度：最小 8 位 (hh:mm:ss)，最大 16 位 (hh:mm:ss.nnnnnnn)	3~5字节
date	0001-01-01 到 9999-12-31 字符长度：10 位	固定3个字节 1、3 字节整数存储日期。
smalldatetime	日期范围：1900-01-01 到 2079-06-06 时间范围：00:00:00 到 23:59:59 精确度：1分钟字符长度：最高 19 位	4字节	2007-5-9 23:59:59 将被舍为 2007-5-10 00:00:00
datetime	日期范围：1753-1-1 到 9999-12-31 时间范围：00:00:00 -23:59:59.997 精确度：0.00333 秒字符长度：最低 19 位到最高 23 位	8字节
datetime2	0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 精确度：100纳秒	6~8字节
datetimeoffset	日期范围：0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 时间表示）时间范围：00:00:00 到 23:59:59.9999999 精确度：100纳秒字符长度：最低 26 位 (YYYY-MM-DD hh:mm:ss {+\|-}hh:mm) 到最高 34 位 (YYYY-MM-DD hh:mm:ss.nnnnnnn {+\|-}hh:mm)	8~10字节默认值为 10 个字节的固定大小，默认的秒的小数部分精度为 100ns。

4. 字符数据类型

字符数据类型包括varchar、char、nvarchar、nchar、text以及ntext。这些数据类型用于存储字符数据。varchar和char类型的主要区别是数据填充。如果有一表列名为FirstName且数据类型为varchar(20)，同时将值Brian存储到该列中，则物理上只存储5个字节。但如果在数据类型为char(20)的列中存储相同的值，将使用全部20个字节。SQL将插入拖尾空格来填满20个字符。

如果要节省空间，那么为什么还使用char数据类型呢？使用varchar数据类型会稍增加一些系统开销。例如，如果要存储两字母形式的州名缩写，则最好使用char(2)列。尽管有些DBA认为应最大可能地节省空间，但一般来说，好的做法是在组织中找到一个合适的阈值，并指定低于该值的采用char数据类型，反之则采用varchar数据类型。通常的原则是，任何小于或等于5个字节的列应存储为char数据类型，而不是varchar数据类型。如果超过这个长度，使用varchar数据类型的好处将超过其额外开销。

nvarchar数据类型和nchar数据类型的工作方式与对等的varchar数据类型和char数据类型相同，但这两种数据类型可以处理国际性的Unicode字符。它们需要一些额外开销。以Unicode形式存储的数据为一个字符占两个字节。如果要将值Brian存储到nvarchar列，它将使用10个字节；而如果将它存储为nchar(20)，则需要使用40字节。由于这些额外开销和增加的空间，应该避免使用Unicode列，除非确实有需要使用它们的业务或语言需求。

接下来要提的数据类型是text和ntext。text数据类型用于在数据页内外存储大型字符数据。应尽可能少地使用这两种数据类型，因为可能影响性能但可在单行的列中存储多达2GB的数据。与text数据类型相比，更好的选择是使用varchar(max)类型，因为将获得更好的性能。另外，text和ntext数据类型在SQL Server的一些未来版本中将不可用，因此现在开始还是最好使用varchar(max)和nvarchar(max)而不是text和ntext数据类型。

数据类型	描述	存储空间	注释
Char(n)	固定长度， n取值：1~8000 char的ISO同义词：character	n字节	如果没有在数据定义或变量声明语句中指定 n，则默认长度为 1。如果在使用 CAST 和 CONVERT 函数时未指定 n，则默认长度为 30。将为使用 char 或 varchar 的对象指派数据库的默认排序规则，除非使用 COLLATE 子句指派了特定的排序规则。该排序规则控制用于存储字符数据的代码页。如果站点支持多语言，请考虑使用 Unicode nchar 或 nvarchar 数据类型，以最大限度地消除字符转换问题。如果使用 char 或varchar，建议执行以下操作：如果列数据项的大小一致，则使用 char。如果列数据项的大小差异相当大，则使用 varchar。如果列数据项大小相差很大，而且大小可能超过 8,000 字节，请使用 varchar(max)。当执行 CREATE TABLE 或 ALTER TABLE 时，如果 SET ANSI_PADDING 为 OFF，则定义为 NULL 的 char 列将作为 varchar 处理。
Varchar(n\|max)	可变长度， n取值：1~8000 max最大存储大小是：2^31-1 个字节 (2 GB) varchar的ISO同义词：char varying或character varying	每字符1字节＋2 字节额外开销
text	服务器代码页中长度可变的非Unicode数据，最大长度： 2^31-1 (2,147,483,647) 个字节	当服务器代码页使用双字节字符时，存储仍是 2,147,483,647 字节。根据字符串，存储大小可能小于 2,147,483,647 字节。
nchar(n)	固定长度，Unicode字符串数据， n取值：1~4000 char的ISO同义词：national char或national character	2n(每字符2字节) n(排序规则代码页使用双字节字符时)	如果没有在数据定义或变量声明语句中指定 n，则默认长度为 1。如果没有使用 CAST 函数指定 n，则默认长度为 30。如果列数据项的大小可能相同，请使用 nchar。如果列数据项的大小可能差异很大，请使用 nvarchar。 sysname 是系统提供的用户定义数据类型，除了不可为空值外，在功能上与 nvarchar(128) 相同。sysname 用于引用数据库对象名。为使用 nchar 或 nvarchar 的对象分配的是默认的数据库排序规则，但可使用 COLLATE 子句分配特定的排序规则。 SET ANSI_PADDING ON 永远适用于 nchar 和 nvarchar。SET ANSI_PADDING OFF 不适用于 nchar 或 nvarchar 数据类型。
nvarchar(n\|max)	可变长度，Unicode字符串数据， n取值：1~4000 max最大存储为大小是：2^31-1 个字节 (2 GB) nvarchar的ISO同义词：national char varying、national character varying	2n(每字符2字节)+2字节
ntext	长度可变的 Unicode 数据，字符串最大长度为 2^30 - 1 (1,073,741,823) 个字节。 ntext 的 ISO 同义词为 national text。	存储大小是所输入字符串长度的两倍（以字节为单位）

5.二级制类型

如varbinary、binary、varbinary(max)或image等二进制数据类型用于存储二进制数据，如图形文件、Word文档或MP3文件。其值为十六进制的0x0~0xf。image数据类型可在数据页外部存储最多2GB的文件。image数据类型的首选替代数据类型是varbinary(max)，可保存最多8KB的二进制数据，其性能通常比image数据类型好。SQL Server 2008的新功能是可以在操作系统文件中通过FileStream存储选项存储varbinary(max)对象。这个选项将数据存储为文件，同时不受varbinary(max)的2GB大小的限制。

数据类型	描述	存储空间	注释
binary(n)	固定长度， n取值：1~8000 char的ISO同义词：character	n字节	如果没有在数据定义或变量声明语句中指定 n，则默认长度为 1。如果没有使用 CAST 函数指定 n，则默认长度为 30。如果列数据项的大小一致，则使用 binary。如果列数据项的大小差异相当大，则使用 varbinary。当列数据条目超出 8,000 字节时，请使用 varbinary(max)。
varbinary(n\|max)	可变长度， n取值：1~8000 max最大存储大小是：2^31-1 个字节 (2 GB) varbinary 的 ANSI SQL 同义词为 binary varying。	存储大小为所输入数据的实际长度 + 2 个字节。所输入数据的长度可以是 0 字节
image	长度可变的二进制数据，从 0 到 2^31-1 (2,147,483,647) 个字节。

6. 其他系统数据类型

数据类型	描述	存储空间	注释
Cursor	包含一个对光标的引用和可以只用作变量或存储过程参数	不适用
Hierarchyid	包含一个对层次结构中位置的引用	1~892字节＋2 字节的额外开销
SQL_Variant	可能包含任何系统数据类型的值，除了text、ntext、 image、timestamp、xml、 varchar(max)、nvarchar(max)、 varbinary (max)、sql_variant以及用户定义的数据类型。最大尺寸为8000字节数据＋16字节 (或元数据)	8016字节
Table	用于存储用于进一步处理的数据集。定义类似于Create Table。主要用于返回表值函数的结果集，它们也可用于存储过程和批处理中	取决于表定义和存储的行数
Timestamp or Rowversion	对于每个表来说是唯一的、自动存储的值。通常用于版本戳，该值在插入和每次更新时自动改变	8字节
Uniqueidentifier	可以包含全局唯一标识符 (Globally Unique Identifier， GUID)。guid值可以从Newid() 函数获得。这个函数返回的值对所有计算机来说是唯一的。尽管存储为16位的二进制值，但它显示为char(36)	16字节
XML	可以以Unicode或非Unicode形式存储	最多2GB

注意：

cursor数据类型可能不用于Create Table语句中。

hierarchyid列是SQL Server 2008中新出现的。您可能希望将这种数据类型的列添加到这样的表中--其表行中的数据可用层次结构表示，就像组织层次结构或经理/雇员层次结构一样。存储在该列中的值是行在层次结构中的路径。层次结构中的级别显示为斜杠。斜杠间的值是这个成员在行中的数字级别，如/1/3。可以运用一些与这种数据类型一起使用的特殊函数。

XML数据存储XML文档或片段。根据文档中使用UTF-16或是UTF-8，它在尺寸上像text或ntext一样存储。XML数据类型使用特殊构造体进行搜索和索引。第15章将更详细地介绍这些内容。

7. CLR集成

在SQL Server 2008中，还可使用公共语言运行库(Common Language Runtime，CLR)创建自己的数据类型和存储过程。这让用户可以使用Visual Basic或C#编写更复杂的数据类型，以满足业务需求。这些类型被定义为基本的CLR语言中的类结构。

巴特西