include column in sql server index

Question

All non-clustered-index (NCI) will also store the key column of clustered-index(CI)

While creating the NCI, if we intentionally include the key column what would happen, is that occupy space one more time?

Means to store key column, will space occupied twice ?

Thanks in advance

Alsin · Accepted Answer · 2015-02-04 12:47:04Z

1

No, space won't be taken twice.

create table test (id int not null primary key, c1 int, c2 int)

create index ix1_test on test (c1)
create index ix2_test on test (c1) include (id)
create index ix3_test on test (c1) include (id, c2)

sp_SQLskills_SQL2012_helpindex shows this information:

index_name  index_description     index_keys    included_columns    columns_in_tree columns_in_leaf
[PK__test]  clustered, unique, PK [id]        NULL                  [id]            All columns "included"
[ix1_test]  nonclustered          [c1]        NULL                  [c1], [id]      [c1], [id]
[ix2_test]  nonclustered          [c1]        [id]                  [c1], [id]      [c1], [id]
[ix3_test]  nonclustered          [c1]        [id], [c2]            [c1], [id]      [c1], [id], [c2]

answered Feb 4, 2015 at 12:47

Alsin

1,6481 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KumarHarsh Over a year ago

Do I understand that included column do not occupy space.So in above example c2 do not occupy any space.Thanks.

Alsin Over a year ago

Index is a b-tree structure. c2 is included column, so it appears in a tree only on leaf level. So c2 does take space, but on leaf-level only.

Collectives™ on Stack Overflow

include column in sql server index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related