Sql-server – Index creation with multiple columns – should I combine as many as possible

Computed Columns can be stored in the data page in one of two ways. Either by creating them as PERSISTED or by including them in the clustered index definition.

If they are included in the clustered index definition then even if the columns are not marked as PERSISTED then the values will still be stored in each row. These index key values will additionally be stored in the upper level pages.

If the computed column is imprecise (e.g. float) or not verifiable as deterministic (e.g. CLR functions) then it is a requirement for the column to be marked as PERSISTED in order to be made part of an index key.

So to give an example

CREATE TABLE T
(
A INT,
C1 AS REPLICATE(CHAR(A),100) PERSISTED,
C2 AS REPLICATE(CHAR(A),200),
C3 AS CAST(A AS FLOAT) PERSISTED,
C4 AS CAST(A + 1 AS FLOAT)
)

CREATE UNIQUE CLUSTERED INDEX IX ON T(C2,C3)

• C1 will be stored in just the data page rows as it is marked as PERSISTED but not indexed.
• C2 will be stored in both the rows on the data page and the index higher levels as it is an index key column.
• C3 will be stored as for C2. As it is imprecise it is a requirement to mark it as PERSISTED however.
• C4 won't be stored anywhere as it is neither marked as PERSISTED nor indexed.

Similarly all computed columns referenced in non clustered index definitions as key columns need to be stored at all levels of the index as they are part of the index key. There is the same requirement regarding precise/deterministic results.

Fails

CREATE NONCLUSTERED INDEX IX2 ON T(A,C4)

With the error.

Cannot create index or statistics 'IX2' on table 'T' because the computed column 'C4' is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

To include it as part of the non clustered index key it must also be stored in the clustered index data pages. However

Succeeds.

CREATE NONCLUSTERED INDEX IX2 ON T(A) INCLUDE (C4)

Computed columns that are only INCLUDEd columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.

Having many indices requires SQL Server to maintain and update many indices for each insert, delete or update statement. So having less indices is generally better.

However, when you have a compound index (made up of multiple columns), that only helps if you use/specify the n left-most columns in your query.

So if you have an index on (City, LastName, FirstName) for an address table, that index might be used if

• you have all three columns in your WHERE clause
• you specify WHERE City = 'London' in your query (using the 1 left-most column)
• you specify WHERE City = 'London' and LastName = 'Smith' in your query (using the 2 left-most columns)

However, such an index cannot ever be used for:

• finding all rows with a WHERE FirstName = 'Joe' clause - you're not using the n left-most columns in that index
• finding all rows with a WHERE LastName = 'Brown' AND FirstName = 'Charlie' clause - again: you're not using the n left-most columns in that index

A compound index cannot (in many cases) replace all one-column indices. Be very careful when designing and creating compound indices to make sure you design them in a way they'll really be useful!

So while compound indices do have their reason to be, and they're generally preferable - you also need to make sure they'll really be able to be used. An index that is just being maintained (and causes effort on SQL Server's side), but can never be used in any queries, is the worst index ever - no gain, only maintenance overhead.

You should also read Kimberly The Queen of Indexing Tripp's excellent blog posts on SQL Server indexing - most notably Indexes: just because you can, doesn't mean you should!