CODEWITHSUNDEEP
sql-serverperformancesql-server-2008-r2calculated-columns
Luke Vo
Luke Vo

Reputation: 20668

Does Persisted Computed Column need to update all rows or just affected rows?

In this Stackoverflow article, I understand what Persist mean and their advantages and disadvantages. However, I tried to research deeper but cannot find about the "update" one.

Here is my scenario (TL;DR version below): I have Order table and OrderDetail table, as in this diagram:

enter image description here

The OrderDetail.Subtotal is a computed column, with this simple fomula: ([Price]*[Quantity])

The Order.Total is a computed column, with this formula: ([dbo].[CalculateOrderTotal]([ID])), and here is the function:

ALTER FUNCTION [dbo].[CalculateOrderTotal]
(
    @orderID int
)
RETURNS int
AS
BEGIN
    DECLARE @result int;

    SELECT @result = SUM(od.Subtotal)
    FROM OrderDetail od
    WHERE od.OrderID = @orderID;

    RETURN @result;
END

TL;DR: My Order and OrderDetail are frequently updated, but only mostly INSERTING. I also need to query it usually, though not as frequently as inserting. If I check my Subtotal and Total to be Persist, since most of the operations won't affect the Subtotal field of a row, does SQL Server know it so that the database only need to update the affected record, or it have to recompute all record of Order table? Is it good or bad?

P.s: I am using SQL Server 2008 in case it matters.

Upvotes: 0

Views: 2971

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Computed columns are calculated when they are selected.
Persisted columns are calculated when the expressions they are calculated from are changed.
So for select performance i would suggest using persist, but for storage space not to use persist, since "regular" computed columns are not actually stored in the database.

Upvotes: 1

Related Questions