I like topics like this. It gets me thinking. I would like to present a scenario where dynamic SQL is a better solution, as far as I can see. If you have a partitioned view with lots of tables, when you query the view you want to pull from as few tables as possible, so you want to include the partitioning field in your query as much as possible. If you specify the filter using a variable, then look at the Estimated Execution Plan, you will see that SQL Server goes through all the tables in the view; but if you specify it as a literal (such as '1/16/01' for a date), then SQL Server will only pull from the table(s) that are necessary. I have noticed a huge performance difference with this.

To put this in a stored procedure where the filter value is passed in as a parameter, the only way I know of to get the value specified as a literal is to use dynamic SQL.

My theory on why this is so, is that SQL Server determines which tables of a partitioned view to use in a query when it is initially parsed, at which time the filter values are not known if you use variables.

If there is a better solution to this, I'd be very interested in hearing about it.

James C Loesch