This is one of my least favorite query patterns, because even with appropriate indexes, performance often isn’t very good without additional interventions.
Without indexes in place, or when “indexes aren’t used”, then the query plans will often look like one of these.
Maybe not always, but there are pretty common.
It’s something that I do wish the optimizer could be smarter about. One important thing to note in Erik’s demo: the OR clause is on two different columns, so
SELECT x.Col1 FROM dbo.TblX x WHERE x.ID = 8 OR x.ID = 7 works fine, but
WHERE x.ID = 8 OR x.SomethingElse = 14 is liable to cause performance issues on a large enough table.