Application Engine

Creating, modifying and deleting indexes on Staging tables

New B# functions to create, modify, and remove indexes on SQL tables have been introduced. The index-related functions are supported only for the Staging database connection. View Relational Objects and Edit Relational Objects modeling permission is required.

These functions are used to create unique/nonunique, clustered/non-clustered indexes on existing tables:

• void SQLCreateIndex(SQLConnection conn, string tableName, string indexName, SQLIndexTypetype, StringList keyColumns)
• void SQLCreateIndex(SQLConnection conn, string tableName, string indexName, SQLIndexTypetype, StringList keyColumns, VariantList keyColumnsSorting)
• void SQLCreateIndex(SQLConnection conn, string tableName, string indexName, SQLIndexTypetype, StringList keyColumns, VariantList keyColumnsSorting, bool dropExisting, StringList includedColumns)

This function is used to query existing indexes:

StringList SQLGetIndexNames(SQLConnection conn, string tableName)

This function is used to query the type of an existing index:

SQLIndexType SQLGetIndexType(SQLConnection conn, string tableName, string indexName)

This function modifies an existing table by rebuilding or reorganizing the index:

void SQLAlterIndex(SQLConnection conn, string tableName, string indexName, SQLAlterIndexOpalterIndexOp)

This function removes an existing index:

void SQLDropIndex(SQLConnection conn, string tableName, string indexName, bool ifExists)

These functions are complemented by new index-related enumerations for a better user experience when writing processes. This list shows the new index-related enumerations:

• SQLIndexType:
  • SQLIndexType.None - nonunique nonclustered index
  • SQLIndexType.Clustered - clustered index
  • SQLIndexType.Unique - unique index
• SQLAlterIndexOp:
  • SQLAlterIndexOp.Reorganize
  • SQLAlterIndexOp.Rebuild