0

I have a column USER_ID which I set as a "Computed Column Specification" with a formula of (newid()). But accidentally, I did not select the "Is Persisted" option.

Meanwhile, I have inserted some of the records in the same table. Now I want to change it to being "Is Persisted".

But while changing process, I get an error

Computed column 'USER_ID' in table 'Tmp_USER_PROFILE' cannot be persisted because the column is non-deterministic.

Please suggest how I can change it from "Is Persisted=No" to "Is Persisted=Yes".

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sumit Joshi
  • 25
  • 8
  • 2
    You can't, because `NEWID()` is nondeterministic, and nondeterministic computed columns can't be persisted. You probably intended to give the column a *default* value of `NEWID()`, rather than have the value change on every row retrieval. Recreate the column as `USER_ID UNIQUEIDENTIFIER DEFAULT NEWID()`. Note that your current rows already don't have stable IDs, so you're not losing any information by dropping the column. – Jeroen Mostert Sep 14 '17 at 11:42
  • you can not use newid() as calculated columns however you can use trigger for workaround. please see following code --->> go create table Table1 (ID int,Column2 uniqueidentifier ) go create trigger t1 on Table1 after insert as begin update t1 set t1.Column2 = NEWID() from Table1 t1 join inserted t2 on t1.id = t2.id end go insert into Table1(ID) select 1 go – Rahul Richhariya Sep 14 '17 at 12:15

0 Answers0