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?
