DaveWentzel.com            All Things Data

Using Fully Qualified Names

 

So, does using fully qualified names really help performance?  We've all heard it and many have poo poo'd it.  So how does using fully qualified names affect query plans and performance? 
 
Well, the default schema does not have to be looked up when the query is executed.  The plan can also be executed by multiple users regardless of default schema. 
 
Test with the following variation of queries against one of your databases as a user who has access to that default schema. 
 
select * from tbl
 
select st.text, cp.plan_handle, cp.cacheobjtype, cp.objtype, pa.attribute, pa.value, pa.is_cache_key

from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(plan_handle) st

outer apply sys.dm_exec_plan_attributes(cp.plan_handle) pa

where cp.cacheobjtype = 'Compiled Plan'

and st.text not like '%select st.text%'

and pa.is_cache_key = 1

order by pa.attribute;

go
 
will show a value of 7 which means the plan can't be shared across users.
 
select * from schema.tbl
 
The cache value will name show -2 which means the plan will be shared across multiple users regardless of default schema.  

Add new comment