April 28, 2011 at 9:58 am
Hi.
I'm wondering about marking a computed column as persisted is useful when I'm using a query fully covered with an index.
I think that this haven't sense, because really persisted data won't be used, index data will be used instead.
Thank you for your opinion,
Fran
April 28, 2011 at 1:04 pm
In this as in all things, it depends.
You are correct. If your query is using a covering index then the data isn't coming from the clustered index. But sometimes you need persisted columns to be able to build an index in the first place.
But from BOL:
Creating Indexes on Persisted Computed Columns
You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED ... This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2011 at 1:42 pm
hillock666 (4/28/2011)
Hi.I'm wondering about marking a computed column as persisted is useful when I'm using a query fully covered with an index.
I think that this haven't sense, because really persisted data won't be used, index data will be used instead.
Thank you for your opinion,
Fran
I'm not sure why you'd want to make the computed column persisted in the first place. Is it an imprecise data type? If it isn't, you can use a computed column in an index, you can use a computed column as an included column in an index. What would making it persisted do?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 28, 2011 at 2:23 pm
I think that was the point behind his question, Stefan. Why would he ever need to make a computed column persisted?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2011 at 2:43 pm
Stefan Krzywicki (4/28/2011)
hillock666 (4/28/2011)
Hi.I'm wondering about marking a computed column as persisted is useful when I'm using a query fully covered with an index.
I think that this haven't sense, because really persisted data won't be used, index data will be used instead.
Thank you for your opinion,
Fran
I'm not sure why you'd want to make the computed column persisted in the first place. Is it an imprecise data type? If it isn't, you can use a computed column in an index, you can use a computed column as an included column in an index. What would making it persisted do?
Avoid constant recalculation. To have an index, it has to be persisted or precisely deterministic (basically not float or real, and not varying on external data to the row), or it won't make sense.
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.
Also see this link:
MSDN Creating Indexes on Computed Columns
See under the header of determinism requirements when you can have an index that's non-persisted. Indexing a deterministic column does, in a way, persist the data in the index, but not in the table itself. At that point, you might as well, under most circumstances, persist it in the table proper. Otherwise they're recalculated if you don't end up using that index and you go in via the clustered or a different nonclustered index.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply