SQL Server 2012笔记分享-26：了解数据类型

在 SQL Server 中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。 数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。

SQL Server 提供系统数据类型集，该类型集定义了可与 SQL Server 一起使用的所有数据类型。 您还可以使用 Transact-SQL 或 Microsoft .NET Framework 定义自己的数据类型。 别名数据类型基于系统提供的数据类型。

数据类型分默认和自定义两种。

SQL 数据类型 由13个基本数据类型组成，它们是由 Microsoft Jet 数据库引擎和几个验证过的有效同义字定义的。常见的有：整形，单精度，双精度，可变长度字符，固定长度字符，长型，日期等等。

====================================================================

（一）精确数字

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

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

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

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

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

bit

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

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

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

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

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

用句点分隔局部货币单位（如美分）和总体货币单位。 例如，2.15 表示 2 美元 15 美分。 

这些数据类型可以使用下列任意一种货币符号。 

   

===========================================================================

   

（二）近似数字

float & real

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

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)。

使用科学记数法将 float 值转换为 decimal 或 numeric 被限制为仅限 17 位精度的值。 精度高于 17 的任何值都将舍入为零。

参考：http://msdn.microsoft.com/zh-cn/library/ms173773.aspx

   

===========================================================================

（三）日期和时间

date的用法如下表

[m]m、dd 和 [yy]yy 在字符串中表示月、日和年，使用斜线 (/)、连字符 (-) 或句点 (.) 作为分隔符。

仅支持四位或两位数字表示的年份。 请尽可能使用以四位数字表示的年份。 若要指定一个位于 0001 到 9999 之间的整数来表示用于将两位数年份解释为四位数年份的截止年份，请使用配置两位数年份截止服务器配置选项。

一个比截止年份的后两位数字小或者与其相等的两位数年份与该截止年份处于同一个世纪。 而一个比截止年份的后两位数字大的两位数年份所处的世纪为该截止年份所处世纪的上一个世纪。 例如，如果“两位数年份截止”为默认值 2049，则两位数年份 49 被解释为 2049 年，而两位数年份 50 被解释为 1950 年。

默认日期格式由当前语言设置决定。 可以通过使用 SET LANGUAGE 和 SET DATEFORMAT 语句更改日期格式。

date 不支持 ydm 格式。

mon 表示采用当前语言的完整月份名称或月份缩写。 逗号是可选的，且忽略大小写。

为避免不确定性，请使用四位数年份。  

如果没有指定日，则默认为当月第一天。 

详细介绍：http://msdn.microsoft.com/zh-cn/library/bb630352.aspx

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

datetime2：可将 datetime2 视作现有 datetime 类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。

datetimeoffset：用于定义一个与采用 24 小时制并可识别时区的一日内时间相组合的日期。

smalldatetime：定义结合了一天中的时间的日期。 此时间为 24 小时制，秒始终为零 (:00)，并且不带秒小数部分。

time：定义一天中的某个时间。 此时间不能感知时区且基于 24 小时制。

===================================================================================

（四）字符串

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 同义词为 char varying 或 character varying。

   

text   

服务器代码页中长度可变的非 Unicode 数据，字符串最大长度为 2^31-1 (2,147,483,647) 个字节。 当服务器代码页使用双字节字符时，存储仍是 2,147,483,647 字节。 根据字符串，存储大小可能小于 2,147,483,647 字节。

在 Microsoft SQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。 请避免在新开发工作中使用这些数据类型，并考虑修改当前已使用这些数据类型的应用程序。 请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。

===================================================================================

（五）Unicode 字符串

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^31-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。 

===================================================================================

（六）二进制字符串

binary [ ( n ) ]

长度为 n 字节的固定长度二进制数据，其中 n 是从 1 到 8,000 的值。 存储大小为 n 字节。

varbinary [ ( n | max) ]

可变长度二进制数据。 n 的取值范围为 1 至 8,000。 max 指示最大存储大小是 2^31-1 个字节。 存储大小为所输入数据的实际长度 + 2 个字节。 所输入数据的长度可以是 0 字节。 varbinary 的 ANSI SQL 同义词为 binary varying。

image

长度可变的二进制数据，从 0 到 2^31-1 (2,147,483,647) 个字节。

=================================================================================== 

（七）其他数据类型

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

timestamp：timestamp 的数据类型为 rowversion 数据类型的同义词，并具有数据类型同义词的行为。 在 DDL 语句，请尽量使用 rowversion 而不是 timestamp。rowversion 通常用作给表行加版本戳的机制。 存储大小为 8 个字节。 rowversion 数据类型只是递增的数字，不保留日期或时间。 若要记录日期或时间，请使用 datetime2 数据类型。

hierarchyid：hierarchyid 数据类型是一种长度可变的系统数据类型。 可使用 hierarchyid 表示层次结构中的位置。 类型为 hierarchyid 的列不会自动表示树。 由应用程序来生成和分配 hierarchyid 值，使行与行之间的所需关系反映在这些值中。

uniqueidentifier：uniqueidentifier 数据类型的列或局部变量可通过以下方式初始化为一个值：使用 NEWID 函数。通过从 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 形式的字符串常量进行转换，其中，每个 x 都是 0-9 或 a-f 范围内的十六进制数字。 例如，6F9619FF-8B86-D011-B42D-00C04FC964FF 为有效的 uniqueidentifier 值。

sql_variant：一种数据类型，用于存储 SQL Server 支持的各种数据类型的值。

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

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

空间类型

===================================================================================

SQL Server中，varchar和nvarchar如何选择？

   

varchar在SQL Server中是采用单字节来存储数据的，nvarchar是使用Unico来存储数据的．中文字符存储到SQL Server中会保存为两个字节（一般采用Unico编码），英文字符保存到数据库中，如果字段的类型为varchar，则只会占用一个字节，而如果字段的类型为nvarchar，则会占用两个字节。

正常情况下，我们使用varchar也可以存储中文字符，但是如果遇到操作系统是英文操作系统并且对中文字体的支持不全面时，在SQL Server存储中文字符为varchar就会出现乱码(显示为??)。而且正常情况下，主机都会支持中文的环境，所以如果使用varchar来存储数据，在开发阶段是发现不了的。多数情况下，在部署的时候也不会有问题．

   

所以在Design的时候应该尽量使用nvarchar来存储数据。只有在你确保该字段不会保存中文的时候，才采用varchar来存储。

1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间。

2、VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

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

4、NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。

参考地址：http://baike.baidu.com/view/1255757.htm?fr=aladdin

============================================================================