{"id":7930,"date":"2015-04-30T02:22:44","date_gmt":"2015-04-30T02:22:44","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/why-you-should-always-use-schema-name-in-queries\/"},"modified":"2016-07-28T10:56:32","modified_gmt":"2016-07-28T10:56:32","slug":"why-you-should-always-use-schema-name-in-queries","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/why-you-should-always-use-schema-name-in-queries\/","title":{"rendered":"Why you should always use schema name in queries"},"content":{"rendered":"

There is a very important reason to always use the schema name in the queries. For example, the following sintax can be a problem:<\/p>\n

select * from customers<\/pre>\n
The solution is to use the schema name in the query:<\/p>\n
select * from dbo.customers<\/pre>\n
The reason is simple: SQL Server does cache the query plan for ad-hoc queries, but if the schema name isn’t present in the query the cache can’t be re-used for other users, only for the same user.<\/p>\n
This happens because the same database can have tables with the same name and different schemas. For example, we could have a table Customers in schema Sales and another table Customers in schema Marketing.<\/p>\n
If jonh uses Sales as default schema and Mark uses Marketing as default schema, the same query has different meaning for each use. Because this possibility SQL Server will not re-use plan caches for different users unless the schema is specified in the query.<\/p>\n
Even if this situation couldn’t happen in your database, even if you don’t have tables with the same name, SQL Server can’t take this risk.<\/p>\n
We can prove this in a test environment with northwind database.<\/p>\n
First, clear the query plan cache (you should only do this in test environments):<\/p>\n
dbcc freeproccache<\/pre>\n
Now let’s execute one query:<\/p>\n
select * from customers<\/pre>\n
We can check the information registered in the cache. We need to look for the attribute user_id of the cache objects:<\/p>\n
with qry as \n     ( select refcounts,usecounts,size_in_bytes, cacheobjtype,objtype, attribute,value,plan_handle \n             from sys.dm_exec_cached_plans ecp \n                    outer apply sys.dm_exec_plan_attributes(ecp.plan_handle) epa \n              where epa.attribute='user_id') \nselect refcounts,usecounts,size_in_bytes,cacheobjtype, objtype,attribute,value, objectid,[text],[dbid] \n               from qry cross apply \n                           sys.dm_exec_sql_text(qry.plan_handle) \nwhere dbid=db_id('Northwind')<\/pre>\n
You can see in the following image that our query has a value of 1 for user_id.<\/p>\n
\"tmp1.png\"<\/a><\/p>\n
Let’s see who is the user 1:<\/p>\n
select user_name(1)<\/pre>\n
It’s DBO. This means that this object in the cache can only be re-used by the DBO. Any other user can’t re-use this cache object.<\/p>\n
Let’s try again:<\/p>\n
First, clear the cache:<\/p>\n
dbcc freeproccache<\/pre>\n
Then let’s execute the query, now using the schema in the query:<\/p>\n
select * from dbo.customers<\/pre>\n
Let’s check again the cache objects:<\/p>\n
with qry as \n     ( select refcounts,usecounts,size_in_bytes, cacheobjtype,objtype, attribute,value,plan_handle \n             from sys.dm_exec_cached_plans ecp \n                    outer apply sys.dm_exec_plan_attributes(ecp.plan_handle) epa \n              where epa.attribute='user_id') \nselect refcounts,usecounts,size_in_bytes,cacheobjtype, objtype,attribute,value, objectid,[text],[dbid] \n               from qry cross apply \n                           sys.dm_exec_sql_text(qry.plan_handle) \nwhere dbid=db_id('Northwind')<\/pre>\n
You can notice the user_id attribute with -2 value, this means that this object can be re-used by any user.<\/p>\n
\"tmp2.png\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
There is a very important reason to always use the schema name in the queries. For example, the following sintax can be a problem: select * from customers The solution is to use the schema name in the query: select * from dbo.customers The reason is simple: SQL Server does cache the query plan for……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-7930","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7930","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=7930"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7930\/revisions"}],"predecessor-version":[{"id":42481,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7930\/revisions\/42481"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=7930"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=7930"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=7930"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=7930"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}