Creating SQL Server Non-Clustered Indexes with Included Columns (INCLUDE) in Maximo

SQL Server supports non-clustered indexes with included columns using the INCLUDE keyword. This feature can significantly improve query performance by covering all columns while keeping the index key size compact.

Before looking at how this applies to IBM Maximo, let's briefly review the different types of indexes.

Clustered Index - It determines the physical order of rows in a table. It's typically created on the table's primary key, and only one clustered index can exist per table.

Non Clustered Index - A non-clustered index stores a separate structure from the table data and is commonly created on columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses to speed up query performance.

Non Clustered Index including non-key columns - A non-clustered index can also include non-key columns using the INCLUDE keyword. These columns are stored only at the leaf level of the index and are not part of the index key.

This enables SQL Server to satisfy a query directly from the index without accessing the underlying table or clustered index.

Using included columns offers several advantages:

• Included columns are not counted toward SQL Server's index key size or key column limits
• Allows columns with data types that cannot be used as index keys
• Improves query performance by reducing key lookups and disk I/O
• Keeps the index key size small while still covering more queries   

How are Included Columns used in Maximo ? 

SQL Server INCLUDE columns are non-key (leaf-level) columns and are not always fully parsed by Maximo during reverse-engineering. As a result, such indexes can not be imported or may fail during refresh.

Attempting to import these indexes into Maximo using "Refresh Index Tables" option in Database Configuration may result in errors. 

Maximo Database Configuration manages indexes using its internal metadata model. It is designed primarily for standard index key columns. 

The recommended approach in Maximo is to Create non-clustered index using INCLUDE keyword in SQL Server from backend rather than through Maximo Database configuration application. It will function normally and be used by the query optimizer can locate all the columns in the index.

No import into Maximo is required

If a table is rebuilt during Database configuration (configdb) process, indexes that are not defined within Maximo may need to be manually recreated.

Example, Suppose several Maximo queries retrieve work orders using the siteid and status columns while returning additional information.

SELECT wonum, description, assetnum, location, reportdate
FROM workorder
WHERE siteid = 'BEDFORD'
AND status in ('WAPPR','APPR');

CREATE NONCLUSTERED INDEX IX_WORKORDER_SITE_STATUS
ON dbo.WORKORDER (siteid, status)
INCLUDE (wonum, description, assetnum, location, reportdate);

In this example, 

• siteid and status are the index key columns because they are used in the WHERE clause.
• The remaining columns are added as included columns, allowing SQL Server to satisfy the query entirely from the index without performing additional lookups

Best Practices

When designing non-clustered indexes with included columns:

• Start by identifying slow-running queries.
• Design the index key using columns referenced in the WHERE, JOIN, ORDER BY, and GROUP BY clauses.
• Add columns returned in the SELECT list as included columns whenever they are not needed for searching or sorting.
• Keep the index key as small as possible for better performance and lower maintenance costs.
• Thumb Rule: Use key columns for filtering and searching, and use included columns for covering the query.

An Exception in Maximo

In testing, Maximo appears able to import indexes that use the INCLUDE keyword when only a single included column is specified.

CREATE NONCLUSTERED INDEX IX_WORKPERIOD 
ON dbo.workperiod (orgid,shiftnum,calnum) 
INCLUDE (workperiodid);

However, indexes containing multiple included columns may fail to import or may not be correctly interpreted by Database Configuration. Therefore, backend creation remains the recommended approach.

Conclusion: 

SQL Server's INCLUDE clause is an excellent way to create efficient covering indexes while keeping index keys compact. Although Maximo does not fully support managing these indexes through Database Configuration, they can be safely created and maintained directly in SQL Server.

References:
microsoft-relational-dbindexes-with-included-columns