Why you should always use schema name in queries

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

The solution is to use the schema name in the query:

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.

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.

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.

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.

We can prove this in a test environment with northwind database.

First, clear the query plan cache (you should only do this in test environments):

Now let’s execute one query:

We can check the information registered in the cache. We need to look for the attribute user_id of the cache objects:

You can see in the following image that our query has a value of 1 for user_id.

tmp1.png

Let’s see who is the user 1:

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.

Let’s try again:

First, clear the cache:

Then let’s execute the query, now using the schema in the query:

Let’s check again the cache objects:

You can notice the user_id attribute with -2 value, this means that this object can be re-used by any user.

tmp2.png