0

I need a couple of computed columns that contain count totals (indexed columns). Do you think it is better to use a computed column in a view or add extra columns to the table that will store the totals? Adding extra columns would probably mean using triggers to keep the count totals correct.

DB is MS SQL 2008 R2.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user666423
  • 229
  • 3
  • 14

2 Answers2

1

You can use a indexed view to get the performance of stored columns at no maintenance effort.

usr
  • 168,620
  • 35
  • 240
  • 369
0

It depends.

If the tables change a lot but you rarely need the counts, a view is better. The question "view vs. computed columns" is one of DB design. If you can't change the original table or the DBMS doesn't support computed columns, use a view. If you can change the table definition, computed columns can be better but they also clutter the definition and make select * slower if you don't always need this data.

If the table rarely changes but you need those numbers a lot, use extra columns with triggers to avoid performance problems.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • For a table that records a the number of transactions (many transactions happen every minute) for each user, would the computed column result in a higher performance penalty over an actual totals column? – user666423 May 23 '11 at 16:13
  • As always with performance, any answer that doesn't include measuring some real data has a 90% chance of being wrong. Just try several approaches in the test system. – Aaron Digulla May 24 '11 at 08:20