question

mdullni1 avatar image
mdullni1 asked

'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?

syntaxerrorsclause
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered

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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·

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 Likes 0 ·

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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