Index with included columns on Microsoft SQL Server

We are working for a customer to add the missing indexes to a table in the Uniface model.
In the Microsoft SQL Server database, an index contains included columns. How can we specify these fields in Uniface?

The definition of the index:
CREATE NONCLUSTERED INDEX [inz_I6] ON [dbo].[inz]
(
[ros_vrs] ASC,
[ros_sts] ASC,
[wek_cde_inz] ASC,
[fak1] ASC
)
INCLUDE (
[dtm_inz],
[ros_cde],
[rgl_num_inz],
[log_vlg_num_inz]) ON [idx]
GO

We expect the Uniface model to specify all fields for the index in question.
We are currently using Rocket Uniface 10.3.02 (patch level 062) and MSS 2016.

Does anyone have an idea how to model this in the Uniface model?

------------------------------
Richard de Vries
Sogeti Nederland B.V.
Deventer NL
------------------------------

Hi Richard,

I feel this is not possible as of today directly from current Uniface model; it's only possible to define the first part (the usual part of the index). Anyhow you have two possible paths to follow to implement the full "create index" instruction, including those "included columns":

1) Just applying it manually to your SQL Server
2) Create by yourself an extension to Uniface repository to maintain the list of the fields to be included and build a functionality to revise the standard SQL generated from IDE.

In both cases Uniface will use the adapted SQL Server schema at runtime.

What I've never fully checked before is:
1) Is the "include" part of the "create index" instruction supported from latest ANSI SQL?
2) How many DBMS supported from Uniface has that feature available? (I only know the answer it's YES for SQLServer and PostgreSQL).

HERE a nice explanation of included columns in an index (dated 2019-04 probably NOT fully up to date!).

Hope it helps.

Regards,
Gianni

------------------------------
Gianni Sandigliano
IT
------------------------------

Hi Richard,

I feel this is not possible as of today directly from current Uniface model; it's only possible to define the first part (the usual part of the index). Anyhow you have two possible paths to follow to implement the full "create index" instruction, including those "included columns":

1) Just applying it manually to your SQL Server
2) Create by yourself an extension to Uniface repository to maintain the list of the fields to be included and build a functionality to revise the standard SQL generated from IDE.

In both cases Uniface will use the adapted SQL Server schema at runtime.

What I've never fully checked before is:
1) Is the "include" part of the "create index" instruction supported from latest ANSI SQL?
2) How many DBMS supported from Uniface has that feature available? (I only know the answer it's YES for SQLServer and PostgreSQL).

HERE a nice explanation of included columns in an index (dated 2019-04 probably NOT fully up to date!).

Hope it helps.

Regards,
Gianni

------------------------------
Gianni Sandigliano
IT
------------------------------

Hi Gianni,

Thanks for your response.

The mentioned index has already been created in the database. We do not want to create it from the Uniface model. We want to get the model in Uniface in line with the database model. The application is running for over six years. The DBA team from the customer has updated and added indexes for performance reasons. Only this has not been added to the Uniface model.

We believe that we need to create the index with all fields/columns mentioned. We are not sure about this. It might be that only the first part should be used in the Uniface model. We are hoping some one else has some experience with this kind of indexes.

------------------------------
Richard de Vries
Sogeti Nederland B.V.
Deventer NL
------------------------------

We are working for a customer to add the missing indexes to a table in the Uniface model.
In the Microsoft SQL Server database, an index contains included columns. How can we specify these fields in Uniface?

The definition of the index:
CREATE NONCLUSTERED INDEX [inz_I6] ON [dbo].[inz]
(
[ros_vrs] ASC,
[ros_sts] ASC,
[wek_cde_inz] ASC,
[fak1] ASC
)
INCLUDE (
[dtm_inz],
[ros_cde],
[rgl_num_inz],
[log_vlg_num_inz]) ON [idx]
GO

We expect the Uniface model to specify all fields for the index in question.
We are currently using Rocket Uniface 10.3.02 (patch level 062) and MSS 2016.

Does anyone have an idea how to model this in the Uniface model?

------------------------------
Richard de Vries
Sogeti Nederland B.V.
Deventer NL
------------------------------

Hi Richard,

I'm not familiar with the concept of included columns (so probably not the safest answer to take) but from the documentation:
Create indexes with included columns - SQL Server | Microsoft Docs
It sounds like its just an extension of the SQL Server index functionality to include columns you couldn't normally included under Microsoft's criteria of an index column. Uniface's definition of the index is probably looser on what can be in an index and, outside of SQL generation, is really only used to determine when to explicitly USE that index.

That means if uniface sees a profile with all of those fields it will say "Fetch the data using this index" to the database rather than just "Fetch this data" without an index. If you want the index to be used in the right circumstances it should probably be declared with all of the columns. It would just be a flat list rather than in two parts but Uniface wont know the difference anyway.

You would have to be careful if you were to need to generate the table from Uniface as it would not come out the same (and the SQL would probably fail due to the columns being listed incorrectly) but as you are looking at an existing table this just needs to be noted and the SQL rewritten if it gets generated later.

------------------------------
Mike Porter
Equiniti Group
Belfast, UK
------------------------------

Hi Richard,

I'm not familiar with the concept of included columns (so probably not the safest answer to take) but from the documentation:
Create indexes with included columns - SQL Server | Microsoft Docs
It sounds like its just an extension of the SQL Server index functionality to include columns you couldn't normally included under Microsoft's criteria of an index column. Uniface's definition of the index is probably looser on what can be in an index and, outside of SQL generation, is really only used to determine when to explicitly USE that index.

That means if uniface sees a profile with all of those fields it will say "Fetch the data using this index" to the database rather than just "Fetch this data" without an index. If you want the index to be used in the right circumstances it should probably be declared with all of the columns. It would just be a flat list rather than in two parts but Uniface wont know the difference anyway.

You would have to be careful if you were to need to generate the table from Uniface as it would not come out the same (and the SQL would probably fail due to the columns being listed incorrectly) but as you are looking at an existing table this just needs to be noted and the SQL rewritten if it gets generated later.

------------------------------
Mike Porter
Equiniti Group
Belfast, UK
------------------------------

If uniface only suggests the use of the index where a profile contains all of the fields in the uniface model of the index, then I'd specifically NOT mention the included columns as well. If you want to know them from the model, put them in the comments field. 
Included columns are used to minimise the number of disk reads, as the data most likely to be used with the index is fetched on the same read as the index itself, and therefore the read on the main data file is not necessary, but to get the best use out of this then the components you expect to use the index would want to have a limited field list containing only fields which are in the index or included columns. 


------------------------------
Iain Sharp
Head of Technical Services
Pci Systems Ltd
Sheffield GB
------------------------------

If uniface only suggests the use of the index where a profile contains all of the fields in the uniface model of the index, then I'd specifically NOT mention the included columns as well. If you want to know them from the model, put them in the comments field. 
Included columns are used to minimise the number of disk reads, as the data most likely to be used with the index is fetched on the same read as the index itself, and therefore the read on the main data file is not necessary, but to get the best use out of this then the components you expect to use the index would want to have a limited field list containing only fields which are in the index or included columns. 


------------------------------
Iain Sharp
Head of Technical Services
Pci Systems Ltd
Sheffield GB
------------------------------

Couldn't it actually get the search results wrong then though? As I recall (and maybe its changed or I'm wrong) Uniface will only pass fields that are in the  modelled index into the generated SQL and will therefore ignore profiles in the included columns resulting in more results back than you want. At that point actually modelling the index would be detrimental to the product and not a good idea to model it at all. Let the database decide which index to use based on the profile passed in.
I'm not 100% sure as its been a while and I'm not in a position to test anything at the moment so take this with a pinch (bucket) of salt.

------------------------------
Mike Porter
Equiniti Group
Belfast, UK
------------------------------

Couldn't it actually get the search results wrong then though? As I recall (and maybe its changed or I'm wrong) Uniface will only pass fields that are in the  modelled index into the generated SQL and will therefore ignore profiles in the included columns resulting in more results back than you want. At that point actually modelling the index would be detrimental to the product and not a good idea to model it at all. Let the database decide which index to use based on the profile passed in.
I'm not 100% sure as its been a while and I'm not in a position to test anything at the moment so take this with a pinch (bucket) of salt.

------------------------------
Mike Porter
Equiniti Group
Belfast, UK
------------------------------

I would have thought that profile fields not in the modelled index would cause uniface to not suggest that index in the sql, but to still pass the full profile. 
SQL Server may then optimise to use that index, to allow for a seek on the profile fields which are indexed and then a filter on the fields which are included.

------------------------------
Iain Sharp
Head of Technical Services
Pci Systems Ltd
Sheffield GB
------------------------------