question

skpani avatar image
skpani asked

index_id getting changed

As we know that Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index and Heaps have one row in sys.partitions, with index_id = 0 for each partition used by the heap.But I have a table with 5 partitions but only 2 columns(PROD_ID,PROD_NAME). Whenever I execute the below query my index_id column getting changed as per the below condition : SELECT * FROM SYS.PARTITIONS WHERE OBJECT_NAME(OBJECT_ID)='DEMO' Condition 1: Whenever I put only UNIQUE key on PROD_ID the [index_id]=0 which is repeating for 5 times and [index_id]= 2 which is repeating again for 5 times.I am getting 10 rows. Condition 2: Whenever I put NON-CLUSTERED UNIQUE on PROD_ID the [index_id]=0 which is repeating for 5 times and [index_id]=2 which is repeating again for 5 times and [index_id] = 3 which is repeating again for 5 times.I am getting 15 rows.. Why so ?
indexnonclustered-indextable partitionheap
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.