Search code examples
sqlsql-serverindexingsql-server-2014non-clustered-index

Do SQL Server included columns takes double space?


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


Solution

  • 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.