I did a quick test kind of following Gregory's suggestion except I did not use profiler I user Set Statistics Time On. The first thing I did was create a user who did not "own" the dbo schema nor have dbo as it's default schema, but did have select permissions on the dbo schema. I wanted to make sure I was crossing schemas. I also ran DBCC DropCleanBuffers and DBCC FreeProcCache between each execution. Then I ran it only executing the DBCC statements before the first run of each query. The query did use a covering non-clustered index as well. I was trying to stick it in here, but decided the formatting wasn't appropriate so I attached an Excel file instead.

A quick summary is that, if the plan is in cache they took about the same time. When the plan was not in cache, the non-qualified query took about 33ms to parse and compile and the qualified query to 1ms to parse and compile. This is a very big difference in performance.

I also ran it with the users default schema being DBO, results not in the Excel File, and the Parse and Compile times were the same after running the DBCC statements. This would lead me to believe that in SQL 2005, if you have only 1 schema, that they both perform the same way. I am not going to make any definitive statements based on this limited testing though.

I think the biggest argument for using fully qualified names in ad-hoc SQL is consistency so that you only get 1 plan in cache for each query vs. 1 for non-qualified queries and 1 for qualified queries. This allows you to have more distinct plans in the cache.

Paul,

I guess I was slightly confused in terminology between compiled and cached. I know that ad-hoc queries plans are cached and, after looking it up in BOL, realize that this is what compilation really is. Thanks for the prompt.