If you’re getting an error that reads something like “Computed column ‘c3’ in table ‘t1’ cannot be persisted because the column is non-deterministic” in SQL Server, it appears that you’re trying to persist a computed column that’s nondeterministic.

A nondeterministic function or expression returns a different result for the same input. If a column uses such an expression, then it will be nondeterministic. A computed column must be deterministic.

To fix this issue, either don’t persist the column, or remove the nondeterministic expression from the column.

Example of Error

Here’s an example of code that leads to the error:

CREATE TABLE t1 (
    c1 int,
    c2 varchar(10),
    c3 AS NEWID() PERSISTED
);

Output:

Msg 4936, Level 16, State 1, Line 1
Computed column 'c3' in table 't1' cannot be persisted because the column is non-deterministic.

In my case, I’m trying to persist a computed column that uses the NEWID() function. This function is nondeterministic. It returns a different randomly generated 16-byte GUID (globally unique identifier) each time it’s run. Therefore, each time a user runs a SELECT statement against the table, this function will produce a new value in that column for each row.

This is fine for many other situations, but not when we’re trying to persist the computed column. Persisting a computed column physically stores the column in the table. Therefore, any data needs to remain static, otherwise it would need to be refreshed every time anyone viewed the data, which would defeat the purpose of persisting the computed column in the first place.

Solution

One option is to remove the nondeteriministic function/expression from the computed column, but that might be a bit extreme. Doing this would most likely remove any functionality you’re trying to implement.

Therefore, you can fix this problem simply by removing PERSISTED from the definition of the computed column:

CREATE TABLE t1 (
    c1 int,
    c2 varchar(10),
    c3 AS NEWID()
);

Output:

Commands completed successfully.

This time it ran successfully.

Just to verify the nondeterminism of my column, let’s insert a row and select it multiple times:

INSERT INTO t1 (c1, c2) VALUES (1, 'Homer');

SELECT * FROM t1
UNION ALL
SELECT * FROM t1
UNION ALL
SELECT * FROM t1;

Output:

c1  c2     c3                                  
--  -----  ------------------------------------
1   Homer  BA9344BC-97A7-4A62-9432-4897EE7DA041
1   Homer  EB5CEF51-0360-4C73-8769-8D5BACB93F96
1   Homer  342206FF-D38B-4FD5-BD77-B7F65A3526BE

We can see that the c3 column contains a different value each time we run the SELECT statement. This is a nondeterministic column, and so that’s why I had to remove the PERSISTED keyword.