资源描述：
《sqlserver唯一索引和非唯一索引的区别简析》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、SQLServer唯一索引和非唯一索引的区别简析SQLServer创建索引时，可以指定Unique使之成为唯一索引。“唯一”顾名思义，但是两都到底有什么区别呢？因为索引也是一种物理结构，所以还是要从存储和结构上分析。索引结构分叶级和非叶级，分析时我们要分开来看，这个很重要。文中涉及的索引行大小计算，参考MSDN估计数据库大小索引部分。1.非唯一聚集索引和唯一聚集索引 创建两个测试表，各10000条整数，tb1唯一，tb2非唯一，有1000条为9999的重复值。Codecreatetabletb1(co
2、l1int);declare@iint=1while@i<10001begininsertintotb1values(@i);set@i=@i+1;end;createuniqueclusteredindexucixontb1(col1)go-------createtabletb2(col2int);declare@iint=1while@i<9001begininsertintotb2values(@i);set@i=@i+1;end;goinsertintotb2values(9999)go10
3、00;createclusteredindexcixontb2(col2)go先查询索引的一些基本状况:从上面的结果可以看到，无论是叶级还是非叶级，非唯一聚集索引的索引行都比唯一的大一些，所以所占页也多一点。当然，因为测试数据很小，又是int，所以不明显。 那到底大在哪里呢？将两者的非叶级页和叶级页放在一起比一下就知道了。先找出页号，再用DBCCPAGE来查看。通过PaulS.Randal写的存储过程sp_allocationMetadata可以查到根页和每级索引的首页。 就挑这两个页做对比。发现多出
4、一个UNIQUIFIER，同样叶级也是一样。MSDN说明：“如果聚集索引不是唯一的索引，SQLServer将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。”还有UNIQUIFIER不是一个全局自增列，重复记录增加时此值会发生改变,并且它是一个可为null的变长列。现在来算一算索引行大小： 两个表都是只有一个int型可为NULL的字段，而聚集索引叶级是存储数据本身 叶级是一个4字节的INT列，无变长列，加上3字节
5、的NULL位图，再加上4字节的行头开销：两个表的叶级minSize=4+0+3+4=11 非叶级是一个4字节的INT列，无变长列，加上3字节的NULL位图，加上1字节的行头开销，再加6字节的子页指针：两个表的非叶级minSize=4+0+3+1+6=14 tb1的索引行大小是一致的minSize=maxSize,因为它是唯一的。tb2的索引行大小不一致，有大有小，大的索引行是因为：a)不唯一b)UNIQUIFIER 唯一标识列增加了2+1*2+4=8字节开销，tb2的min和max相差就是这8字
6、节。 tb2的叶级maxSize=4+8+3+4=19 tb2的非叶级maxSize=4+8+3+1+6=22小结：非唯一聚集索引为保证索引键值唯一性，会生成UNIQUIFIER与键列一起组成索引键值。同时无论在叶级还是非叶页级，都比唯一索引占用更多存储空间。 2.堆表上的唯一和非唯一的非聚集索引CodecreatetableIndexTest(idintidentity,UniqueColint,NonuniqueColint)gosetnocounton;declare@iint=1;whil
7、e@i<100000begininsertintoIndexTestvalues(@i,@i);set@i=@i+1;endsetnocountoff;gocreateuniqueindexUIX_UniqueColonIndexTest(UniqueCol);createindexIX_NonuniqueColonIndexTest(NonuniqueCol);goselecti.name,ips.index_id,ips.index_type_desc,index_depth,index_leve
8、l,page_count,record_count,min_record_size_in_bytesasminSize,max_record_size_in_bytesasmaxSize,avg_record_size_in_bytesasavgSizefromsys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('IndexTest'),null,null,'DETAILED')ipsinnerj
