All is OK when I create an index within SSMS with this script

but the same description in DDT raises an error when deployment

(in log file

Is that limit of 16 column names in an index definition a SF DDT limit, or a Microsoft SMO limit?

We really need many indexes like this (with sometimes more than 40 columns as INCLUDE), and hoped that DDT could manage it.

Thanks in advance

INCLUDing columns in index often improve performances, especially if you have complex views on tables with many columns (say more than 60 or 80 columns in 2 or more tables). You can see it in execution plans.

I didn't think about post-deployment scripts as a workaround, but of course, it is probably the best way. Thanks for advice!

in DDT, I can't see where and how I can INCLUDE columns in index (not the columns that will be part of index expression, but those that will be included)

Is it a SMO limit ? I mean, is the INCLUDE in index supported by SMO ?

You can add the fields (columns) to an index when creating the index:

Since I am sure that you already know this, then you may need to give me a little better understanding of what you are trying to accomplish.

what you show here is similar to the following syntax:

and what I want to do with DDT is similar to :

With the keyword include, the values of the "other fields" are store IN the index itself (really in the b-tree leaf corresponding to the record defined by the my_pk and my_name combination).

Say we have a clustered index on the PK identity column, so the index is physically the table itself, and we need to retrieve a row that whe define by the 3 fields. In my real table, these fields are cde_cmd, no_ligne_1, and cde_mat). They define the combination I need in one of the JOIN of a view. But in the SELECT statement of that view, I'll ask for cal_surf, surf_lq, lq_m2, and so on.

If these columns are in the INCLUDE statement of the index definition, their values will be brought up during the Non-Clustered index scan of the execution plan, and doing so, I shall avoid the cost of a Hash-Match table, using only a Merge Join.
The more you need columns in the select statement, the more you need to look at the INCLUDE clause in your indexes definition.

Does it make sense?

A couple of things.  I have verified that the 16 columns (or 900 characters) for a covering index is the limit for SQL Server and has nothing to do with SMO per say.  SMO just enforces these rules in a more strict manner.  Here is a paragraph from SQL Server Performance about this specific issue:

Here is a link that may give you more details and insight about this:

http://www.sql-server-performance.com/articles/clustering/create_nonkey_columns_p1.aspx

Next, as for the INCLUDED tag on the index, yes, you are correct that at present the DDT does not support this.  We have added this to the list for a future update, but will not be immediately implemented. 

AFAIK (and what I read in BOL), 16 is the max # of key columns in the index definition, but INCLUDE may content 1 to 1023 columns...

Yes, there's a risk to enlarge the index size, and to increase disk I/O. If the columns you would include are all Char(100), sure you'll see bad performances. But when I have 20 or even 40 columns of SmallInt, there is a huge benefice of using Include columns.

And for DDT, I'll wait for the future update (before 2015, please, because I'll be retired )

I do not debate philosophy: don't forget that English is not my native language, and that BOL I refer to is in French. So sometimes (often) I may hurt someone, and I apologize for that.

Especially here, I've found 2 differents meaning of "covering index". One refers to index which includes all columns, and another to index where all columns are either in key columns, either in include columns. Also found a third usage of "covering index", where it means index with include columns, no matter of the # of them. Even in French, it is confused for me . Trying to dicuss it in English is very difficult.

Thanks again for your patience (and for StrataFrame)

There is no apology necessary and I was never frustrated, offended, or anything of the sort, though my post was a little bleak.  When I was posting I was trying to get out the door, so I did not elaborate on any of my explanations.

Yes, you are 100% correct that terminology and nomeclature can mess things up when having these types of discussions, even when English IS your native language!

I think that we are on the same page here.  Long story short, we will be introducing the INCUDED columns in a near release as we are also adding additional data type support for some other users as well (yes, that is you Keith if you are reading this post).  We are going to be careful on this build prior to releasing to make sure that there are many unit tests in place to make sure nothing is broken along the way.  But this is something that is very beneficial and even something I have recently used as well.  So there is definitely a need.

Thanks for your continued support and I apologize if my post seemed harsh, as that was not my intention at all!