Idea - More index options

Back to Ideas

Follow Idea

Justin James

MVP

More index options

On our Radar

Database

We need fine-grained control over indexes. There are things like "fill factor" which we can set through SQL Management Studio, but we cannot set through Service Studio. This is a problem for 3 reasons:

1. Who knows when a deploy will wipe it out?
2. Customers on the OutSystems cloud do not have access to the DB to add it.
3. We love to manage everything through proper OutSystems tools!

J.Ja

18 Aug 2014

Hans Dollen

here is the first comment.

options:
- function based indexes

and advanced options
- setting the order of columns, but now including the Tenant Identifier.

29 Sep 2015

Bill Brady

Staff

How about a free-text area within the index management pop-up in Service Studio, that would simply allow you to enter the SQL required to create the desired index?

29 Mar 2016

Justin James

MVP

Bill - Mainly? I'm not sure how well that would work out in terms of writing it out. It's a good idea.

J.Ja?

29 Mar 2016

Bill Brady

Staff

Yeah, it pretty much relies on the person creating the index to get their syntax right, if thats what you mean.

I can't take credit for the idea, though, it was a suggestion from one of the project leads at Intel. Just helping me bang the drum ;)

Bill

30 Mar 2016

Justin James

MVP

Intel is using OutSystems now? No one tells me anything anymore!?!? :) J.Ja

30 Mar 2016

Bill Brady

Staff

Sometimes I feel the same way (until I make a bit of noise ;)
Happy to catch you up J.Ja! Ping me at bill.brady@outsystems.com

30 Mar 2016

Mark Jurkovich

Needed a filtered index today. Can't do it without going into the database and adding it manually. More options please!

22 Feb 2019

Ricardo Mata

This feels like a good addition to the platform. Our clients' DBAs occasionally suggest we create idexes with included columns for performance reasons, and we have to throw back the ball at them so they create the index directly in the database, which often means losing track of unmanaged resources.

29 Mar 2019

Wim van den Brink

I think included columns are one of the best options to add, it saves db space as opposed to including them in the index fields and it will speed up searches a lot as the index will be much smaller.

12 Jul 2019

Wim van den Brink

It's a pity this important idea gets so few likes, not many people are using high volume or high speeds databases maybe? With all the emphasis from OutSystems on getting the big customers in maybe this should get more attention

12 Jul 2019

Justin James

MVP

Merged this idea with 'Filtered index and unique filtered index functionality' (created on 08 Jan 2020 10:41:32 by Matthew Stokes)

17 Jan 2020

Matthew Stokes

Hi, would it be possible to add "filtered" index functionality to the platform?

A few benefits off the top of my head would be that this will allow us to easily create unique indexes on only active records (for example). This will also help in keeping indexes smaller and therefore save on the cost of maintenance, insert, update and query faster.

It will allow you to keep certain records unique - but exclude nulls i.e. You want to ensure all your customer email addresses are unique - but you want to be able to have more than 1 customer without an email address.

I know MSSQL has had this functionality for some time, and after a brief search it looks like the other database platforms have similar ways of achieving the same goal.

It would be very cool to be able to implement such functionality from the entity indexes tab!

This comment was:
- originally posted on idea 'Filtered index and unique filtered index functionality' (created on 08 Jan 2020 by Matthew Stokes)
- merged to idea 'More index options' on 17 Jan 2020 03:05:55 by Justin James

08 Jan 2020

Justin James

MVP

Would it make sense to merge this idea with this one:

https://www.outsystems.com/ideas/1683/

J.Ja

10 Jan 2020

Matthew Stokes

Hi Justin,

I have no objections to merging the ideas - my only concern is that not much has been happening on that idea for +-5 years? Perhaps too many requests on one idea?

Up to you though.

Thanks.

16 Jan 2020

Justin James

MVP

The advantage to the merge is that it combines the votes into one bucket, to show more support for the idea. :)

J.Ja

17 Jan 2020

Justin James

MVP

Merged this idea with 'Index Included Columns' (created on 23 Apr 2016 22:34:53 by Philip Miller)

10 May 2021

Philip Miller

It would be nice to be able to specify included columns in an index for MSSQL databases.

This comment was:
- originally posted on idea 'Index Included Columns' (created on 23 Apr 2016 by Philip Miller)
- merged to idea 'More index options' on 10 May 2021 15:05:58 by Justin James

23 Apr 2016

Justin James

MVP

Why can't you do it now? You can add multiple columns to the index? Is there something I'm missing (I'm not an index expert)?

J.Ja

This comment was:
- originally posted on idea 'Index Included Columns' (created on 23 Apr 2016 by Philip Miller)
- merged to idea 'More index options' on 10 May 2021 15:05:58 by Justin James

24 Apr 2016

Philip Miller

What I mean by included columns is this. They would not go into the b-tree structure of the index, so no overhead on writing to the database, but would be included in the linked list at the bottom so you could access that information without having to do a table read.

As an example. Say you have a table that shows Name and description and a combo box to select a filter on something like a type. You could create an index on type and have name and description as included columns. The execution plan would select that index on type to scan since its part of the where clause of the query. Name and description would be included in the leaf nodes of that index so the query could return those two columns without having to access anything other than the index.

If there was just the type index with no included columns, the database server would get to the bottom of the index b-tree and have a bunch of rowids which it would use to read the database rows to get the name and description values. Depending on how the database is clustered it could involve mutliple read operations because it can't get everything at once.

This comment was:
- originally posted on idea 'Index Included Columns' (created on 23 Apr 2016 by Philip Miller)
- merged to idea 'More index options' on 10 May 2021 15:05:58 by Justin James

06 May 2016

Justin James

MVP

Philip -

Got it!

I think this should all be part of the bigger "we need more advanced indexing options" discussion that's been going on:
https://www.outsystems.com/ideas/1683/more-index-options

J.Ja

This comment was:
- originally posted on idea 'Index Included Columns' (created on 23 Apr 2016 by Philip Miller)
- merged to idea 'More index options' on 10 May 2021 15:05:58 by Justin James

06 May 2016

Justin James

MVP

Merged this idea with 'Create database index with INCLUDE attributes' (created on 08 May 2021 09:39:06 by José Gonçalves)

This comment was:
- originally posted on idea 'Index Included Columns' (created on 23 Apr 2016 by Philip Miller)
- merged to idea 'More index options' on 10 May 2021 15:05:58 by Justin James

10 May 2021

José Gonçalves

Creating an index in service studio doesn't have the option to use the INCLUDE option limiting the performance of the index.

For reference:

Indexes with included columns, what's the difference?

This comment was:
- originally posted on idea 'Create database index with INCLUDE attributes' (created on 08 May 2021 by José Gonçalves)
- merged to idea 'Index Included Columns' on 10 May 2021 15:04:50 by Justin James

This comment was:
- originally posted on idea 'Index Included Columns' (created on 23 Apr 2016 by Philip Miller)
- merged to idea 'More index options' on 10 May 2021 15:05:58 by Justin James

08 May 2021

José Gonçalves

2016... hope lost :\

10 May 2021

Zak Counides

I think the root problem in regards to the INCLUDE on tsql/mssql is that there is no equivalent for Oracle. (you kinda just add all the fields to the index.) And in order to support both, and keep the product stack simple enough for citizen development, they are trying to avoid platform specific features.... (I might be slightly wrong on the Oracle side, been a while since I looked at them.)

11 May 2021

José Gonçalves

Good point Zak, Oracle doesn't have the INCLUDE option.

However it wouldn't be a rocket science to allow only for SQL Server scenarios.

11 May 2021

Justin James

MVP

There are a few other places where OutSystems has has functionality that wasn't supported based on runtime, it's not a big deal. Caching, for example, was never supported on the Java version and it wasn't a big deal. INCLUDE indexing will be super edge case, and it's a 1% feature, and most OS shops use SQL Server anyways, so if 10% of the 1% can't use it, no big deal. Just let the publish system throw a warning and move on with life.

J.Ja

11 May 2021

Ellery Mendoza

Hi, supporting the request to have the option INCLUDE in creating index.

We are in the OutSystems Cloud so creating the index with included fields ourselves is not an option for us.

Thank you.

03 Jan 2022

Paulo Jadaugy

Hi,

Supporting this idea, we shouldn't be limited when creating missing indexes.

04 Jul 2024

Jorge Trujillo

Hello

Supporting the idea of adding the Include clause. we shouldn't be limited when creating recommended indexes. Our project is also cloud based and Outsystems support refuses to help us adding the index directly into the DB (without using service studio). Adding the include clause makes a difference with performance.

With INCLUDE: If all columns required by the SELECT statement are either key columns or included columns, the query can be satisfied entirely by the index. The database engine finds the rows using the index keys and retrieves the requested non-key data directly from the leaf level, completely avoiding the need to access the base table.

04 Sep 2025