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