SQL Server: indexes with included columns

Hi All,


Does anybody have an idea why we cannot use the option for included columns with indexes? At our big installation we have some performance problems due to high volume tables. If you dive into the tools of SQL Server they give hints about poor performing queries and even give ways to improve the query sometimes. These look like improve the query with 80% by adding the index on table A with included columns b and c. Included columns are fields on the index that are only there on the final leave of the index, thus creating a much smaller index but providing sql just what it needs to create a speedy search.(normally the ref to the record is stored in the final leave of an index, now also the columns are there so sql doesn't have to do a lookup)

I can imagine that OutSystems wants to preserve cross db platform possibilities, but when the app is set to SQL only then it would be easy to make it possible to use the advanced features (i'm by the way pretty sure oracle must also have something like this)


regards,

Wim

Hi Wim,

I understand Oracle does not have the ability to include columns in indexes; including the columns as keys lets the index work as a covering index.

The only way I can imagine to do this would be to get direct access to the database, and add the indexes directly.  This would mean they do not appear in the OutSystems metadata though.

For cloud environments you can ask OutSystems for the access to your database to do this. If you have an on premise installation, you can arrange the permissions within your own organisation.

I recommend you add a like to this Idea for supporting include columns: https://www.outsystems.com/ideas/2405/index-included-columns

I know how to do it directly (when we have the access rights) but then with each publish we need to check all the tables for indexes, using the outsystems way would be a lot nicer :-) 

I did support the idea now, thx for the link

Funny Oracle doesn't support them, normally they steal the best idea's from each other and include them in a next release ;-) but off coarse Oracle also sell's servers and using included columns decreases needed space and so income so not good for the business model ;-)

Wim.

Hi Wim and Stuart,

It looks like you mean the Oracle 'function based indexes': https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS00505

Kind regards,
Eric

Nope, those still include all the info on all nodes, included columns are indexes with extra information stored in the last node, so they don't use space on the branches, just on the last node, so index is smaller but still holds all the info needed for a query so the actual record isn't needed anymore.