'FORCE_LEGACY_CARDINALITY_ESTIMATION' hint in view

Question

question

mdullni1 asked Feb 28, '19

'FORCE_LEGACY_CARDINALITY_ESTIMATION' hint in view

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?

syntax errors clause

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-02-28T13:42:49Z

KenJ answered Feb 28, '19

You don't use the option clause inside the view definition. You would use option when selecting from the view:

select * from your_view
option(use hint('force_legacy_cardinality_estimation'))

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mdullni1 commented · Feb 28, 2019 at 01:55 PM

Thank you for your response @KenJ.

So, we have a view that an external group selects against. That view has the problem view (that needs legacy) joined in it. If I understand correctly, we can't invoke the legacy cardinality from within the view we are providing for the external source?

0 ·

KenJ mdullni1 commented · Feb 28, 2019 at 02:06 PM

That is correct. Is the external source able to use a stored procedure? That would be a viable way to wrap the query you want to create.

0 ·

mdullni1 commented · Feb 28, 2019 at 02:09 PM

Thank you. We will have to rewrite the problem view to work with 2016 cardinality. The external source only selects against views as a standard.

I have accepted your answer.

0 ·

question