I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both enterprise edition) using 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 in MSSQL 2008R2 is not anymore in MSSQL 2019) but I don’t understand why would 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 MS documentation…