partition 对index的影响

table 在做partition前后index 可能会变化，下面直接上例子来说明吧

 

table name:person_site

primary key: person_id, site_id

执行：exec sp_helpindex person_site

index_name	index_description	index_keys
pk_person_site	clustered,unique,primary key located on ps_pf_person_site	person_id,site_id
idx_person_site_site_id	nonclustered located on ps_pf_person_site	site_id

 

partition 后的table: person_site_swap

partition key:person_id

primary key: person_id, site_id

执行：exec sp_helpindex person_site_swap

index_name	index_description	index_keys
pk_person_site	clustered,unique,primary key located on ps_pf_person_site	person_id,site_id
idx_person_site_site_id	nonclustered located on ps_pf_person_site	person_id,site_id

 

这是由于在做完partition后，自动给table的所有的索引key 中不包含partition_key字段的加上 partition key字段作为index_keys。

这个可以在

select * from sys.index_columnswhere index_id in(

select index_id fromsys.indexeswhere object_id= object_id('person_site')

and name = 'idx_person_site_site_id'

) and object_id=object_id('person_site')

 

select * from sys.columnswherecolumn_id in(

select column_id fromsys.index_columnswhere index_id in(

select index_id fromsys.indexeswhere object_id= object_id('person_site')

and name = 'idx_person_site_site_id'

) and object_id=object_id('person_site')

--and key_ordinal > 0     --should add this limitation,then the results should be the same with sp_helpindex.

)and object_id=object_id('person_site')

so, 很容易就可以看出person_id被加上去了，并且key_ordinal 是 ‘0’。

同时也不难发现partition_ordinal也是为‘0’，为什么不使用partition_ordinal呢？例如，索引pk_person_site中，primary key 本身就包含person_id，这个时候partition_ordinal仍然为‘0’，如果使用partition_ordinal > 0来限制，就会把正确的person_id 给限制掉，但是若使用key_ordinal,由于此时的 key_ordinal不为0，所以不会被限制掉。

select * from sys.index_columnswhere index_id in(

select index_id fromsys.indexeswhere object_id= object_id('person_site')

and name = ‘pk_person_site’

) and object_id=object_id('person_site')

select * from sys.columnswherecolumn_id in(

select column_id fromsys.index_columnswhere index_id in(

select index_id fromsys.indexeswhere object_id= object_id('person_site')

and name = 'pk_person_site'

) and object_id=object_id('person_site')

--and key_ordinal > 0

)and object_id=object_id('person_site')

ok.

可以走了，虽然回家还是要加班，可是总学了些东西~