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:

DEFINITION

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:

SQL

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:

DEFINITION

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:

SQL

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:

DEFINITION

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:

SQL

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:

DEFINITION

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:

SQL

CREATE TABLE pub.F_TRIP (
  ...
);
CREATE INDEX IDX_F_TRIP_PICKUP_DT ON pub.F_TRIP (lpep_pickup_datetime ASC) INCLUDE(category,pickup_point);