2

I want the computed column to store count totals from another table, how would I do it? (would the following work)

create table sample ( column1 AS (SELECT COUNT(*) FROM table2) PERSISTED )

user666423
  • 229
  • 3
  • 14

2 Answers2

2

For SQL Server you could potentially do this with an Indexed View.

Those present a host of other restrictions, though, so be sure the value is enough to justify the increased effort in maintenance.

One of the handier aspects of indexed views is that you don't need to query them directly to get the benefits - if the optimizer detects you querying an aggregate that is indexed it'll make use of it "behind the scenes".

JNK
  • 63,321
  • 15
  • 122
  • 138
  • How would I create an indexed view? – user666423 May 23 '11 at 18:04
  • @user - take a look at the link. Basically you create a view `WITH SCHEMA_BINDING` which means you can't change any fields it references without dropping it, and follow the guidelines for the fields. You need a `COUNT` and you can include a lot of aggregates in there as well. – JNK May 23 '11 at 18:05
1

Per MSDN:

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Jerad Rose
  • 15,235
  • 18
  • 82
  • 153