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

Question

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?

score 10 · Accepted Answer · answered Sep 29 '17 at 06:39

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.

Adding to this, maybe it's better to also check computing that column server-side after you retrieve data from database. Calculating in c#/php/etc is way better than calculating in sql and you save some storage for the cost of memory. — Razvan Dumitru, Sep 29 '17 at 07:28
As regards storage, I generally argue that in 2018, if you are worried about storage, get a bigger box. Storage is generally cheaper than performance. In this example. presuming the the results don’t need re-calcuating, I should imagine that if you only want the `fullname`, then a persisted column would be cheaper both in processing and I/O. Sorry it’s taken me so long to accept the answer. — Manngo, Dec 22 '18 at 00:58

score 4 · Answer 2 · answered Sep 29 '17 at 06:28

4

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.

answered Sep 29 '17 at 06:28

Bave

235
1
6

score 2 · Answer 3 · answered Sep 29 '17 at 06:48

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

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

3 Answers3