Microsoft SQL Server - Index definitions (Business Rule)

These business rules check the custom indexes of a model object on Microsoft SQL Server. The errors ID-32000, ID-32001, ID-32003, ID-32004, ID-32005 and ID-32008 block generation in the client; the warning ID-32002 does not. Each entry names the index setting that raises the message.

Messages

Each message names the model object and its type in place of <model object name> and <type>. Index types and columns are set in Edit an Index. The Generate Artifacts page lists the messages without the rule ID.

Rowstore index without index columns

The client shows an error with this text:

Model object '<model object name>' of type '<type>' has a rowstore
index without the mandatory index columns defined. Please configure
the index columns.

On the model object card, point to the error icon to see this message with the rule ID: Error ID-32000.

Cause: a Clustered Rowstore Index or Non-clustered Rowstore Index has no Index Columns.
Fix: select the Index Columns of the index.

More than one clustered index

The client shows an error with this text:

Model object '<model object name>' of type '<type>' has more than
one clustered index defined. Only one clustered index is allowed per
table.

On the model object card, point to the error icon to see this message with the rule ID: Error ID-32001.

Cause: a target object has more than one index of the types Clustered Rowstore Index and Clustered Columnstore Index.
Fix: keep one clustered index, and change the others to a non-clustered type or delete them.

Clustered index next to the default indexes

The client shows a warning with this text:

Model object '<model object name>' of type '<type>' has a clustered
index defined in addition to the default indexes. Only one clustered
index is allowed per table. Check for incompatibilities with the
default indexes.

On the model object card, point to the warning icon to see this message with the rule ID: Warning ID-32002.

Cause: a target object has a custom clustered index while its default indexes are switched on.
Fix: switch the default indexes of the target object off, see Understand Indexes overview, or use a non-clustered index type.

More than one columnstore index

The client shows an error with this text:

Model object '<model object name>' of type '<type>' has more than
one columnstore index defined. Only one columnstore index is allowed
per table.

On the model object card, point to the error icon to see this message with the rule ID: Error ID-32003.

Cause: a target object has more than one index of the types Clustered Columnstore Index and Non-clustered Columnstore Index.
Fix: keep one columnstore index, and delete the others.

Column in both index columns and included columns

The client shows an error with this text:

Model object '<model object name>' of type '<type>' has a rowstore
index defined where a column is used in both the index and the
included columns. Each column can only appear once in either the
index or the included columns.

On the model object card, point to the error icon to see this message with the rule ID: Error ID-32004.

Cause: a Non-clustered Rowstore Index has a column in both Index Columns and Included Columns.
Fix: remove the column from one of the two lists.

Index column with an unsupported data type

The client shows an error with this text:

Model object '<model object name>' of type '<type>' has a rowstore
index defined that uses index columns with incompatible data types.

On the model object card, point to the error icon to see this message with the rule ID: Error ID-32005.

Cause: an Index Column of a rowstore index has the data type image, nvarchar(max), varchar(max), varbinary(max) or sql_variant.
Fix: choose other Index Columns, or give the term a data type with a length.

Non-clustered columnstore index without included columns

The client shows an error with this text:

Model object '<model object name>' of type '<type>' has a
nonclustered columnstore index without the mandatory included columns
defined. Please configure the included columns.

On the model object card, point to the error icon to see this message with the rule ID: Error ID-32008.

Cause: a Non-clustered Columnstore Index has no Included Columns.
Fix: select the Included Columns of the index.

Available since Generator 2.0.

Check the result

The error or warning icon is gone from the model object. After an error is fixed, generation is no longer blocked by it.

Example

In a Data Mart project on Microsoft SQL Server, the Non-clustered Rowstore Index UX_BRANCH_NAME of the Mart Composite non_clust_rowstore_unique loses its Index Columns. The client shows:

Model object 'non_clust_rowstore_unique' of type 'Mart Composite' has
a rowstore index without the mandatory index columns defined. Please
configure the index columns.

Related articles

• Reference: Add an Index, Edit an Index
• See also: Understand Indexes overview, Setting ignored for a Virtual model object (Business Rule), Understand warnings and errors