Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Whilst reading through my 70-433 exam book, I thought of something that I can see not working, yet I believe it does. The passage read something like:

The column must also be marked as PERSISTED, which means that SQL Server physically stores the result of the computed column's expression in the data row instead of calculating it each time it is referenced in a query.

From this I understand two things:

  1. A non-persisted computed column is calculated every time that it is referenced in a query
  2. Because nothing is stored for the computed column, I assume no index can be created for the column.

After reading it, I thought that this was a bit strange as I have managed to create an index on a non-persisted column in a previous project.

How can an index be created for something that is not persisted and is this detrimental in the long run?


To prove this I have run the following SQL statement:

CREATE TABLE testTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    telephone VARCHAR(14),
    c_areaCode AS (SUBSTRING(telephone,0,5)),
    cp_areaCode AS (SUBSTRING(telephone,0,5)) PERSISTED
)

INSERT INTO testTable VALUES('09823 000000');
INSERT INTO testTable VALUES('09824 000000');
INSERT INTO testTable VALUES('09825 000000');

CREATE NONCLUSTERED INDEX IX_NotPersisted ON testTable(c_areaCode);
CREATE NONCLUSTERED INDEX IX_Persisted ON testTable(cp_areaCode);

And then run the following queries:

DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL');
DBCC DROPCLEANBUFFERS
GO
SELECT cp_areaCode FROM testTable;
GO
SELECT c_areaCode FROM testTable;

Having looked at the query plan for the above code, I can see that both select queries are using the non-persisted index. Again, how?

enter image description here

share|improve this question
    
For those that have the 70-433 book, the quoted text is at the top of page 111. –  Stuart Blackler Aug 29 '11 at 17:04
add comment

1 Answer

up vote 6 down vote accepted

2.Because nothing is stored for the computed column, I assume no index can be created for the column.

This assumption is not true - either kind can be indexed. The computed column must be deterministic in either case but when the computed column is persisted, the requirement that the computation is also precise is relaxed (ie it can involve floating point operations).

How can an index be created for something that is not persisted and is this detrimental in the long run?

The result of the function is 'persisted' in the index in either case - the only difference is whether it is persisted in the table.

share|improve this answer
    
Thanks for the answer Jack. If it persisted in the index, then how does the index know if the underlying value is changed? –  Stuart Blackler Aug 29 '11 at 19:08
    
And do you know why the QO would use the non-persisted column in my example above? –  Stuart Blackler Aug 29 '11 at 19:34
1  
An index will always be updated if the underlying data is changed - in the case of a computed column, the 'underlying data' is any column referred to in the function. –  Jack Douglas Aug 30 '11 at 9:14
    
The choice of index will be based on the estimated 'cost' - I suspect in this case it is arbitrary. Are the indexes physically the same size? –  Jack Douglas Aug 30 '11 at 9:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.