Do SQL Server included columns takes double space?

Question

My question is for example I have following columns

Cl1 int - primary key
Cl2 int
Cl3 nvarchar(200)
Cl4 nvarchar(max)

Now assume that I am creating a non-clustered index on Cl2 however when I query Cl2 I always retrieve Cl3 and Cl4 also.

It is logical to include Cl3 in the index as it will make retrieval operation faster, however Cl4 is nvarchar(max) and can be pretty big: this column holds crawled page source

So my question is: it is logical to include Cl4 in the non-clustered index or not

Including it would make Cl4 to be stored exactly 2 times on the hard drive?

Thank you very much for answers

SQL Server 2014

Yes, if you **include** `CL4` into your non-clustered index, then the values of `CL4` will be stored in both the basic data page, as well as in the index page for your non-clustered index. It's a classic trade-off between space (you need more) and speed (you should get better speed if you include `CL4`) - pick one or the other - you cannot have both — marc_s, Aug 14 '14 at 21:22

score 2 · Answer 1 · edited May 23 '17 at 10:26

2

I will refer you to this answer that explains a bit more about included columns in indexes.

In short, yes, the space used will be greater if you include the column in your index, but the performance will be better. The choice is yours.

edited May 23 '17 at 10:26

Community

1
1

answered Aug 14 '14 at 20:48

Jonathan M

1,891
13
21

2

Retrieve performance will be better. Insert/update/delete performance can be degraded. – Joel Coehoorn Aug 14 '14 at 21:29

score 2 · Accepted Answer · answered Aug 14 '14 at 21:56

from Books online for SQL Server 2014:

Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes.

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index.

score 1 · Answer 3 · answered Aug 14 '14 at 21:32

As you surmised, including C14 likely will have a positive impact on select performance, at the cost of disc space. I think you also understand that it's not just disc space, but also inserts, and updates, and deletes that are impacted by this. Whether the change is worth it or not will depend on your system. I'd encourage you to do some real performance profiling... of both options. Then make your decision based on the results.

Do SQL Server included columns takes double space?

3 Answers3