Custom index naming policies

Advisor checks your index names against a naming policy: a small script that generates the name it expects for each index, so it can flag any that don't match. Set the policy from Settings, Advisor, Index Naming, where you choose Default Policy 1, Default Policy 2 or Custom Policy, and see a live preview of what each produces against five sample indexes (a primary key, a clustered index, a unique index, an ordinary index, and an index with included columns).

Choosing Custom Policy adds an Edit Custom Policy button, which opens a script editor with its own Validate button (it refreshes the same preview), a Restore Default button, and a link to the documentation. If you haven't written a custom script yet, the editor starts you from a blank, commented template rather than an empty box.

What the script gets and returns

Each time Advisor needs a name, it runs your script with these read-only inputs: _tableName, _isPrimaryKey, _isClusteredIndex, _isColumnstoreIndex, _isNonClusteredColumnStoreIndex, _isUniqueIndex, _isUniqueConstraint, _indexedColumns and _includedColumns (both string arrays), and _indexDescription (see below). Your script sets a variable called _indexName and returns it.

Six helper functions are available: len(x), uppercase(x), lowercase(x), join(x, y), replace(x, y, z) and substring(x, start, length), where start is zero-based.

A worked example

This is Default Policy 1, unedited:

var _indexName;

if (_isPrimaryKey) {
    _indexName = "PK_";
} else if (_isClusteredIndex && _isUniqueIndex) {
    _indexName = "UCIX_";
} else if (_isClusteredIndex && !_isUniqueIndex) {
    _indexName = "CIX_";
} else if (_isUniqueIndex) {
    _indexName = "AK_";
} else {
    _indexName = "IX_";
}

_indexName += _tableName;
_indexName += "_";
_indexName += join(_indexedColumns, "_");

var _len = len(_indexName);
if (_len > 128) {
    _indexName = substring(_indexName, 0, 128);
}

return _indexName;

It prefixes the name by what kind of index it is, appends the table name and the indexed columns joined with underscores, then truncates to SQL Server's 128-character name limit.

The <description> placeholder

If your naming convention includes a free-text description rather than being based purely on index properties, append _indexDescription to your generated name at the point the description belongs. Advisor treats the literal text <description> in the name it gets back as a placeholder: it checks only the fixed prefix and suffix around it, and accepts anything in between as your description.

The chosen policy, and any custom script, are stored in your local profile, not on the server, so they don't travel with you to another machine.