CODEWITHSUNDEEP
sql-servercalculated-columnsquery-performance
Manngo
Manngo

Reputation: 16281

SQL Server: Is there a performance cost to computed columns?

If I include a computed column in a table, does it place a perfomance burden on it?

For example, if I have a table defined as follows:

CREATE TABLE users(
    givenname …,
    familyname …,
    fullname AS givenname+' '+familyname
);

… does it recalculate every time I SELECT from the query?

Would it help if the computed column is PERSISTED?

Upvotes: 15

Views: 11263

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28890

My tests indicate ,even if you persist the column,the are calculated every time when you select ..

below is some test data

create table numm
(
id int,
 col_not_persist  as id+1,
col_persist as id+1 persisted
)

insert into numm
(id)
select number from Numbers--just numbers from 1 -1000000

now when you query

select * from numm

execution plan shows two scalar operator(costed less though) and if you can see the definition on top right hand corner.. you can see they are calculated every time they are referenced

enter image description here

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

does it recalculate every time I SELECT from the query

If that column is references in a query then the calculation will be performed at least once during the query. This is (yet another) good reason to avoid select * ... - if you have a query against the users table that doesn't reference the fullname column then no calculation will be performed.

Would it help if the computed column is PERSISTED

Obviously, the calculation doesn't need to be performed, but you're now paying for a small amount of storage and I/O costs in having the retrieve the persisted data.

But in short, don't try to second guess these things. If this is a common calculation, create a computed column for it. Later, once you have performance goals and can measure your performance, you can assess whether it has a meaningful performance issue and whether the persisted trade off (calculation vs storage) has any effect on this.

Upvotes: 10

Bave
Bave

Reputation: 235

Yes, if the computed column is PERSISTED, there is no performance issue in selecting the data (but there is in inserting them).

And yes, it is calculated every time you select the data.

Upvotes: 4

Related Questions