Strange behaviour with Computed Columns in SQL-Server 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: 


A non-persisted computed column is calculated every time that it is referenced in a query
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?
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.