I'm using a database splitted into several databases I will refer to as "partitions".
Each partition holds the same data structure for a particular month.
A view on the top of all the partitions does a big UNION for data retrieval, and each table of each partition has a CHECK CONSTRAINT based on date bounds that helps the query optimizer skip the unneeded partitions for a particular query between two dates.
Everything works fine so far.
However, when I submit a query with, let's say: WHERE [Date] > @StartDate, @StartDate being a variable, the query plan shows that every single partition takes part of the UNION.
I found some ressources advising to prefer parameters to variables, but the same happens.
I even changed my query into a stored procedure, passing date bounds as parameters, and added the WITH RECOMPILE hint, with no luck.
Issuing a DBCC FREEPROCCACHE didn't help either.
I'm considering using dynamic queries, concatenating the query and the parameters so I submit literal date bounds, as my best option here. But that makes me really unhappy (I guess you can see why). Furthermore, even if there's no better workaround, I'd like to at least understand what's going on.
