CODEWITHSUNDEEP
sql-serversql-server-2008etl
James A Mohler
James A Mohler

Reputation: 11120

Add and populate a column or add a persisted computed column?

I have a DB table with 10 million rows, 5GB of data, no primary key, but a clustered index. Every day 100s of thousands of records get added. I am looking to add a new column:

 ALTER TABLE dbo.CMS_hsplit ADD
     split_locIDacd  AS CONVERT(varchar(8), split) + '-' + 
     CONVERT(varchar(8), CASE WHEN loc_id = 105 THEN acd ELSE loc_id END) PERSISTED

The DBA assigned to this prefers to change the ETL process that loads the table instead. I know the persisted calculated field will take some time to back fill the data. Is there really a significant overhead associated with using a calculated field?

UPDATE

The Calculated field is not expected to change for years

If the definition did change, it would have to be retroactive

Upvotes: 4

Views: 5312

Answers (1)

Pondlife
Pondlife

Reputation: 16240

I think the overhead of adding or populating the column isn't really the point. A more important question is will your computed column definition always be valid for every row in the table for the lifetime of the database? If you ever need to change the definition, or if new rows added after a certain date should have a different definition but the old rows should keep the current one, then a computed column will either be impractical or completely impossible.

My preference would be to add a normal column and add the business rules for populating it to your ETL process. That also ensures that all your business rules are in one place (your ETL code) rather than being split between code and schema.

Upvotes: 7

Related Questions