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 INCLUDE
d columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.
Nonclustered indexes always include a row locator.
For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that the CI key be narrow and not frequently updated.
In the table shown in the question the Clustered index key is a 4 byte integer and potentially may also include a 4 byte uniqueifier for any duplicate key values.
In your case as the NCIs are not declared as unique the CI key will be appended to the NCI key.
For unique non clustered indexes the CI key would be added as included column(s) in the leaf pages unless explicitly made part of the key.
See Kalen Delaney: More About Nonclustered Index Keys for some additional information about how you can see this for yourself.
With these 4 rows of data all three indexes only consume a single 8KB data page.
SELECT index_id,
index_level,
page_count,
record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('people'), NULL, NULL, 'DETAILED')
Returns
+----------+-------------+------------+--------------+
| index_id | index_level | page_count | record_count |
+----------+-------------+------------+--------------+
| 1 | 0 | 1 | 4 |
| 2 | 0 | 1 | 4 |
| 3 | 0 | 1 | 4 |
+----------+-------------+------------+--------------+
The additional page shown in use by sys.allocation_units.total_pages
is an IAM page. This is not used for storing data but just for tracking the pages and extents comprising the index.
Best Answer
It does matter some times. Colleen Morrow does a good demo on this at http://colleenmorrow.com/2011/04/07/composite-indexes-does-column-order-matter/
In essence she says that if your WHERE clause contains the first column in the index it will do an index seek. If it contains the third column in the index and not the first it will do an index scan which is not as good. In this case the new index would be better (assuming the optimizer chooses it). If your WHERE clause contains both columns then apparently either index would serve.