Do you know under which version of SQL Server this column was created?
I have run into similar issues when they changed how they store the expressions for computed columns, like in this example adding extra parentheses. And like in your case this caused grief, because where was code in a tool that compared the expressions as strings without parsing them. (Full disclosure: I am the author of that tool myself.)
I believe this was in the transition from SQL 2008 to SQL 2012. And indeed, when I run
CREATE TABLE alfa (x_id int IDENTITY (1, 1) NOT NULL,
somedata nvarchar(100) NOT NULL,
x_hash_id AS convert(tinyint, abs(x_id) % 16),
CONSTRAINT pk_alfa PRIMARY KEY (x_id)
)
SELECT definition FROM sys.computed_columns WHERE object_id = object_id('alfa')
on SQL 2008, I get:
(CONVERT([tinyint],abs([x_id])%(16),0))
Whereas if I run the same on later version, I get
(CONVERT([tinyint],abs([x_id])%(16)))
That is, no extra zero at all. And if I add that extra zero, it is bracketed in parens. (Which also happens on SQL 2008.)
You have two choices:
- Drop the computed column before the switch and recreated afterwards.
- Start SQL Server in single-user mode and edit the underlying system tables. I definitely recommend against this. (I believe that if you later open a support case, Microsoft may detect that you have been fiddling there, and at that point they may close the case and say that you are not supported.)