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 )