Post reply

Indexes

  • January 15, 2008 at 11:56 pm

    #180663

    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

    #768483

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply