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
------------------------------
Original Message:
Sent: 06-09-2022 11:30
From: Richard de Vries
Subject: 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
------------------------------