5

I see that sql join internally functions always on some of the three basic operations : nested loops join, merge join and hash join.

From multiple articles on the internet I see SQL casting is said to be able to impact performances severely and almost always the reason is it disables index usage.

But to me it looks it could always be avoided: Casting could disable merge join usage (and merge benefits from index usage) but hash join, that is said to be generally faster than merge, can still be utilized in the same way(looks to me). No matter in which phase of the hash join (build of probe) casting is applied, all that has to be additionally done is casting, before hashing, for the column casted.

Hash join, anyway, passes every row of the first table once, same as for the second table. Hence, from my aspect, casting of join columns, applied to hash join, has not performance impact but the casting operation itself. And the guys on the internet say casting severely impacts performances since indexes cannot be used?

Is something wrong in my thinking above? Thank you for the time

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Срба
  • 151
  • 1
  • 2

1 Answers1

3

CAST or any other function is expensive when used in a JOIN condition because it makes the transformed column(s) be non-SARGable.

The database has no way to know what the output of the function will be until it runs the function. This is as true for CAST as it is for a UDF that you write which does some fancy string manipulation logic.

The condition being non-SARGable means that the database can't use a search argument to find the value - it has to run it on every single row that matches the other criteria in the query.

For this reason, functions should be avoided in any filtering logic - JOINs, WHERE clauses, etc. wherever possible, as it essentially forces a table/index scan.

JNK
  • 18,064
  • 6
  • 63
  • 98