Performance implications of computed columns in SQL Server 2005 database?

Question

The situation: we have a large database with a number of denormalized tables. We frequently have to resummarize the data to keep the summary tables in synch. We've talked on and off about using computed columns to keep the data fresh. We've also talked about triggers, but that's a separate discussion.

In our summary tables, we denormalized the table such that the Standard ID as well as the Standard Description is stored in the table. This inherently assumes that the table will be resummarized often enough so that if they change the standard description, it will also change it in the summary table. A bad assumption.

Question: What if we made the Standard Description in the summary table a derived/computed column which selects the standard description from the standard table? Is there a tremendous performance hit by dropping a computed column on a table with 100,000-500,000 rows?

Answer 1

Not really an answer, but what's wrong with using views?

Answer 2

Are you trying to complete this change without changing the signature of the "summary" table? (e.g., you don't want to have to make a code change to existing code which references the field already belonging to the summary table)

Answer 3

Computed columns are fine when they are not calculation intensive and are not executed on a large number of rows. Your questions is "will there be a hit by dropping the computed column." Unless this column is an index that is used by the query (REAL bad idea to index a comp col - i don't know if you can depending on your DB), then dropping it cant hurt your performance (less data to query and crunch).

If the standard table has the description, then you should be joining it in from the id and not using any computation.

You alluded to what may be a real problem, and that is the schema of your database. I have had problems like this before, where a system was built to handle one thing, and something like reporting needs to be bolted on/in. Without refactoring your schema to balance all of the needs, Sunny's idea of using views is just about the only easy way.

If you want to post some cleansed DDL and data, and an example of what you are trying to get out of the db, we may be able to give you a less subjective answer.

Answer 4

A computed column in a table can only be derived from values on that row. You can't have a lookup in the computed column. For that you would require a view.

On a table that small denormalising the name into the table will probably have negligable performance impact. You can use DBCC PINTABLE to hint the server to keep the table in the cache.

If you need the updates to be made in realtime then really your only option is triggers. Putting a clustered index on the ID column corresponding to the name you are updating should reduce the amount of I/O overall (the records for a given ID will be in the same block or set of blocks) so try this if the triggers are causing performance issues.

Answer 5

Just to clarify the issue for the sql2005 and up:

This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.