Conceptual
Browse Instruct Curate
Login

Implicit Type Casting: When the Engine Converts One Side of Your Comparison

When the two sides of a comparison have different types, the database converts one of them so they can be compared, and you usually never see it happen. Which side gets converted decides whether an index is usable: converting your literal is harmless, while converting the column means the engine must compute a new value for every row and the index over the original column is useless. This is why comparing a text column to a number, or a timestamp column to a date, can make a perfectly good index disappear from your plan.

Questions this Concept answers

  • Why does converting the column side of a comparison make an ordinary index on that column useless?