SECONDARY_INDEX
Target database: Azure SQL Database, Azure Synapse SQL, PostgreSQL
Defining a SECONDARY_INDEX for an entity creates a non-clustered index in the listed target database management systems.
See also:
Usage
Pick one or several attributes and their sorting directions to form the index, and give a name to the index. Add index options if needed.
When index options field contains keywords: WITH, INCLUDE or WHERE
the index options -field is not processed and will be used as part of the INDEX creation clause as given
Otherwise the index options will be placed inside WITH-clause.
Notes
Check Export SQL/Entity SQL after defining a SECONDARY_INDEX to see generated DDL.
Examples
Defining a secondary index in Azure SQL Database
Key definition example 1:
KEY NAME: IDX_F_TRIP_PICKUP_DT
KEY TYPE: SECONDARY_INDEX
ATTRIBUTE 1: lpep_pickup_datetime
SORT DIRECTION 1: ASC
INDEX OPTIONS (optional): ONLINE = ON, MAXDOP = 1, RESUMABLE = ON
DDL generated by Agile Data Engine:
CREATE TABLE [pub].[F_TRIP] (
...
);
CREATE INDEX [IDX_F_TRIP_PICKUP_DT] ON [pub].[F_TRIP] ([lpep_pickup_datetime] ASC) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);
Key definition example 2:
KEY NAME: IDX_F_TRIP_PICKUP_DT
KEY TYPE: SECONDARY_INDEX
ATTRIBUTE 1: lpep_pickup_datetime
SORT DIRECTION 1: ASC
INDEX OPTIONS (optional): INCLUDE([category],[pickup_point])
DDL generated by Agile Data Engine:
CREATE TABLE [pub].[F_TRIP] (
...
);
CREATE INDEX [IDX_F_TRIP_PICKUP_DT] ON [pub].[F_TRIP] ([lpep_pickup_datetime] ASC) INCLUDE([category],[pickup_point]);
Defining a secondary index in PostgreSQL
Key definition example 1:
KEY NAME: IDX_F_TRIP_PICKUP_DT
KEY TYPE: SECONDARY_INDEX
ATTRIBUTE 1: lpep_pickup_datetime
SORT DIRECTION 1: ASC
INDEX OPTIONS (optional): fillfactor = 70
DDL generated by Agile Data Engine:
CREATE TABLE pub.F_TRIP (
...
);
CREATE INDEX IDX_F_TRIP_PICKUP_DT ON pub.F_TRIP (lpep_pickup_datetime ASC) WITH (fillfactor = 70);
Key definition example 2:
KEY NAME: IDX_F_TRIP_PICKUP_DT
KEY TYPE: SECONDARY_INDEX
ATTRIBUTE 1: lpep_pickup_datetime
SORT DIRECTION 1: ASC
INDEX OPTIONS (optional): INCLUDE(category,pickup_point)
DDL generated by Agile Data Engine:
CREATE TABLE pub.F_TRIP (
...
);
CREATE INDEX IDX_F_TRIP_PICKUP_DT ON pub.F_TRIP (lpep_pickup_datetime ASC) INCLUDE(category,pickup_point);