computed persisted column

I am trying to use the persisted option of the computed column on the result obtained by the below query which I have used it in a scalar function. I have encountered the below error.

Computed column 'Resources_1' in table 'ImplProject' cannot be persisted because the column is non-deterministic.

I have tried searching many blogs about this but unable to come to a proper solution. Please help

select ISNULL(STUFF((SELECT CHAR(10) + d.[Value] + ': ' + [dbo].[FullName](u.[FirstName], u.[LastName])
FROM [ImplProject] ip
JOIN [Resource] r ON r.[EntityId] = ip.[Id]
JOIN [Dropdown] d ON d.[Id] = r.[TypeDropdownId]
LEFT JOIN [User] u ON u.[Id] = r.[UserId]
WHERE ip.[Id] = @implid
ORDER BY d.[Value]
FOR XML PATH('')), 1, 1, ''), '') as "Resources"


ImplProject

1 Answer
1

Well, if the column is non-deterministic it can't be persisted and it's quite clear why:
A persisted column is computed when one of it's components changes, and to get reliable values the formula to compute it must be deterministic.
If the formula to compute the column value is non-deterministic, it must be computed on each use - that means it can't be persisted.

A formula is deterministic if it always returns the same value for the same input.
Since your formula depends on data from a table, it can't be deterministic, and therefor your column can't be persisted.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.