Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

Question

We have an OrderLines table with columns:

Quantity int not null
QtyCancelled int not null
QtyBackorder int not null
QtyPicking int not null
QtyPacking int not null
QtyShiped int not null

with computed and persisted column FulfillmentStatusId as:

dbo.clr_GetFulfillmentStatusByLineQuantities(Quantity, QtyCancelled, QtyBackorder, QtyPicking, QtyPacking, QtyShiped)

"clr_GetFulfillmentStatusByLineQuantities", of course, being mapped through a scalar function to an ASSEMBLY in the database with the associated method (C#) marked with:

[SqlFunction(DataAccess = None, IsDeterministic = true, IsPrecise = true, SystemDataAccess = None)]

With reference to this question, I've run DBCC CHECKTABLE against this table multiple times and it still remains as has_unchecked_assembly_data=1. This was true when the column was first added to the table, but I believe the DBCC CHECKTABLE command succeeded prior to the migration to SQL 2016.

---

While the table has been in use for a long time with has_unchecked_assembly_data=1, there seems to be no impact on the table's functionality. I am concerned about ALTER ASSEMBLY throwing an exception when not using WITH UNCHECKED DATA, then throwing another exception when I did use that option, but appearing to update the assembly anyway "according to MVID".

Sorry if I ramble, I don't ask too many questions and I mostly lurk.

Thank in advance, however!

---

Update (20180917T17:34-05:00): Thanks to Sean for suggesting an initial direction. The result of DBCC CHECKTABLE ('OrderLines') WITH EXTENDED_LOGICAL_CHECKS was two lines similar to:

Msg 2537, Level 16, State 106, Line 1
Table error: object ID 1486732449, index ID 1, partition ID 72057594403946496, alloc unit ID 72057594422755328 (type In-row data), page (1:1430554), row 5. The record check (valid computed column) failed. The values are 38 and 0.

So I executed a REBUILD on the primary key of the table (which is index ID 1 indicated above), which succeeded. However, rerunning DBCC CHECKTABLE('OrderLines') WITH EXTENDED_LOGICAL_CHECKS again returned similar errors, only this time with different partition IDs and allocation unit IDs (as one might expect).

The extreme solution might be to drop, recreate, and repopulate the table with identity insert, "nuke the site from orbit" so to speak, but for obvious reasons, that's extreme.

What is the correct solution to "there appears to be inconsistent computed column data in the table's clustered index"?

Answer 1

OP here, answering my own (and first) question!

With help from Sean Gallardy, using EXTENDED_LOGICAL_CHECKS pointed me to inconsistencies with the persisted computed column's data. I first tried recreating the primary key (clustered index), but this didn't actually fix anything (an extreme and weird solution).

I also tried using DBCC CHECKTABLE ('{table}', REPAIR_ALLOW_DATA_LOSS), but the same errors were returned again. I don't believe anything was repaired.

My first solution was to drop and recreate the persisted computed column. This worked.

But, thinking that was somewhat "extreme" as well (considering the table has 10M+ rows and it takes forever to do this because there is also an index against this column), I was curious about a more direct solution.

Since there were only two rows reported by DBCC CHECKTABLE with their page and row references I looked into DBCC PAGE. Using this command with precision got me the actual row data/row identity and when I executed the CLR method that drives the computed column, there was indeed an inconsistency.

Basic actual and inconsistent table data:

OrderLineId  QtyOrd  QtyCancl  QtyBack  QtyPick  QtyPack  QtyShip  FulfillId
    1234567       4        12        0        0        0        0          0
    4567890       4        12        0        0        0        0          0

FulfillId in this case should be 1 (representing cancelled), but these two table rows indicate 0 (open/unfulfilled) for both rows.

I ran the actual row data through the CLR function using a SELECT statement:

select
  ol.OrderLineId,
  ol.QtyOrd, ol.QtyCancl, ol.QtyBack, ol.QtyPick, ol.QtyPack, ol.QtyShip,
  ol.FulfillId [CurrentFulfillId],
  CLR(ol.QtyOrd, ol.QtyCancl, ol.QtyBack, ol.QtyPick, ol.QtyPack, ol.QtyShip) [ComputedFulfillId],
from
  OrderLines [ol]
where
  ol.OrderLineId in (1234567, 4567890)

This returned essentially the exact same information, with ComputedFulfillId also resolving to 0 (open). So I retested the CLR code directly in a .Net console application, and given the explicit values that should have been passed in the SQL call, the result was the expected value 1 (cancelled).

I revised the SELECT statement to include a call to the CLR method using explicit constant values instead of the values from the dubious rows:

SELECT ..., CLR(4, 12, 0, 0, 0, 0) [ForcedFulfillId] FROM OrderLines [ol] ...

And the ForcedFulfillId column contained the correct value of 1.

Using DBCC PAGE to find the exact row identities and updating the rows in question forced the computed column data to be recomputed. One final DBCC CHECKTABLE returned no errors and everything was good again.

So rather than drop and recreate the whole computed column, using DBCC PAGE with information from DBCC CHECKTABLE({table}) WITH EXTENDED_LOGICAL_CHECKS allowed me to fix the original "has_unchecked_assembly_data=1" issue causing ALTER ASSEMBLY errors.

WHEW

Sorry for the long-winded explanation - wanted to make sure I documented my travels for the next poor soul.