Sql-server – computed columns, index, clustered index and covering index

indexsql-server-2008

We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time.
So, my first question is, is that correct that what I just said?

Also, if you create a clustered index, okay let me just quote..
"If you create a clustered index on a computed column, the values of the column will
physically exist in the corresponding table rows, because leaf pages of the clustered
index contain data rows."
Yeah, okay, but why create clustered index on computed column only? I thought if you create a clustered index on any column the data exists on leaf?
So, my Ques 2 is would the value of computed column exists (persist) in table if only we create a clustered index on that particular column? (question 2 continues) If that is indeed the case, what if we have more than one computed columns? surely, we can't create more than one clustered index, than how do we make their value persistent?

Now moving on, what about the covering indices? First, we don't really need covering (or we can't really create) covering index on while creating clustered index because all the data would already be on leaf, right? So, there's no point of including any other column?
So, thirdly I'd like to ask, am I correct about what I just said in my previous statement?

Now if we are creating a covering index, (non-clustered right?) and we include the computed column(s), then would they we persisted? would they be "physically stored" or calculated again? (and that was my 4th ques. by the way.)

Finally, (5th ques) if we can achieve the same (make computed columns persist) by all these ways (or some of these ways, I don't really know which one, that's why I am asking the question in first place!) then, what would be the best method to adopt and why?

ps : I am sorry if for asking 5 question in just once, but they are all terribly related, and I didn't want to ask same thing over and over again in 5 different questions. I hope you understand! 🙂

EDIT : well in response to comments, right now I am using SQL server 2008, but if you could provide how the situation would differ if using anything else (my sql or whatever) that would be added benefit for not only me but also for community. 🙂

Best Answer

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.

Related Question