You also might see it useful if you like weird or funny SQL Server behavior.
At first, here is the SQL Server version I have that problem on.
After a while Microsoft might fix that bug.
| Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) |
To demonstrate the problem in the most simple way you can run following script:
DROP TABLE IF EXISTS #tbl_Error_Test;
GO
CREATE TABLE #tbl_Error_Test(
GuidOne
UNIQUEIDENTIFIER,
GuidTwo
as CAST(GuidOne as CHAR(36)) PERSISTED
);
GO
INSERT INTO #tbl_Error_Test(GuidOne) VALUES (NewID()), (NewID());
GO
SELECT * FROM #tbl_Error_Test;
GO
|
It will return something like this:
As you can see, columns GuidOne and GuidTwo are different.
Moreover, if you run following command you will get very unpleasant error:
DBCC CHECKTABLE('#tbl_Error_Test') WITH NO_INFOMSGS,
EXTENDED_LOGICAL_CHECKS;
|
Msg 2537, Level 16, State 106, Line 152
Table error: object ID -1485216446, index
ID 0, partition ID 6052840780930088960, alloc unit ID 2738196559872000000 (type
In-row data), page (4:1079768), row 0. The record check (valid computed column)
failed. The values are 2 and 0.
Msg 2537, Level 16, State 106, Line 152
Table error: object ID -1485216446, index
ID 0, partition ID 6052840780930088960, alloc unit ID 2738196559872000000 (type
In-row data), page (4:1079768), row 1. The record check (valid computed column)
failed. The values are 2 and 0.
CHECKTABLE found 0 allocation errors
and 2 consistency errors in table
'#tbl_Error_Test_____________________________________________________________________________________________________000000001926'
(object ID -1485216446).
repair_allow_data_loss is the minimum
repair level for the errors found by DBCC CHECKTABLE
(tempdb.dbo.#tbl_Error_Test_____________________________________________________________________________________________________000000001926).
|
If you try to check what the correct value should be, it won't make any good:
SELECT GuidOne, GuidThree = CAST(GuidOne as CHAR(36))
FROM #tbl_Error_Test; |
The SQL Server is smart enough to recognize same function and instead of calculating the value it will return you the value stored in the table.
You can only do a trick if you change the function and convert Guid to 37 chars, instead of 36:
SELECT GuidOne, GuidFour = CAST(GuidOne as CHAR(37))
FROM #tbl_Error_Test;
|
It is not easy, but possible. You just have to replace Guid column by itself, but from another table:
UPDATE G1 SET GuidOne = G2.GuidOne
FROM #tbl_Error_Test as G1
INNER JOIN #tbl_Error_Test as G2
ON G1.GuidOne = G2.GuidOne;
GO
SELECT * FROM #tbl_Error_Test;
GO
|
It is fixed now and it also fixes DBCC CHECKDB error.
If you see the same behavior on your SQL Server you can vote for that bug to be fixed at Microsoft site:
https://feedback.azure.com/forums/908035-sql-server/suggestions/39694663-use-of-guid-column-in-persisted-calculated-column
