【转】对于表列数据类型选择的一点思考
原文链接：http://www.cnblogs.com/CareySon/archive/2012/06/14/ChoiceOfDataTypeWhenDesignTable.html
简介
SQL Server每个表中各列的数据类型的选择通常显得很简单，但是对于具体数据类型的选择的不同对性能的影响还是略有差别。本篇文章对SQL Server表列数据类型的选择进行一些探索。
一些数据存储的基础知识
在SQL Server中，数据的存储以页为单位。八个页为一个区。一页为8K，一个区为64K,这个意味着1M的空间可以容纳16个区。如图1所示：
图1.SQL Server中的页和区
如图1（PS:发现用windows自带的画图程序画博客中的图片也不错
）可以看出，SQL Server中的分配单元分为三种，分别为存储行内数据的In_Row_Data，存储Lob对象的LOB_Data,存储溢出数据的Row_Overflow_data。下面我们通过一个更具体的例子来理解这三种分配单元。
我建立如图2所示的表。
图2.测试表
图2的测试表不难看出，通过插入数据使得每一行的长度会超过每页所能容纳的最大长度8060字节。使得不仅产生了行溢出(Row_Overflow_Data)，还需要存储LOB的页.测试的插入语句和通过DBCC IND看到的分配情况如图3所示。
图3.超过8060字节的行所分配的页
除去IAM页，这1行数据所需要三个页来存储。首先是LOB页，这类是用于存储存在数据库的二进制文件所设计，当这个类型的列出现时，在原有的列会存储一个24字节的指针，而将具体的二进制数据存在LOB页中，除去Text之外，VarBinary(max)也是存在LOB页中的。然后是溢出行，在SQL Server 2000中，一行超过8060字节是不被允许的，在SQL Server 2005之后的版本对这个特性进行了改进，使用Varchar,nvarchar等数据类型时，当行的大小不超过8060字节时，全部存在行内In-row data,当varchar中存储的数据过多使得整行超过8060字节时，会将额外的部分存于Row-overflow data页中，如果update这列使得行大小减少到小于8060字节，则这行又会全部回到in-row data页。
数据类型的选择
在了解了一些基础知识之后。我们知道SQL Server读取数据是以页为单位，更少的页不仅仅意味着更少的IO，还有更少的内存和CPU资源消耗。所以对于数据选择的主旨是:
尽量使得每行的大小更小
这个听起来非常简单，但实际上还需要对SQL Server的数据类型有更多的了解。
比如存储INT类型的数据，按照业务规则，能用INT就不用BIGINT,能用SMALLINT就不用INT,能用TINYINT就不用SMALLINT。
所以为了使每行的数据更小，则使用占字节最小的数据类型。
1.比如不要使用DateTime类型，而根据业务使用更精确的类型，如下表:
| 类型 | 所占字节 |
| Date(仅日期) | 3 |
| Time(仅时间) | 5 |
| DateTime2(时间和日期) | 8 |
| DateTimeOffSet(外加时区） | 10 |
2.使用VarChar(Max),Nvarchar(Max),varbinary(Max)来代替text,ntext和image类型
根据前面的基础知识可以知道，对于text,ntext和image类型来说，每一列只要不为null,即使占用很小的数据，也需要额外分配一个LOB页，这无疑占用了更多的页。而对于Varchar(Max)等数据类型来说，当数据量很小的时候，存在In-row-data中就能满足要求，而不用额外的LOB页，只有当数据溢出时，才会额外分配LOB页，除此之外，Varchar(Max)等类型支持字符串操作函数比如：
- COL_LENGTH
- CHARINDEX
- PATINDEX
- LEN
- DATALENGTH
- SUBSTRING
3.对于仅仅存储数字的列，使用数字类型而不是Varchar等。
因为数字类型占用更小的存储空间。比如存储123456789使用INT类型只需要4个字节，而使用Varchar就需要9个字节(这还不包括Varchar还需要占用4个字节记录长度)。
4.如果没有必要，不要使用Nvarchar,Nchar等以“字”为单位存储的数据类型。这类数据类型相比varchar或是char需要更多的存储空间。
5.关于Char和VarChar的选择
这类比较其实有一些了。如果懒得记忆，大多数情况下使用Varchar都是正确的选择。我们知道Varchar所占用的存储空间由其存储的内容决定，而Char所占用的存储空间由定义其的长度决定。因此Char的长度无论存储多少数据，都会占用其定义的空间。所以如果列存储着像邮政编码这样的固定长度的数据，选择Char吧，否则选择Varchar会比较好。除此之外，Varchar相比Char要多占用几个字节存储其长度，下面我们来做个简单的实验。
首先我们建立表，这个表中只有两个列，一个INT类型的列，另一个类型定义为Char(5)，向其中插入两条测试数据，然后通过DBCC PAGE来查看其页内结构，如图4所示。
图4.使用char(5)类型，每行所占的空间为16字节
下面我们再来看改为Varchar(5),此时的页信息，如图5所示。
图5.Varchar(5)，每行所占用的空间为20字节
因此可以看出，Varchar需要额外4个字节来记录其内容长度。因此，当实际列存储的内容长度小于5字节时，使用char而不是varchar会更节省空间。
关于Null的使用
关于Null的使用也是略有争议。有些人建议不要允许Null,全部设置成Not Null+Default。这样做是由于SQL Server比较时就不会使用三值逻辑(TRUE,FALSE,UNKNOWN)，而使用二值逻辑(True,False),并且查询的时候也不再需要IsNull函数来替换Null值。
但这也引出了一些问题，比如聚合函数的时候,Null值是不参与运算的，而使用Not Null+Default这个值就需要做排除处理。
因此Null的使用还需要按照具体的业务来看。
考虑使用稀疏列(Sparse)
稀疏列是对 Null 值采用优化的存储方式的普通列。 稀疏列减少了 Null 值的空间需求，但代价是检索非 Null 值的开销增加。 当至少能够节省 20% 到 40% 的空间时，才应考虑使用稀疏列。
稀疏列在SSMS中的设置如图6所示。
图6.稀疏列
更具体的稀疏列如何能节省空间，请参看MSDN。
对于主键的选择
对于主键的选择是表设计的重中之重，因为主键不仅关系到业务模型，更关系到对表数据操作的的效率（因为主键会处于B树的非叶子节点中，对树的高度的影响最多）。关于主键的选择，我之前已经有一篇文章关于这点：从性能的角度谈SQL Server聚集索引键的选择，这里就不再细说了。
----------------------------------------------
IAM=index allocation map page,它用来跟踪哪些盘区是放数据的，哪些盘区是用来放索引的，它本是不存储用户的数据或索引，而是存储并决定用户的数据或索引该存储在哪个盘区！所以，它可以管理多达4GB的空间！
nvarchar存中文还好，存英文一个字符会占两个字节,选择nvarchar中文就不会出现乱码什么的。所以字段只存英文的话，不要考虑nvarchar
总结
本篇文章对于设计表时，数据列的选择进行了一些探寻。好的表设计不仅仅是能满足业务需求，还能够满足对性能的优化。
如果从上面几个方法考虑，像“尽量使得每行的大小更小”就是错误的，应该优先使用多种数据库支持的可变字符串，整型，而不是二进制类型。
关于主键的选择，更不应该使用“关系到业务模型”的主键，最好使用guid/uuid的base64编码主键，方便高并发合并，支持nosql的key-value存储。
优先使用字符串类型，不要使用和业务相关的主键，不要使用外键，不要使用二进制存储，不要使用和数据库特征相关的字段。
把数据库当做存储数据的地方，而不是放业务逻辑的地方，业务逻辑包括外键，存储过程，触发器，视图等。
优先考虑可伸缩性更强，可分布式的数据库设计方案，因为一台电脑再强也强不过云计算。
如果你设计数据库表，可以很方便的迁移到nosql上，这种设计就是一种可伸缩性设计，如同好的框架对于软件后期发展的重要性。
举个例子，现在很多公司不用存储过程的原因，就是存储过程只是把一部分代码写在数据库，而这些代码很大程度上是业务逻辑层的事。
依赖第三范式过度设计数据库，实际上数据库干了很多应该属于业务逻辑层的事。第三范式产生之初只是为了节省那时比现在手机功能还弱的电脑资源，发挥单机的最大性能，而现在可以多个电脑水平扩展来解决这些问题。游击兵单兵再厉害也是无法和可扩展的正规矩阵军团对抗的。在提前假设条件的变化下，数据库表的设计需要更好的可扩展，而不是用类似用二进制的数据来提高效率（如同当初socket网络通信多用二进制通信，可扩展性极差，现在多用json或xml）。
关于主键的选择，不使用业务逻辑相关的主键，也是上述思想的体现。其实业务逻辑相关的主键都可以通过索引来解决问题。而在分布式环境下，如果产生唯一主键，可以方便扩展到未来的nosql中（可以把数据量大的表放在nosql数据库中，如发布的文章）使用key-value来存储。
注意了，不是要使用nosql来淘汰关系数据库，而是各取所长混合使用。小数据量的表放在关系数据库中，而大数据量的表使用key-value方式放入nosql，如发表的文章，一个key,一个文章主体value，而在关系数据中依然保存文章这个表，只是文章主体没有了，只有文章key。这样方便查询，又能解决高性能问题。说白了，就是把数据库表中的大字段类型，用key-value的方式放入nosql，如同现在解决图片问题一样（一个图片的存储路径key,value放在硬盘）。当然你可以把很少查询的表也用nosql存储，这样原来数据库表设计的guid/uuid主键就起关键作用了。这样整个系统可伸缩性就高多了。
关于云计算与分布式计算，如同web 2.0对http协议，不是一个概念，云计算建立在分布式计算之上，但不光使用分布式计算技术，还包括很多如云存储，虚拟技术，基础设施平台你可以了解一下亚马逊云计算服务内容。对于电脑中的操作系统，网络，文件存储，数据库这些基础的东西，云计算强调可伸缩性分配。如果用水来比喻计算能力，云计算是海洋，私有云是湖泊，单机是池塘，手机是脸盆。
云计算不是普通人能玩得起的，如果建设一个100万台机器的云计算中心,一年光是电费就要20-50亿元(一台2000到5000元)。另外新的arm低功耗服务器可能使云计算快速普及，使用成本下降。当然普通程序员，只需要怎么实现这些程序就行了，设计可扩展可伸缩程序是关键。
欢迎喜欢深入研究SQL的人加群:214363958