CODEWITHSUNDEEP
sql-serverindexinginclude
Vignesh M
Vignesh M

Reputation: 178

include column in sql server index

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

Upvotes: 1

Views: 179

Answers (1)

Alsin
Alsin

Reputation: 1618

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]

Upvotes: 1

Related Questions