We need to add the
'FORCE_LEGACY_CARDINALITY_ESTIMATION' option to a view. When we run the select with the option clause
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
in SSMS, it works. When we try to alter the view with the option clause, it errors with
Incorrect syntax near the keyword 'OPTION' and
USE database statement is not allowed in a procedure, function or trigger.
How do we put the option hint in a view?