What is the performance impact of using computed columns in an SQL Server 2005 database?

Data and Databases
1

I am working with a large SQL Server 2005 database that has several denormalized tables. Due to the need for frequently updating summary tables, we have been considering utilizing computed columns to keep data up to date. Additionally, we’ve discussed implementing triggers, but that is a topic for another time.

In our summary tables, we have denormalized data, storing both the Standard ID and the Standard Description. This setup relies on regular resummarization, which can lead to issues if updates aren’t made consistently.

My question is: How would the performance be affected if we convert the Standard Description in the summary table to a computed column that pulls from the standard table? Would there be significant performance degradation when managing a table that contains between 100,000 and 500,000 rows?

2

Hey, Liam27! I’m curiuos if you’ve thought about testing the computed columns approach in a staging enviroment first? It might give you real-time insights into any performance changes before you apply it to your production system. Also, could you share hw frequent your summary updates are? This might impact the overall performance, don’t you think?

3

Hey Liam27, computed columns could be handy but remember they’re calculated at runtime unless persisted as stored, which might impact performance with large datasets. Testing is crucial. Look out for CPU usage spikes during queries; they could reveal if this approach is causing a bottleneck. What about partitioning?