Working with Functional Indexes

PeopleSoft uses computed columns that allow the creation of functional indexes. A functional index is an index created to keep uniqueness in a table when the number of keys exceeds the SQL Server limit, which is a maximum of 16 key columns for an index. What makes a functional index special is that it's required only when the number of key columns exceeds the SQL Server limit.

PeopleSoft implements the functional index by creating an index over a computed column. The computed column MSSCONCATCOL is the sum of all the key columns required to keep uniqueness.

Note:

If your database has tables containing the MSSCONCATCOL column, you will see SQL to alter the tables and re-create their associated indexes, even though the underlying tables and indexes may not have changed.

In order to create indexes on computed columns, SQL Server requires the Quoted Identifier option to be enabled in the database. This is the default configuration, but this option could be overridden as a connection option from any client. If you are using Query Analyzer to run SQL scripts, look at Tools, Options, Connection Properties on your Query Analyzer menu and make sure the Quoted Identifier option is selected, which will activate it for that particular connection.

Another important option that needs to be enabled to operate computed columns is the database property Arithabort. Make sure this option is enabled for your PeopleSoft database.

Note:

Both Quoted Identifier and Arithabort are explicitly set during installation automatically by the Database Configuration Wizard or when running the script, createdb.sql, at the database installation.