Use of Uniqueidentifier in Persisted calculated column.

This post is for you in case you decide to use Uniqueidentifier column in your table and then you think about including it into a Persisted calculated column.
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;

Now you got the right result, but how to fix it in the table?

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