Our clustered index:
create unique clustered index clustind_pk on ourtable (id, partitionedkey)
Our test indexes:
create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)
create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)
create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)
Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.
select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28
No comments:
Post a Comment