I have a query that is running very slow so I started looking into the Execution Plan. TRY_CAST is a safe way of finding out if the data can be cast to a type. And there is a warning in the plan regarding TRY_CAST.

 Type conversion in expression try_cast may affect "CardinalityEstimate" in query plan choice

Please note that I am not a DBA but a coder. Treat me as a novice when I comes to database stuff.

What should be best approach to improve the speed of the query (based on the warning in the title of this question)? Is there anything other than TRY_CAST that might help speed up the query, for example, CAST on its own is prone to exceptions and the only way to safe cast that I know of is TRY_CAST?

enter image description here


Basically, this could mean that the type conversion cannot effectively use an index. Cardinality is a important property of the indexes that you might use. For performance, the ideal is to have the data types match, instead of trying to convert them on the fly.

You will find Thomas LaRock's post on this useful: http://thomaslarock.com/2012/08/why-datatypes-matter-3-ways-they-can-hurt-performance/

  • Thanks! This was an issue for me where one field was varchar(max) and the other had a set length.
    – Kevin Pope
    Jun 2 at 4:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.