1、一个字节造成的巨大性能差异

--建表

CREATE TABLE tb4039(c1 INT IDENTITY,c2 char(4035) not null)
CREATE TABLE tb4040(c1 INT IDENTITY,c2 char(4036) not null)

--由于INT类型是4个字节，所以我们创建的tb4039表有4+4035=4039个字节的长度，tb4040中的c2字段比--tb4039中的c2字段多了一个字节，总长度是4040字节
--插入100条数据
DECLARE @i INT
SET @i=1
WHILE @i<=100
BEGIN
    INSERT INTO tb4039 (c2) VALUES('test'+CONVERT(VARCHAR(5),@i));
    INSERT INTO tb4040 (c2) VALUES('test'+CONVERT(VARCHAR(5),@i));
    SET @i=@i+1
END

---两个表的数据占用的空间

SELECT OBJECT_NAME(i.object_id) AS TableName,data_pages*8 AS DataSize --这里返回的是数据页个数，1页是8K，所以乘以8
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id=OBJECT_ID('tb4039') OR i.object_id=OBJECT_ID('tb4040')

 

---一个字节的差距就造成了存储空间成倍的增加，为什么会这样呢？

/******

     SQL Server最小的存储单位是页（Page），一个页的大小是8K=8192字节。除了页头占用的空间和行偏移矩阵占用的空间
（100字节）中间剩下的空间就是给数据行使用的（8092字节）。不管我们对表的定义是多么的简单，一行数据除了数据自身占
用的空间外，控制信息（状态位、数据列、定长数据列等）至少还要占用7个字节。如果定义的数据列很多，或者里面有变长数
据列，那么占用的空间可能会更多。

    现在回到我们前面讲到的2个表tb4039和tb4040，要存储tb4039中的一行数据需要4039+7=4046字节，所以正好可以在一个
页中保存2行数据。所以插入了100行数据，实际上是保存在50个数据页中，大小就是8K*50=400K。对于tb4040表，要存储一行
数据需要4047个字节，没法在一个页中保存2行数据，所以一行数据就占用一个数据页，100行数据占用了100个数据页，大小
就是8K*100=800K

    这里只是举了一个极端的例子，所以造成了一个字节的偏差而使占用的存储空间翻倍，在实际应用中很少会出现这么极端
的情况，但是很有可能使一个页存储5条数据的因为某个列多了1个2个字节所以只能存储4条数据。也许大家认为少存一条数据
并没有什么，但是在数据量变的非常庞大以后一页4条数据和一页5条数据将会产生明显的性能差异。使得一页中存放更多的数
据并不是为了节约存储成本.

     实际上我们要让一个数据页中存放更多行的数据主要是出于性能的考虑。SQL Server进行数据库读写操作的基本单位是页，
如果一页中存放了更多的数据，那么对表进行扫描和查找时进行的IO操作将减少，毕竟IO操作是非常消耗时间影响性能的。
假设tb4039中有100W条数据，那么进行全表扫描就要读取50W个数据页，如果读取10W个数据页花费1秒钟，那么对表tb4039
进行扫描需要花费5秒钟时间，而如果是使用tb4040存储这100W条数据，进行全表扫描则需要读取100W个数据页，总共花费
10秒钟时间。就一个字节的差别，一个是5秒另一个是10秒，对性能的影响非常明显。

为了提高数据库查询的性能，在表设计时可以遵循以下建议：

1、主键尽可能的短，能用tinyint的就不要用int，能用char(5)的就不要用成varchar(50)。
2、计算好表列的长度，能够在一个页中存放5条数据的，那就不要将字段设置的太长使得一个页中只能存放3条或者4条数据。
3、尽量将字段设置为不允许为NULL，因为NULL值在存储和数据处理时系统需要专门的处理，降低了性能。
4、能够用固定长度的就不要用变长字段，比如身份证号就可以使用CHAR(18)，而不应该使用VARCHAR(18)。
5、不要在一个表中建立太多的列，如果一个实体的属性太多时可以考虑进行垂直分割，将常用的字段放在一个表，不常用的
   字段放另外的表，这样可以减小常用字段表中数据列占用的空间，使得一个数据页中存储更多的数据行。
6、不要将大对象、长字符串和常用的字段放在同一个表中。同样还是出于性能上的考虑，比如有个产品表，里面有产品ID、
   产品名字、产品售价、产品图片、产品描述等字段，那么我们可以将产品ID、产品名字、产品售价这几个常用的而且占用
   空间小的列放在一个表，然后建立产品ID、产品图片、产品描述这样的表，通过外键约束的方式将大对象数据和长字符串数据放在另一个表中

    ******/