Re: Computed columns vs Dynamically calculated columns (for celko)
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 09 Jun 2009 15:28:44 -0700
(sqlguru@xxxxxxxx) writes:
Take a large forum for an example. Is it better to use a computed
column or a dynamically calculated column for statistics like post
count, topic count etc.
You could have a separate table that hold this information, and which
you would need to keep updated through triggers or similar.
Whether this is a good idea depends. Querying the data will be faster, but
there will be an overhead to maintain it. And in either case, there is
a risk that you do not code correctly, or miss an update in some place,
that the summary table will not hold accurate information.
In the example you give, I suspect that the answer is negative. But say
that you have transactions and cash holdings for all accounts in a large
bank. The cash holdings is the sum of all transactions, but you would
recompute these everytime from the dawn of time, but have the cash holdings
in a separate table.
An alternative is to have an indexed view. This relieves you from writing
the update logic. The extra cost for the updates will still be there though.
And unless you use Entprise Edition, you would need to use the NOEXPAND
hint when you query the view to actually use it.
Also, what is the difference between a computed column vs a calculated
column? I know you can persist a computed column but would it have a
performance benefit?
There are some situations when you have to persist it. For instance if
you want that column in the clustered index.
Whether to persist a computed column solely for the performance of that
column? If the expression is very complex, maybe.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: Computed columns vs Dynamically calculated columns (for celko)
- From: Aaron Bertrand [SQL Server MVP]
- Re: Computed columns vs Dynamically calculated columns (for celko)
- References:
- Prev by Date: Re: datetime month question
- Next by Date: Re: Setting 3 variables from one Select?
- Previous by thread: Re: Computed columns vs Dynamically calculated columns (for celko)
- Next by thread: Re: Computed columns vs Dynamically calculated columns (for celko)
- Index(es):
Relevant Pages
|
