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

 

数据类型类别

 

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

• 数字类型
  • 1.精确数字
  • 2.近似数字
• 3.日期和时间
• 字符串类型
  • 4.非Unicode字符串
  • 4.Unicode字符串
  • 5.二进制字符串
• 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语言中的类结构。