SQL Server进阶（二）字段类型

概述

系统数据类型详情

SqlDbType

namespace System.Data
{
    //
    // 摘要:
    //     指定要用于 System.Data.SqlClient.SqlParameter 中的字段和属性的 SQL Server 特定的数据类型。
    public enum SqlDbType
    {
        //
        // 摘要:
        //     System.Int64.64 位的有符号整数。
        BigInt = 0,
        //
        // 摘要:
        //     System.Byte 类型的 System.Array。二进制数据的固定长度流，范围在 1 到 8,000 个字节之间。
        Binary = 1,
        //
        // 摘要:
        //     System.Boolean.无符号数值，可以是 0、1 或 null。
        Bit = 2,
        //
        // 摘要:
        //     System.String.非 Unicode 字符的固定长度流，范围在 1 到 8,000 个字符之间。
        Char = 3,
        //
        // 摘要:
        //     System.DateTime.日期和时间数据，值范围从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为 3.33 毫秒。
        DateTime = 4,
        //
        // 摘要:
        //     System.Decimal.固定精度和小数位数数值，在 -10 38 -1 和 10 38 -1 之间。
        Decimal = 5,
        //
        // 摘要:
        //     System.Double.-1.79E +308 到 1.79E +308 范围内的浮点数。
        Float = 6,
        //
        // 摘要:
        //     System.Byte 类型的 System.Array。二进制数据的可变长度流，范围在 0 到 2 31 -1（即 2,147,483,647）字节之间。
        Image = 7,
        //
        // 摘要:
        //     System.Int32.32 位带符号整数。
        Int = 8,
        //
        // 摘要:
        //     System.Decimal.货币值，范围在 -2 63（即 -922,337,203,685,477.5808）到 2 63 -1（即 +922,337,203,685,477.5807）之间，精度为千分之十个货币单位。
        Money = 9,
        //
        // 摘要:
        //     System.String.Unicode 字符的固定长度流，范围在 1 到 4,000 个字符之间。
        NChar = 10,
        //
        // 摘要:
        //     System.String.Unicode 数据的可变长度流，最大长度为 2 30 - 1（即 1,073,741,823）个字符。
        NText = 11,
        //
        // 摘要:
        //     System.String.Unicode 字符的可变长度流，范围在 1 到 4,000 个字符之间。如果字符串大于 4,000 个字符，隐式转换会失败。在使用比
        //     4,000 个字符更长的字符串时，请显式设置对象。当数据库列为 nvarchar(max) 时，使用 System.Data.SqlDbType.NVarChar
        //     。
        NVarChar = 12,
        //
        // 摘要:
        //     System.Single.-3.40E +38 到 3.40E +38 范围内的浮点数。
        Real = 13,
        //
        // 摘要:
        //     System.Guid.全局唯一标识符（或 GUID）。
        UniqueIdentifier = 14,
        //
        // 摘要:
        //     System.DateTime.日期和时间数据，值范围从 1900 年 1 月 1 日到 2079 年 6 月 6 日，精度为 1 分钟。
        SmallDateTime = 15,
        //
        // 摘要:
        //     System.Int16.16 位带符号整数。
        SmallInt = 16,
        //
        // 摘要:
        //     System.Decimal.货币值，范围在 -214,748.3648 到 +214,748.3647 之间，精度为千分之十个货币单位。
        SmallMoney = 17,
        //
        // 摘要:
        //     System.String.非 Unicode 数据的可变长度流，最大长度为 2 31 -1（即 2,147,483,647）个字符。
        Text = 18,
        //
        // 摘要:
        //     System.Byte 类型的 System.Array。自动生成的二进制数字，它们保证在数据库中是唯一的。timestamp 通常用作为表行添加版本戳的机制。存储大小为
        //     8 字节。
        Timestamp = 19,
        //
        // 摘要:
        //     System.Byte.8 位无符号整数。
        TinyInt = 20,
        //
        // 摘要:
        //     System.Byte 类型的 System.Array。二进制数据的可变长度流，范围在 1 到 8,000 个字节之间。如果字节数组大于 8,000 个字节，隐式转换会失败。在使用比
        //     8,000 个字节大的字节数组时，请显式设置对象。
        VarBinary = 21,
        //
        // 摘要:
        //     System.String.非 Unicode 字符的可变长度流，范围在 1 到 8,000 个字符之间。当数据库列为 varchar(max) 时，使用
        //     System.Data.SqlDbType.VarChar 。
        VarChar = 22,
        //
        // 摘要:
        //     System.Object.特殊数据类型，可以包含数值、字符串、二进制或日期数据，以及 SQL Server 值 Empty 和 Null，后两个值在未声明其他类型的情况下采用。
        Variant = 23,
        //
        // 摘要:
        //     XML 值。使用 System.Data.SqlClient.SqlDataReader.GetValue(System.Int32) 方法或 System.Data.SqlTypes.SqlXml.Value
        //     属性获取字符串形式的 XML，或通过调用 System.Data.SqlTypes.SqlXml.CreateReader 方法获取 System.Xml.XmlReader
        //     形式的 XML。
        Xml = 25,
        //
        // 摘要:
        //     SQL Server 用户定义类型 (UDT)。
        Udt = 29,
        //
        // 摘要:
        //     指定表值参数中包含的构造数据的特殊数据类型。
        Structured = 30,
        //
        // 摘要:
        //     日期数据，值范围从公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日。
        Date = 31,
        //
        // 摘要:
        //     基于 24 小时制的时间数据。时间值范围从 00:00:00 到 23:59:59.9999999，精度为 100 毫微秒。对应于 SQL Server
        //     time 值。
        Time = 32,
        //
        // 摘要:
        //     日期和时间数据。日期值范围从公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日。时间值范围从 00:00:00 到 23:59:59.9999999，精度为
        //     100 毫微秒。
        DateTime2 = 33,
        //
        // 摘要:
        //     显示时区的日期和时间数据。日期值范围从公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日。时间值范围从 00:00:00 到 23:59:59.9999999，精度为
        //     100 毫微秒。时区值范围从 -14:00 到 +14:00。
        DateTimeOffset = 34
    }
}

View Code

常见数据类型所占用的字节数

数据类型大致分为四种：数值类型，日期和时间类型，字符串类型，GUID，使用DataLength()能够查看任意数据类型的变量所占用的字节数量

1.数值类型

对于整数类型，TinyInt 占用1Byte，表示的整数范围是：0-255；SmallInt，int和bigint 分别占用2B，4B和8B。

对于小数类型，decimal(p,s)表示精确的小数类型，float(n)表示近似的小数类型，常用于表示百分比，除法的结果，有两种类型float(24)占用4B，float(53)占用8B，参考《SQL Server的小数数值类型（float 和 decimal）用法》。

2.日期和时间类型

date表示日期，占用3B；
Datetime2(n)，根据时间的毫秒部分来确定占用的字节数量：当n是1，或2时，占用6B；当n是3，或4时，占用7B；当n是5，6，或7时，占用8B；
datetime占用8B，建议使用datetime2(n)来替代datetime；

3.字符类型

建议使用变长字符类型，varchar和nvarchar，后者占用的字节是前者一倍；如果数据中都是拉丁字母，使用varchar更好。对于LOB数据类型，建议使用varchar(max) 和 nvarchar(max)，单列能够存储最大2GB的数据。

变长字符类型 varchar(n) 和 nvarchar(n)，N值的最大值是多少？例如：

declare @nv_max varchar(9000)
declare @nv_min varchar(0)

select @nv_max
select @nv_min

赋予类型 'varchar' 的大小(9000)超出了任意数据类型的最大允许值(8000)。指定的长度或精度 0 无效。

由此可见，对于varchar(N)，N的取值范围的上限是8000，下限是1，可变长度单字节字符类型的取值范围是[1-8000]；

对于varchar(n)，n的最大值是8000；
对于nvarchar(n)，n的最大值是4000；
不管用于变量，还是用于table column，都不能超过8000Bytes，这种限制是由SQL Server的一个Page=8KB决定的。

如果存储的数据占用的字节数超过8000，那么必须使用LOB类型：varchar(max) 和 nvarchar(max)，max突破长度8000Byte的限制，达到LOB的最大值2GB。

4.UniqueIdentifier数据类型

UniqueIdentifier数据类型占用16B，GUID的格式是8-4-4-4-12，即'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'，不要使用varchar来存储GUID。

例如，使用varchar来存储GUID，将会浪费20B

declare @ui uniqueidentifier
declare @vc varchar(max)

set @ui=newid()
set @vc=cast(@ui as varchar(max))

select @ui,@vc,datalength(@ui),datalength(@vc)

如果表的数据行总量是5千万，那么使用varchar来存储GUID将会浪费：953MB。在数据类型的选择上，必须锱铢必较，能省就省。

在设计Table Schema时，要实现三大目标：占用空间少，查询速度快，更新速度快。这三个目标有些千丝万缕的关联，设计良好的Table Schema，都会实现，反之，设计差的Table Schema，都不能实现。

bit 类型只能存储1和0

在存储日期数据时，Date占用3Byte，DateTime占用8Byte，DateTime2(2)占用6Byte，DateTime2(4)占用7Byte，DateTime2(7)占用8Byte。不管从表示的精度上，还是从占用的存储空间上来看，DateTime2(N)都完胜DateTime。

例如，存储‘yyyy-mm-dd MM:HH:SS’格式的日期数据，有以下4中选择：

使用字符串 varchar(19) 或 nvarchar(19)存储，十分不明智，前者占用19Byte后再占用38Byte；
使用数据类型 datetime2(7)存储，占用8Byte，虽然精度更高，但是毫秒都是0，浪费存储空间；
使用数据类型 datetime存储，占用8Byte，如果需要存储毫秒，datetime不满足；
使用数据类型 datetime2(2)存储，占用6Byte，相比较是最理想的。

在创建Index时，必须慎重选择聚集索引键，主要有两个原因

聚集索引其实就是表本身，SQL Server必须保持物理存储顺序和逻辑存储顺序一致

NonClustered Index的叶子节点中，都包含Clustered Index键。

SQL中char、varchar、nvarchar的区别

1.CHAR

　　CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充，所以在读取的时候可能要多次用到trim()。

2.VARCHAR

　　存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用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
它们的区别概括成：

CHAR，NCHAR 定长，速度快，占空间大，需处理

VARCHAR，NVARCHAR，TEXT 不定长，空间小，速度慢，无需处理

NCHAR、NVARCHAR、NTEXT处理Unicode码

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

使用nvarchar的另一个非常好处就是在判断字符串的时候可以不需要考虑中英文两种字符的差别．

资料

http://www.w3school.com.cn/sql/sql_datatypes.asp