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

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.

Persisting a computed column does not guarantee that the persisted value will be used. The optimizer makes a cost-based decision between using the persisted value and computing the expression afresh, though there are also other factors in play. Simplifying, the process looks like this:

1. The computed column reference is always expanded to its definition before query compilation and optimization begins. This is very similar to the way view references are handled (unless indexed and referenced with a NOEXPAND hint). The expansion provides the greatest opportunity for orthogonal simplifications and optimizations to be applied.
2. Depending on the query and the code path taken through the query optimizer, a cardinality estimate may be requested for an expression in the expanded computed column definition. Deriving this cardinality estimation may result in a plan-affecting convert warning being added to an internal warning list.
3. The expanded computed column expression may be matched back to a persisted column or index later in the compilation and optimization process. Any plan-affecting convert warning added previously is not tracked and removed when this substitution occurs.

Like most warnings, the plan-affecting convert warning is opportunistic and informational. It is opportunistic in that it is only added if the optimizer follows a code path that attempts to compute cardinality on a suitable expression. This is similar to 'missing index suggestions', which are only added if the optimizer attempts to match to an 'ideal' index definition that is not found. To put it another way: neither of these facilities are based on exhaustive analysis.

I tried using a UDF in the computed column definition to get rid of the warning

This 'works' because the optimizer cannot expand a scalar UDF into its definition before optimization. The UDF is a 'black box' with guessed cardinality and horrible runtime performance (close to the cost of running a complete separate query per function invocation). Without expansion, a cardinality estimation that might generate the warning cannot occur.

Is there a reason to consider the warning anything other than a bug?

The warning indicates that the convert may affect plan quality because a cardinality estimation was performed on a problematic expression. The complexity of compilation and optimization is such that it is impossible to say if the cardinality estimate will affect the final quality of the plan or not, even if the computed column does end up being resolved to a persisted value or index.

So, the warning is generally useful because it indicates that internal optimization decisions may have been adversely impacted by the convert. I would always check a plan with this warning for inaccurate cardinality estimations, and any resulting performance or resource usage implications.

A final aside: the SQL Server version is not mentioned in the question, but for 2012 and later, TRY_CAST or TRY_CONVERT are more robust ways of handling this sort of requirement. This facility will not generally affect convert warnings one way or the other though.