Include column VS Index Size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Include column VS Index Size

Include column VS Index Size

Rate Topic

Display Mode

Topic Options

Author

Message

panwar.jt

Posted Wednesday, November 07, 2012 11:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, December 17, 2012 12:12 PM
Points: 76, Visits: 284

Hi,

I an wondered why Include column does not change the size of non-clustered index.
As I know non-clustered index is combination of leaf and non-leaf noedes, and include column are stored in leaf node only, and if create a non-clustered index it effect the space used by respective table.
So why include column does not increase the size of non-clustered index size.

Please help me to understand this.

Thanks
Kuldeep panwar

Post #1382103

GilaMonster

Posted Wednesday, November 07, 2012 11:50 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Include columns will increase the size of nonclustered indexes.

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 #1382278

panwar.jt

Posted Thursday, November 08, 2012 4:22 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, December 17, 2012 12:12 PM
Points: 76, Visits: 284

So why it is called that Include column does not increase non-cluster index size......

Post #1382760

ScottPletcher

Posted Thursday, November 08, 2012 4:25 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

It will increase the size of the index, but only for leaf pages. The non-leaf pages of the index will be the same size as before.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1382761

GilaMonster

Posted Thursday, November 08, 2012 7:56 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

panwar.jt (11/8/2012)

So why it is called that Include column does not increase non-cluster index size......

Where is that stated?

Include columns won't increase the size of the index key (they're not part of the index key), but they will increase the size of the index as a whole.

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 #1382798

« Prev Topic | Next Topic »

Permissions