AX1578

About table index schemes

All data and reference tables are assigned to a table index scheme that determines how each table is indexed within the Axiom Software database. In certain situations, using a specialized index scheme can improve performance when querying data from the table or saving data to the table.

IMPORTANT: The table index scheme is an advanced feature that requires a good understanding of data structures and database management. You should only change the index scheme as instructed by your implementation consultant or Axiom Support.

Axiom Software provides the following index schemes:

  • Default: This option applies the default index scheme suitable for most tables. Tables use a clustered row store index.

  • Hybrid: This option combines the default index scheme with a non-clustered column store index. This option may improve performance on larger tables when the Large Table scheme cannot be used due to its restrictions.

  • Large Table: This option applies an index scheme suitable for tables that contain very large sets of data—from hundreds of millions to billions of rows. Tables use a clustered column store index. Use of this option introduces a set of restrictions on how the table and its data can be managed. Make sure you understand these restrictions before enabling this option.

Index schemes are specified in the table properties. You can set the index scheme when creating a table, or when editing a table. However, if you want to use the Large Table index scheme, this must be specified when the table is created. After a table is created, it is not possible to change Default or Hybrid to Large Table, or vice versa. If necessary, you can clone the table and change the index scheme as part of the clone.

Index Scheme in the table properties

NOTE: In previous releases, the Large Table index scheme was managed using a separate table classification of Large Data tables. When upgrading to 2018.1.55 or higher, all Large Data tables are converted to Data tables using the Large Table index scheme. The table should behave the same before and after the upgrade, the only change is in how the table is classified.

System requirements

The Default index scheme can be used with any supported version of Microsoft SQL Server. The other schemes have greater system requirements:

  • Hybrid: Requires SQL Server 2014 or higher
  • Large Table: Requires SQL Server 2016 or higher

Before enabling these features, you should make sure that your system meets these requirements by consulting with your database administrator for on-premise systems, or with Axiom Support for Cloud Service systems.

Table structure restrictions when using Large Table or Hybrid indexing

The following table structure restrictions apply to the Large Table and Hybrid index schemes.

Hybrid

When using the Hybrid index scheme, the table cannot contain a String column that is set to maximum (unlimited) width.

LargeTable

When using the Large Table index scheme, the following restrictions apply:

  • Once a large table has data, it is not possible to add or remove columns in the table. The overall column structure is fixed.
  • Identity columns are not allowed in large tables.
  • Large tables do not support column aliases, column sequences, calculated fields, or data conversions.

Data restrictions when using Large Table indexing

Tables that use the Large Table index scheme are restricted in how data can be saved to the table. Only the following features can be used:

  • Import utilities
  • Copy Table Data

Save Type 1 and Open Table in Spreadsheet cannot be used to save data to large tables. Open Table in Spreadsheet can only be used as read-only. Generally speaking, once the data is imported into the large table, it should not be necessary to further manipulate it within the table.

Zeroing large table data using the Zero/Delete Table Data feature is not allowed. The option is suppressed if the currently selected table uses the LargeTable index scheme. However, deleting large table data using Zero/Delete Table Data is allowed.

The data in large tables cannot be audited. The table property Audited defaults to False and cannot be changed. Generally speaking, large tables should have limited auditing needs because the data is imported from other sources and not manipulated within the large table itself.

Additionally, the RecordModifiedBy and RecordModifiedDTM columns are not available for large tables. Instead, a single column of ActivityID is available to record the associated audit activity. However, currently there is no easy way to look up an audit activity by ID number.

See also