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"?