Object Name Resolution - Table

How SQL Server resolves object names if you have not referred it is as a fully qualified object? If it is not a fully qualified object (a statement like below);

USE AdventureWorks2014;
GO

SELECT * FROM SalesOrderHeader;

it goes through additional steps for making it as a fully qualified object for resolving the name of the object. It is an additional cost for the execution engine and it adds extra time needed to execute the query. Not only that, if it cannot resolve the object, it throws an error saying Invalid Object even though the object is exist. Have a look on the image below.

Let's talk about the first example. Jane executes a query against SalesOrderHeader. She has not mentioned the schema of the table, hence it is treated as a non-fully-qualified-object. SQL Server tries to add her default Schema which is Sales. Once the schema is added, it becomes Sales.SalesOrderHeader and SQL Server could find the object. She executes the query successfully with an additional overhead.

Jack tries to execute the same query. Since Jack's schema is Finance, SQL Server initially tries as Finance.SalesOrderHeader. Since it does not success, it tries with its second Try which adds dbo schema. Again, dbo.SalesOrderHeader does not exist, hence it fails. Jack gets an error.

Realized how important it is to mention the schema? Make sure objects you have mentioned in your queries contains fully qualified object names for avoiding extra works and errors.