SQL Server 2017: OR and LIKE Interaction I am using SQL Server 2017 (i.e. v14).
I could use one of the following equivalent criteria in a query:
x IN ('104','504','554')
(x='104' OR x='504' OR x='554')
(x='104' OR x Like '5[05]4')

If I use either of the first two, the query takes around ten times longer to execute than the third one.  Originally, different query plans were produced.  However, as I stripped the query down, I managed to create simple queries which produced exactly the same plan for all three.  However, only the query using the third statement ran quickly and those using the other two ran slowly.
Since the query plans are identical, it leads me to believe that there is something special about a formulation with an &quot;= OR Like&quot; structure.
I have also tried these similar but non-equivalent alternatives:
x Like '[15][05]4'
(x='104' OR x='504')

They took a long time to run, and their query plans were slightly different.
I would like to understand what is going on so that I can adapt my query writing appropriately!  (At least, until I have a new version of SQL server to play with and retest this peculiarity.)
Edit: query plan attached.
Even though the asker didn't provide actual execution plans for comparison, I found the question interesting and dug up a little. I am pretty confident that the difference is due to implicit conversion introduced through the LIKE keyword. I made small script showing the effects of it:
CREATE TABLE T1 (A INT NOT NULL)

;WITH CTE AS ( SELECT X.A FROM (VALUES (101), (102), (103), (104), (105), (155), (107), (108)) X(A))
INSERT INTO T1
SELECT CTE1.A FROM CTE AS CTE1
CROSS JOIN CTE AS CTE2
CROSS JOIN CTE AS CTE3
CROSS JOIN CTE AS CTE4
CROSS JOIN CTE AS CTE5
CROSS JOIN CTE AS CTE6

INSERT INTO T1 
VALUES (505), (555)

SELECT COUNT(1) FROM T1 WHERE A IN ('101', '605', '655')

SELECT COUNT(1) FROM T1 WHERE A = '101' OR A LIKE  '6[05]5'

If you check the execution plans of the last two selects you see it clearly:

The second has wrong estimates due to implicit conversion. In this case I scewed the data on purpose, so it got overestimated.
In this simplistic query it doesn't matter, but the real query probably has overall too low estimates, which leads to tempdb spills during hash joins. Changing IN to LIKE inflates the expected number of rows (like in my example) and increases memory grant. This in turn reduces/prevents tempdb spilling, which speeds up the query.