Is it more efficient to join on LIKE or IN SPLIT_STRING() for a string of comma separated values in-CodePudding

I'd like to join on a long string of comma separated values to a single value, for example, lets say all towns in the U.S.:

Would it make more sense to structure my join like:

[TableA].[Town] IN (SELECT VALUE FROM SPLIT_STRING([TableB].[TownList],','))

[TableB].[TownList] LIKE ('%' [TableA].[Town] '%')

Just as a hypothetical example, I'd like to understand better what these are doing on the backend and which makes sense in which scenarios?

Thanks

CodePudding user response：

In a theoretical, abstract viewpoint, you are joining two tables, and you are wondering which join condition outperforms the other. On expression levels, I've noticed that it is rarely worth the effort to try to analyse. Things that are CPU dependent doesn't seem to have a big role in SQL server. I've rarely seen posts analysing expression performance. I can only recall one of the big names indicating format function being expensive.

On the contrary, what plays a massive importance is the plan used. This is not something we can answer though; it depends on indexes, table sizes, and other metadata.

So, like all queries, to understand what is happening and gain meaningful results, you have to read and understand the execution plan. And that is why in all performance questions asked here, the execution plan is always requested.