CODEWITHSUNDEEP
sql-serversql-server-2014calculated-columns
l33t
l33t

Reputation: 19956

When are persisted computed columns updated?

Running SQL Server 2014. MSDN states that the values of computed columns marked as PERSISTED are "updated when any columns that are part of their calculation change". Are they referring to the entire column of a table or the actual row?

E.g. If I have a table T with a persisted computed column Bar as shown below, will Bar be recalculated if I insert a new row into T? (The entire colum does change, but not the actual row.)

CREATE FUNCTION dbo.Baz(@foo INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN @foo % 10 -- Could be more complex! E.g. SELECT ...
END
GO

CREATE TABLE T 
(
    ID INT IDENTITY(1,1) NOT NULL,
    Foo INT NOT NULL,
    Bar AS dbo.Baz(Foo) PERSISTED
)
GO

Upvotes: 0

Views: 690

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Computed columns are meant to only depend on other column values within the same row (plus expressions, constants, etc). As such, your question does not make much sense.

However, some people do "clever" things by using UDFs so that they can access other data within other tables or for other rows within the same table as part of their computed column definition. SQL Server isn't aware of this "cleverness" and so, if the UDF is accessing other data, no, it's not guaranteed to update such a computed column's value for any row which isn't actually affected by a DML statement.

Upvotes: 1

Related Questions