I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like
DBCC UPDATEUSAGE or
UPDATE STATISTICS XXX.
At statistics update, I get following error:
Msg 402, Level 16, State 1, Procedure ZZZZ, Line 5 [Batch Start Line 0] The data types datetime and time are incompatible in the add operator. Msg 4413, Level 16, State 1, Line 1 Could not use view or function 'ZZZZ' because of binding errors.
I know that the message is quite explicit (the view, which was syntactically correct on 2008R2 is not anymore on 2019). I don't understand why a view defined
WITH SCHEMABINDING that is not valid would prevent the update of statistics on an underlying table.
Moreover, I get the same error message when querying the underlying table with a
WHERE clause, except if I force a FULLSCAN with following hint:
OPTION(TABLE HINT( $mytable, FORCESCAN ))
I know SQL Server will fire an error in case of DDL change to underlying schema-bound objects, but I don't understand why, if the view is invalid, the underlying objects cannot be used as usual.
I'm sure there is an explanation (like the protection mechanisms for schema-bound views cannot be processed due to the invalidity of the view), but I can't find it for sure in the documentation.
I ran a
DBCC CHECKDB without problem. I changed the compat level to 150.