CODEWITHSUNDEEP
sql-servert-sql
LONG
LONG

Reputation: 4620

Computed column vs Updates in SQL Server

I would like to know if there any performance boost for computed columns in SQL Server, please check the example below.

Now I have a stored procedure in production environment that updates tables, concatenating two VARCHAR columns into another column, which is NULL when created.

If I would like to switch the updating logic to using a computed column, which will automatically generate the value when loading the data.

Question is: will this help me to boost the process time for that derived column? I cannot really make the changes and test in the production environment at this point, but before I do that, in general, any advantages using computed columns vs updates.

Please note the updated column will remain as it is and the total number of records in effect will be up to million.

UPDATES:

Table definition

CREATE TableA
(
    ColumnA VARCHAR(50),
    ColumnB VARCHAR(50),
    ColumnC VARCHAR(50)
)

ColumnA and ColumnB will be populated with data from SSIS package, and ColumnC will updated by the stored procedure, which is 

UPDATE TableA 
SET ColumnC = ISNULL(ColumnA,'') + ISNULL(ColumnB,'')

These updates will affect about up to millions of records.

If I would like to use:

CREATE TableA
(
    ColumnA VARCHAR(50),
    ColumnB VARCHAR(50),
    ColumnC as ISNULL(ColumnA,'') + ISNULL(ColumnB,'') 
)

will this be quicker for populating the ColumnC?

Upvotes: 2

Views: 1555

Answers (1)

paparazzo
paparazzo

Reputation: 45096

On the update I suggest a where so you don't update rows that do not need to be updated. An update takes a lock and puts an entry in the transaction log. 

UPDATE TableA 
SET   ColumnC  = ISNULL(ColumnA,'') + ISNULL(ColumnB,'') 
WHERE ColumnC <> ISNULL(ColumnA,'') + ISNULL(ColumnB,'')

You can control growing the transaction log with:
(this is from memory so may have syntax error(s)) 

select 1
while (@@rowcount > 0)
begin
    UPDATE top(10000) TableA 
    SET   ColumnC  = ISNULL(ColumnA,'') + ISNULL(ColumnB,'') 
    WHERE ColumnC <> ISNULL(ColumnA,'') + ISNULL(ColumnB,'') 
end

Computed Column

Computed column is a virtual column unless it is persisted. So if you don't persist then there is no extra time to load ColumnA and ColumnB. Select on ColumnC will be slower as it is computed on the fly.

If ColumnC is persisted will be like the update but done on the fly when ColumnA or ColumnB are inserted or updated.

As stated in comments a computed column is consistent. An update is only consistent up to the last time the command was run.

Upvotes: 1

Related Questions