SQLServer之数据类型解析

作者: 痴女一枚眉清目秀
来源: 51数据库
2022-04-21

数字

int、bigint、smallint 和 tinyint

使用整数数据的精确数字数据类型。

数据类型	范围	存储
tinyint	0 到 255。	1 字节
smallint	-2^15 (-32,768) 到 2^15-1 (32,767)。	2 字节
int	-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)。	4 字节
bigint	-2^63 (-9,223,372,036,854,775,808) 到 2^63-1。 (9,223,372,036,854,775,807)	8 字节

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

带固定精度和小数位数的数值数据类型。 decimal 和 numeric 是同义词，可互换使用。

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

p（精度）

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

s （小数位数）

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

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

money 和 smallmoney

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

数据类型	范围	存储
money	-922,337,203,685,477.5808 到 922,337,203,685,477.5807。	8 字节
smallmoney	-214,748.3648 到 214,748.3647。	4 字节

bit

可以取值为 1、0 或 null 的 integer 数据类型。

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

字符串值 true 和 false 可转换为 bit 值：true 将转换为 1，false 将转换为 0。

转换为 bit 会将任何非零值升为 1。

float 和 real

用于表示浮点数值数据的大致数值数据类型。浮点数据为近似值；因此，并非数据类型范围内的所有值都能精确地表示。 real 的 iso 同义词为 float(24)。

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

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

数据类型	范围	存储
float	-1.79e + 308 至 -2.23e - 308、0 以及 2.23e - 308 至 1.79e + 308	取决于 n 的值
real	-3.40e + 38 至 -1.18e - 38、0 以及 1.18e - 38 至 3.40e + 38	4 字节

日期时间

类型

默认的字符串文字格式

日期范围

时间范围

时区偏移量范围

各元素的范围

字符长度

存储大小

精确度

默认值

日历

用户定义的秒的小数部分精度

时区偏移量感知和保留

夏时制感知

date

yyyy-mm-dd

0001-01-01 到 9999-12-31（对于 informatica，为 1582-10-15 到 9999-12-31）

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日（对于 informatica，为公元 1582 年 10 月 15 日到公元 9999 年 12 月 31 日）

yyyy 是表示年份的四位数字，范围为从 0001 到 9999。对于 informatica，yyyy 限为 1582 年到 9999 年。

mm 是表示指定年份中的月份的两位数字，范围为从 01 到 12。

dd 是表示指定月份中的某一天的两位数字，范围为从 01 到 31（最高值取决于具体月份）。

10 位

固定 3 个字节

一天

1900-01-01

此值用于从 time 隐式转换到 datetime2 或 datetimeoffset 时追加的日期部分。

公历

否

datetime

默认的字符串文字格式不适用

1753 年 1 月 1 日到 9999 年 12 月 31 日

00:00:00 到 23:59:59.997

none

yyyy 是表示年份的四位数字，范围为 1753 到 9999。

mm 是表示指定年份中的月份的两位数字，范围为 01 到 12。

dd 是表示指定月份中的某一天的两位数字，范围为 01 到 31（最高值取决于相应月份）。

hh 是表示小时的两位数字，范围为 00 到 23。

mm 是表示分钟的两位数字，范围为 00 到 59。

ss 是表示秒钟的两位数字，范围为 00 到 59。

n* 为一个 0 到 3 位的数字，范围为 0 到 999，表示秒的小数部分。

最低 19 位到最高 23 位

8 字节

舍入到 .000、.003 或 .007 秒三个增量。

1900-01-01 00:00:00

公历（不包括完整的年份范围。）

否

datetime2 [ (fractional seconds precision) ]

yyyy-mm-dd hh:mm:ss[.fractional seconds]

0001-01-01 到 31.12.99

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日

00:00:00 到 23:59:59.9999999

none

yyyy 是一个四位数，范围从 0001 到 9999，表示年份。

mm 是一个两位数，范围从 01 到 12，它表示指定年份中的月份。

dd 是一个两位数，范围为 01 到 31（具体取决于月份），它表示指定月份中的某一天。

hh 是一个两位数，范围从 00 到 23，它表示小时。

mm 是一个两位数，范围从 00 到 59，它表示分钟。

ss 是一个两位数，范围从 00 到 59，它表示秒钟。

n* 代表 0 到 7 位数字，范围从 0 到 9999999，它表示秒小数部分。在 informatica 中，当 n > 3 时，秒的小数部分会被截断。

最低 19 位 (yyyy-mm-dd hh:mm:ss )，最高 27 位 (yyyy-mm-dd hh:mm:ss.0000000)

精度小于 3 时为 6 个字节；精度为 3 和 4 时为 7 个字节。所有其他精度则需要 8 个字节。

100 纳秒

1900-01-01 00:00:00

公历

用户帐户控制

否

datetimeoffset [ (fractional seconds precision) ]

yyyy-mm-dd hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

0001-01-01 到 31.12.99

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日

00:00:00 至 23:59:59.9999999（informatica 不支持秒的小数部分）

-14:00 到 +14:00（informatica 中忽略时区偏移量）

yyyy 是表示年份的四位数字，范围为 0001 到 9999。

mm 是表示指定年份中的月份的两位数字，范围为 01 到 12。

dd 是表示指定月份中的某一天的两位数字，范围为 01 到 31（最高值取决于相应月份）。

hh 是表示小时的两位数字，范围为 00 到 23。

mm 是表示分钟的两位数字，范围为 00 到 59。

ss 是表示秒钟的两位数字，范围为 00 到 59。

n* 是 0 到 7 位数字，范围为 0 到 9999999，它表示秒的小数部分。 informatica 不支持秒的小数部分。

hh 是两位数，范围为 -14 到 +14。 informatica 忽略时区偏移量。

mm 是两位数，范围为 00 到 59。 informatica 忽略时区偏移量。

最低 26 位 (yyyy-mm-dd hh:mm:ss {+

默认值为 10 个字节的固定大小，默认的秒的小数部分精度为 100ns。

100 纳秒

1900-01-01 00:00:00 00:00

公历

用户帐户控制

否

smalldatetime

不适用

1900-01-01 到 2079-06-06

1900 年 1 月 1 日到 2079 年 6 月 6 日

00:00:00 到 23:59:59

2007-05-09 23:59:59 将舍入为

2007-05-10 00:00:00

yyyy 是表示年份的四位数字，范围为 1900 到 2079。

mm 是表示指定年份中的月份的两位数字，范围为 01 到 12。

dd 是表示指定月份中的某一天的两位数字，范围为 01 到 31（最高值取决于相应月份）。

hh 是表示小时的两位数字，范围为 00 到 23。

mm 是表示分钟的两位数字，范围为 00 到 59。

ss 是表示秒钟的两位数字，范围为 00 到 59。小于或等于 29.998 秒的值向下舍入为最接近的分钟数；大于或等于 29.999 秒的值向上舍入为最接近的分钟数。

最高 19 位

固定 4 个字节

一分钟

1900-01-01 00:00:00

公历

（不包括完整的年份范围。）

否

time [ (fractional second scale) ]

对于 informatica，为 hh:mm:ss[.nnnnnnn]）

00:00:00.0000000 到 23:59:59.9999999（对于 informatica，为 00:00:00.000 到 23:59:59.999）

hh 是表示小时的两位数字，范围为 0 到 23。

mm 是表示分钟的两位数字，范围为 0 到 59。

ss 是表示秒的两位数字，范围为 0 到 59。

n* 是 0 到 7 位数字，范围为 0 到 9999999，它表示秒的小数部分。对于 informatica，n* 是零到三位数字，范围为 0 到 999。

最小 8 位 (hh:mm:ss)，最大 16 位 (hh:mm:ss.nnnnnnn)。对于 informatica，最大值为 12 位 (hh:mm:ss.nnn)。

固定 5 个字节，是使用默认的 100ns 秒的小数部分精度时的默认存储大小。在 informatica 中，默认为 4 个字节，固定不变，同时秒的小数部分精度默认为 1 毫秒。

100 纳秒（informatica 中为 1 毫秒）

00:00:00

此值用作从 date 隐式转换到datetime2 或 datetimeoffset 时追加的时间部分。

用户帐户控制

否

cursor

有些操作可以引用那些具有 cursor 数据类型的变量和参数，这些操作包括：

declare @local_variable 和 set @local_variable 语句。
open、fetch、close 及 deallocate 游标语句。
存储过程输出参数。
cursor_status 函数。
sp_cursor_list、sp_describe_cursor、sp_describe_cursor_tables 以及 sp_describe_cursor_columns 系统存储过程。

sp_cursor_list 和 sp_describe_cursor 的 cursor_name 输出列返回游标变量的名称。使用 cursor 数据类型创建的所有变量都可以为 null。对于 create table 语句中的列，不能使用 cursor 数据类型。

rowversion

公开数据库中自动生成的唯一二进制数字的数据类型。 rowversion 通常用作给表行加版本戳的机制。存储大小为 8 个字节。 rowversion 数据类型只是递增的数字，不保留日期或时间。若要记录日期或时间，请使用 datetime2 数据类型。

每个数据库都有一个计数器，当对数据库中包含 rowversion 列的表执行插入或更新操作时，该计数器值就会增加。此计数器是数据库行版本。这可以跟踪数据库内的相对时间，而不是时钟相关联的实际时间。一个表只能有一个 rowversion 列。每次修改或插入包含 rowversion 列的行时，就会在 rowversion 列中插入经过增量的数据库 rowversion 值。这一属性使 rowversion 列不适合作为键使用，尤其是不能作为主键使用。对行的任何更新都会更改行版本值，从而更改键值。如果该列属于主键，那么旧的键值将无效，进而引用该旧值的外键也将不再有效。如果该表在动态游标中引用，则所有更新均会更改游标中行的位置。如果该列属于索引键，则对数据行的所有更新还将导致索引更新。使用任何更新语句都会让 rowversion 值递增，即使没有任何行值发生更改。（例如，如果某列的值为 5，且更新语句将该值设置为 5，即使没有发生任何更改，此操作也被视为更新，并且 rowversion 发生递增。）

timestamp 的数据类型为 rowversion 数据类型的同义词，并具有数据类型同义词的行为。在 ddl 语句中，应尽量使用 rowversion，而不是 timestamp。

binary 和 varbinary

数据类型	定义	取值范围	储存大小	iso	何时使用...
binary [ ( n ) ]	长度为 n 字节的固定长度二进制数据。	n 是从 1 到 8,000 的值。	存储大小为 n 字节。		列数据项的大小一致。
varbinary [ ( n \| max) ]	可变长度二进制数据。	n 的取值范围为 1 至 8,000。 max 指示最大存储大小是 2^31-1 个字节。	存储大小为所输入数据的实际长度 + 2 个字节。所输入数据的长度可以是 0 字节。		[n]列数据项的大小差异相当大。 [max]列数据条目超出 8,000 字节。
char [ ( n ) ]	固定长度，非 unicode 字符串数据。	n 用于定义字符串长度，并且它必须为 1 到 8,000 之间的值。	存储大小为 n 字节。	char 的 iso 同义词是 character
varchar [ ( n \| max ) ]	可变长度，非 unicode 字符串数据。	n 用于定义字符串长度，并且它可以为 1 到 8,000 之间的值。 max 指示最大存储大小是 2^31-1 个字节 (2 gb)。	存储大小为所输入数据的实际长度 + 2 个字节。	varchar 的 iso 同义词是 charvarying 或 charactervarying
nchar [ ( n ) ]	固定长度的 unicode 字符串数据。	n 用于定义字符串长度，并且它必须为 1 到 4,000 之间的值。	存储大小为 n 字节的两倍。当排序规则代码页使用双字节字符时，存储大小仍然为 n 个字节。根据字符串的不同，n 个字节的存储大小可能小于为 n 指定的值。	nchar 的 iso 同义词是 national char 和 national character
nvarchar [ ( n \| max ) ]	可变长度的 unicode 字符串数据。	n 用于定义字符串长度，并且它可以为 1 到 4,000 之间的值。 max 指示最大存储大小是 2^30-1 个字符。	以字节为单位的最大存储大小为 2 gb。实际存储大小（以字节为单位）是所输入字符个数的两倍 + 2 个字节。	nvarchar 的 iso 同义词是 national char varying 和 national character varying。
ntext	长度可变的 unicode 数据，字符串最大长度为 2^30 - 1 (1,073,741,823) 个字节。		存储大小是所输入字符串长度的两倍（以字节为单位）。	ntext 的 iso 同义词为 national text。
text	服务器代码页中长度可变的非 unicode 数据，字符串最大长度为 2^31-1 (2,147,483,647) 个字节。		当服务器代码页使用双字节字符时，存储仍是 2,147,483,647 字节。根据字符串，存储大小可能小于 2,147,483,647 字节。
image	长度可变的二进制数据。		从 0 到 2^31-1 (2,147,483,647) 个字节。

空间类型 - geography

地理空间数据类型 geography 是作为 sql server 中的 .net 公共语言运行时 (clr) 数据类型实现的。此类型表示圆形地球坐标系中的数据。 sql server geography 数据类型存储诸如 gps 纬度和经度坐标之类的椭球体（圆形地球）数据。

sql server 支持 geography 空间数据类型的一组方法。这些方法包括开放地理空间信息联盟 (ogc) 标准和对该标准的一组 microsoft 扩展所定义的 geography 方法。

geography 方法的容错可高达 1.0e-7 * extents。 extents 表示 geography 对象的各点之间的近似最大距离。

geography 类型已进行预定义，可在每个数据库中使用。你可以创建 geography 类型的表列并对 geography 数据进行操作，就像使用其他系统提供的数据类型一样。可以用在持久化和非持久化计算列中。

sql_variant

sql_variant 可以用在列、参数、变量和用户定义函数的返回值中。借助 sql_variant，这些数据库对象可以支持其他数据类型的值。

类型为 sql_variant 的列可能包含不同数据类型的行。例如，定义为 sql_variant 的列可以存储 int、binary 和 char 类型的值。

sql_variant 的最大长度可以是 8016 个字节。这包括基类型信息和基类型值。实际基类型值的最大长度是 8,000 个字节。

对于 sql_variant 数据类型，必须先将它转换为其基本数据类型值，然后才能参与诸如加减这类运算。

可以为 sql_variant 分配默认值。该数据类型还可以将 null 作为其基础值，但是 null 值没有关联的基类型。此外，sql_variant 不能使用其他 sql_variant 作为其基础类型。

唯一键、主键或外键可能包含类型为 sql_variant 的列，但是，组成指定行的键的数据值的总长度不应大于索引的最大长度。该最大长度是 900 个字节。

一个表可以包含任意多个 sql_variant 列。

不能在 containstable 和 freetexttable 中使用 sql_variant。

odbc 不完全支持 sql_variant。因此，使用 microsoft ole db provider for odbc (msdasql) 时，sql_variant 列的查询将作为二进制数据返回。例如，包含字符串数据 'ps2091' 的 sql_variant 列将作为 0x505332303931 返回。

表

一种特殊的数据类型，可用于存储结果集以进行后续处理。 table 主要用于临时存储一组作为表值函数的结果集返回的行。可将函数和变量声明为 table 类型。 table 变量可用于函数、存储过程和批处理中。

uniqueidentifier

存储空间为16 字节 guid。

uniqueidentifier数据类型的列或局部变量可通过以下方式初始化为一个值：

通过使用 newid 或 newsequentialid 函数。
通过从 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 形式的字符串常量进行转换，其中，每个 x 都是 0-9 或 a-f 范围内的十六进制数字。例如，6f9619ff-8b86-d011-b42d-00c04fc964ff 为有效的 uniqueidentifier 值。

比较运算符可与 uniqueidentifier 值一起使用。不过，排序不是通过比较两个值的位模式来实现的。可针对 uniqueidentifier 值执行的运算只有比较运算（=、<>、<、>、<=、>=）以及检查是否为 null（is null 和 is not null）。不能使用其他算术运算符。除 identity 之外的所有列约束和属性均可对 uniqueidentifier 数据类型使用。

具有更新订阅的合并复制和事务复制使用 uniqueidentifier 列来确保在表的多个副本中唯一地标识行。

xml

存储 xml 数据的数据类型。可在列中或者 xml 类型的变量中存储 xml 实例。

存储的 xml 数据类型表示实例大小不能超过 2 gb。

content 和 document 方面仅应用于类型化的 xml.

content
将 xml 实例限制为格式正确的 xml 片段。 xml 数据的顶层可包含多个零或多个元素。还允许在顶层使用文本节点。这是默认行为。

document
将 xml 实例限制为格式正确的 xml 片段。 xml 数据必须且只能有一个根元素。不允许在顶层使用文本节点。

xml_schema_collection
xml 架构集合的名称。若要创建类型化的 xml 列或变量，可选择指定 xml 架构集合名称。