Computed column with/with out PERSISTED

Question

Computed column with/with out PERSISTED

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 1

My personal opinion is that if you're not going to persist a calculated column, write a derived table or view, instead. That way, no one can screw up and actually make queries that even try to use it effeciently. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101130 More actions · Answer 2

Jeff Moden (1/28/2013)
My personal opinion is that if you're not going to persist a calculated column, write a derived table or view, instead. That way, no one can screw up and actually make queries that even try to use it effeciently. 😛

Typically the overhead to materialize the computed column (cc) at run time trivial. If it is, a cc has a lot of advantages, so I suggest using it as intended.

A derived table risks differing definitions in multiple queries.

A view forces people to use different queries to access the data depending on whether they need a certain cc or not. If a query needs that cc added, you have to rewrite the query just because of that.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 3

ScottPletcher (1/28/2013)
Jeff Moden (1/28/2013)
My personal opinion is that if you're not going to persist a calculated column, write a derived table or view, instead. That way, no one can screw up and actually make queries that even try to use it effeciently. 😛
Typically the overhead to materialize the computed column (cc) at run time trivial. If it is, a cc has a lot of advantages, so I suggest using it as intended.
A derived table risks differing definitions in multiple queries.
A view forces people to use different queries to access the data depending on whether they need a certain cc or not. If a query needs that cc added, you have to rewrite the query just because of that.

Sorry Scott... I meant to put out the bright orange barrels, cones, and flags that warned people that sarcasm was "ON". Go back and read it again. It was dripping in sarcasm in support of the use of CC especially persisted CC. 😉

I'll try to give better warnings in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)