0

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.

Community
  • 1
  • 1
Luke Vo
  • 17,859
  • 21
  • 105
  • 181
  • I am not sure if you can make the orders.total persisted. – Tim3880 May 29 '15 at 18:00
  • @Tim3880 You're right! I just checked, there was this message: Computed column 'Total' in table 'Tmp_Order' cannot be persisted because the column is non-deterministic. – Luke Vo May 29 '15 at 18:01
  • 1
    If you want to improve the select, make the total a normal column and update it when you insert rows to order details. If you want to improve insert/update, create a v_orders view including the totals you need. – Tim3880 May 29 '15 at 18:32
  • Thank you for your nice advice! I am going to do it that way. – Luke Vo May 29 '15 at 19:03

1 Answers1

1

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • What I need to clarify is that, if a change that will not affect the computed column value (inserting an `OrderDetail` record that would not affect `Order.Total` for example) is made, do SQL Server have to re-compute all record of `Order` table? – Luke Vo May 29 '15 at 17:43
  • AFAIK Sql server will only re-calculate the values that actually need re-calculation. – Zohar Peled May 29 '15 at 17:47