Nonclustered Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Nonclustered Indexes

Nonclustered Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Saturday, November 10, 2012 4:27 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,199, Visits: 2,125

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

Post #1383401

Bhuvnesh

Posted Monday, November 12, 2012 2:22 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1383559

GilaMonster

Posted Monday, November 12, 2012 5:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 37,726, Visits: 29,986

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 2008, MVP
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

Post #1383639

« Prev Topic | Next Topic »

Permissions