Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Include column VS Index Size Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 11:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Wednesday, November 7, 2012 11:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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
Posted Thursday, November 8, 2012 4:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, November 8, 2012 4:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 2,044, Visits: 3,062
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1382761
Posted Thursday, November 8, 2012 7:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 »

Add to briefcase

Permissions Expand / Collapse