Nonclustered Indexes

  • Hi All

    Please help me clear up something regarding nonclustered indexes

    A non-unique Clustered index has a 4byte uniquifier column added to into to make it unique, does SQL Server do the same for every non-unique Nonclustered index you create?

    Unlike clustered indexes which contains the entire row at the leaf level,Nonclustered indexes contains just the column(s) specified in the index definition. Does SQL Server copy the contents of the column(s) specified and create the nonclustered index on the copy or is the column(s) removed from the table?

    Thanks

  • SQLSACT (11/10/2012)


    A non-unique Clustered index has a 4byte uniquifier column added to into to make it unique, does SQL Server do the same for every non-unique Nonclustered index you create?

    yes

    SQLSACT (11/10/2012)


    Unlike clustered indexes which contains the entire row at the leaf level,Nonclustered indexes contains just the column(s) specified in the index definition. Does SQL Server copy the contents of the column(s) specified and create the nonclustered index on the copy or is the column(s) removed from the table?

    i found a bit confusing this quoted text here actually leaf level of NC indexes contain the address/pointer of actual data at leaf level

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • SQLSACT (11/10/2012)


    A non-unique Clustered index has a 4byte uniquifier column added to into to make it unique, does SQL Server do the same for every non-unique Nonclustered index you create?

    No. Only the clustered index gets a uniquifier if it is not declared unique

    Does SQL Server copy the contents of the column(s) specified and create the nonclustered index on the copy or is the column(s) removed from the table?

    Err.... If it was the latter, then how would SQL be able to do a table scan (if nonclustered indexes resulted in the column being dropped from the table)?

    Books Online says explicitly that nonclustered indexes are a copy

    Please, buy yourself a copy of Kalen's SQL Server 2008 internals and read it.

    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 3 posts - 1 through 2 (of 2 total)

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