5.5.4. MS SQL Server Specifics

Microsoft SQL Server uses cluster indexes for tables.

By default, a cluster index is based on the table’s primary key, however UUID-type keys used by CUBA-applications are poorly suited for clustered index. That is why you should correctly select and create a clustered index for each table. The clustered index field should be relatively small and strictly ascending, following the general rules:

  • For most tables, you can use the CREATE_TS field. With this field, the records will be physically stored according to their creation order.

  • For composite entities, if reading prevails over writing, it makes sense to use an owner key. In this case, the records will be grouped by owner and their loading together with an owner will be faster.

  • For small (< 100 records) and rarely changing tables, the type of clustered index is not important and you can keep using ID.

  • For tables of the entities, inherited via JOINED strategy, that do not have the CREATE_TS field, you can create it manually with the current_timestamp parameter.

Example:

create table SALES_CUSTOMER (
    ID uniqueidentifier not null,
    CREATE_TS datetime,
    ...
    primary key nonclustered (ID)
)^

create clustered index IDX_SALES_CUSTOMER_CREATE_TS on SALES_CUSTOMER (CREATE_TS)^

Example of a composite entity:

create table SALES_ITEM (
    ID uniqueidentifier not null,
    CREATE_TS datetime,
    ...
    ORDER_ID uniqueidentifier,
    ...
    primary key nonclustered (ID),
    constraint FK_SALES_ITEM_ORDER foreign key (ORDER_ID) references SALES_ORDER(ID)
)^

create clustered index IDX_SALES_ITEM_ORDER on SALES_ITEM (ORDER_ID)^

Example of an inherited entity:

create table SALES_DOC (
    CARD_ID uniqueidentifier,
    CREATE_TS datetime default current_timestamp,
    NUMBER varchar(50),
    primary key nonclustered (CARD_ID),
    constraint FK_SALES_DOC_CARD foreign key (CARD_ID) references WF_CARD (ID)
)^

create clustered index IDX_SALES_DOC_CREATE_TS on SALES_DOC (CREATE_TS)^

create index IDX_SALES_DOC_CARD on SALES_DOC (CARD_ID)^
Prev  Up  Next
  Home