October 15, 2009 at 4:24 pm
Guys,
I was playing around with some computed columns and noticed some weirdness. From my understanding, a PERSISTED computed column will remain in the database as a static value, until one of the columns that comprise it is UPDATED. When that happens, the value in the computed column will re-calculate and is then stored statically in the DB again. (Please let me know if this is incorrect!)
But, while playing around I noticed that with a persisted column, if I select it, there is still a Computed Scalar (always 0% based on the limited testing I've done). However, if it's just grabbing the values, there shouldn't be any computation, right? Also- If the column is not declared as PERSISTED, there are actually two compute scalars in the actual execution plan, does anyone know why?
Test script:
CREATE TABLE dbo.TestComputedColumn
(
Price money,
Quantity int,
Total AS Price * Quantity PERSISTED
)
INSERT INTO dbo.TestComputedColumn
VALUES (4.14, 3)
INSERT INTO dbo.TestComputedColumn
VALUES (183.13, 2)
INSERT INTO dbo.TestComputedColumn
VALUES (.08, 78)
SELECT Total FROM dbo.TestComputedColumn
October 16, 2009 at 7:08 am
I posed this question mid-way through another thread about a week ago and Paul gave me the following reply this morning.
Note that this is straight from the other thread and it's from Paul White; I hope he doesn't mind me doing this, I wanted to share his reply with anyone who was also wondering about this:
1. There's no need to persist the column if you are then going to create an index on it.
2. A compute scalar always appears in the plan for a persisted computed column, for internal reasons. It doesn't mean the value is being recomputed - unless it says so.
The QO will (annoyingly) often choose to recompute the value, if the base columns for the computation are available to it, and if the computed column isn't stored on the same data access path. This is because the estimated cost of the scalar computation is so low. For example, the QO will often choose to recompute over performing an RID- or KEY-lookup to fetch the persisted value...the lookup will cost a few logical I/Os, whereas the compute scalar appears to be almost free. This is particularly annoying when the persisted value is based on the result of a complex scalar function. Indexing appropriately (which often means INCLUDEing the computed value) is one way around this.
Thank you guys!
October 16, 2009 at 7:13 am
Hey Mike - I saw your question and thought 'hmmm - now where did I post that other reply earlier...?'
So thanks for saving me a job!
Paul
edit: turns out it was the very next thread in this topic :rolleyes:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply