Does query optimization phase consider this?

Question

I have a table with a date column StartDate indexed. I was just wondering how the query optimization phase handles queries of the following form:

SELECT * 
FROM <dbo.TABLE> 
WHERE (StartDate BETWEEN '2010-01-01' AND '2010-01-10')
OR (StartDate BETWEEN '2010-01-05' AND '2010-01-15')

There is an overlap between the two dates. Should I be doing the optimization myself of giving it the dates as (StartDate BETWEEN '2010-01-01' AND '2010-01-15') or can the SQL engine optimize this by itself?

Answer 1

Compare it with variables and constants: the plans should differ.

SQL Server can optimise for constants but aims for re-use with variables. So the plan is more general with variables. When constants are used, a "generalised reusable plan" isn't required because if the constants change it will be a new plan

A "variable" plan won't consider conditions like end < start which would be short circuited with constants.

As noted in comments to the question, behaviour is as expected

You should make the same happen for constants with "Forced Parameterization", but I haven't tried.