January 15, 2008 at 11:56 pm
Hi All,
I was going thru my routine work of index tuning.In the course I created 2 indexes(refer below) on table LeadsMaster
PK__leadsmaster__1C1DF2EF : is clustered index on primary key
dpages:2242
reserved:2505
used:2497
rowcnt:46479
xmaxlen:8060
IX_iCustIDiProspID: is non clustered composite key on custid and prospid column
dpages:130
reserved:136
used:132
rowcnt:46479
xmaxlen:30
I was surprised to see the difference between the dpages column of both indexes.
Any one can explain me that y there is a difference between the dpages '
column of the 2 indexes
Thanx and Regards,
Ahmad
Bite off more than u chew,then chew it.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 16, 2008 at 12:49 am
It's because one is clustered and one is nonclustered.
A clustered index is the table. The leaf level pages of a clustered index are the data pages and contain the full rows.
A nonclustered index just has in it the index keys and a pointer to the clustered index on the leaf pages. Hence the amount of data in a nonclustered index is much smaller.
Think on a nonlustered index as an index at the back of a refrence book and the clustered index as the pages of the books
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply