StrataFrame Forum

Error on index creation if column number > 16

http://forum.strataframe.net/Topic25962.aspx

By Michel Levy - 2/12/2010

Hi,

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

CREATE NONCLUSTERED INDEX [IX_ligne_1_rpt_et_dev1] ON [dbo].[ligne_1]
(
[cde_cmd] ASC,
[no_ligne_1] ASC,
[cde_mat] ASC
)
INCLUDE ( [epaisseur],
[cal_surf],
[surf_lq],
[lq_m2],
[px2_m2],
[laq_l1],
[laq_l2],
[laq_f2],
[modew],
[mat_four],
[libelle3],
[longueur],
[largeur],
[raly],
[com_lg1],
[typ_laq],
[fl_bcl]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

but the same description in DDT raises an error when deployment

(in log fileSmile

X 12/02/2010 16:19:50 ->   Le index 'IX_ligne_1_rpt_et_dev1' sur la table 'dbo.ligne_1' a 20 noms de colonnes dans la liste de clés index. La limite maximale pour la liste des colonnes de clés d'index ou de statistiques est 16.

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

By Trent L. Taylor - 2/12/2010

Wow, I have never created an index with that many columns! w00t



I would have to actually test this to give you any conclusive results. But most likely, this is a limit of SMO. There is an easy enough workaround for something like this. You could just use a Post Deployment Script to create this index if the standard T-SQL code is allowing it.



I will have to look into the other.
By Michel Levy - 2/12/2010

Trent,

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!

By Trent L. Taylor - 2/12/2010

Yes, I live in execution plans and I also spend a vast amount of time creating the most optimized queries as possible. The other day we had a query that would have taken 60 minutes and by adding only a few keys, changing the logic, and using index hints, we got this same time down to 250 milliseconds. Regardless, that should give you a workaround. Smile
By Michel Levy - 2/12/2010

Trent,

in DDT, I can't see where and how I can INCLUDE columns in index Pinch (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 ?

By Trent L. Taylor - 2/12/2010

Not sure that I know what you mean since I think this is a trick question. BigGrin

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.

By Michel Levy - 2/12/2010

Trent,

what you show here is similar to the following syntax:

CREATE NONCLUSTERED INDEX [Your_index_name] ON [dbo].[GuidTest]
(
[my_pk] ASC,
[my_name] ASC
)

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

CREATE NONCLUSTERED INDEX [Your_index_name] ON [dbo].[GuidTest]
(
[my_pk] ASC,
[my_name] ASC
)
INCLUDE
(
[my_other_field ASC,
[and_another_one_here] ASC
)

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?

By Trent L. Taylor - 2/13/2010

Michel,

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:

Covering indexes, if used judiciously, can be used to speed up many types of commonly run queries. But covering indexes have some limitations. For example, they are limited to a maximum of 16 columns; they have a 900-character maximum width; certain datatypes cannot be included in them; and adding additional columns to an index makes the index wider, which in turn requires more disk I/O to read and write the rows, potentially hurting performance, especially if the table is subject to many INSERTs, UPDATEs, and DELETEs.

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. 

By Michel Levy - 2/13/2010

Trent,

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 Wink )

By Trent L. Taylor - 2/13/2010

I'm not trying to debate philosophy, and the 16 column limit is referring to the covering indexes, not the INCLUDE columns which are not currently supported anyway. But this is something that we will add to the DDT as we see the need. Thanks.
By Michel Levy - 2/13/2010

Trent,

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 Crazy. Trying to dicuss it in English is very difficult.

Thanks again for your patience (and for StrataFrame)

By Trent L. Taylor - 2/14/2010

Michel,

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! Hehe

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!

By Edhy Rijo - 2/19/2010

Trent L. Taylor (02/14/2010)
...Long story short, we will be introducing the INCUDED columns in a near release...




It happens that I am in the process of reviewing some of my queries for performance optimization and many of the INDEX creation suggested by the MS SQL Server Management Studio have the INCLUDE clause, so I believe this will be a must needed request.



Please include me in any possible early beta on this one. Hehe
By Edhy Rijo - 4/6/2010

Trent L. Taylor (02/14/2010)
...Long story short, we will be introducing the INLCUDED columns in a near release...




Hi Trent/Dustin,



Do we have an ETA on this feature? if not available soon, any suggestions on how could I add the creation of this type of indexes in the DDT?



PS

I am not using xCase anymore only the DDT and don't want to have to use 2 different tools to take care of the structure updates, so I need to rely only in the DDT for automated updates at customer site.