qualified objects

Using Fully Qualified Object Names and DEFAULT_SCHEMA for performance gain

As a rule of thumb you should always use fully qualified object names in your T-SQL code. I know that many find it inconvenient, many are not even using different schemas, but still, using fully qualified object names can give your database a performance boost.

In SQL server you can define schemas and you can define on object creation, in which schema this object should be created. When accessing the object you can use the fully qualified name of the object, consisting of up to three parts

database.schema.object

We will ignore the first part, the database, for now. The database should be defined by the connection anyway. This article concentrates on the schema qualifier.

select ID from myschema.mytable;
exec dbo.uspMyProcedure;
select * from dbo.myView;
select ID, dbo.myFunction(myColumn) as calcVal from dbo.myView;

But why should you when this

select ID from mytable;

works just as well?

Well, that´s the point: it doesn’t work as well.

Reads

Let´s take a look at what happens when we execute the statement without schema qualifier. We’ll assume we are executing the statement as userX. SQL Server first looks for the object in the user’s default schema, which pre-SQL Server 2005 was the user’s own. So SQL Server looks for userX.mytable. The object does not exist, so SQL Server looks in the dbo (the overall default schema), so it looks for dbo.mytable, finds it and returns the data.

Note: If the table was in another schema than userX or dbo, the second statement wouldn’t work at all, even if the userX has access to the other schema. SQL Server stops its search. You will have to use a schema qualifier in any case.

One step to solve this is to set the DEFAULT_SCHEMA for userX. Since SQL Server 2005 the default for the DEFAULT_SCHEMA on user creation is dbo. You can look up the default schema for all users in the column default_schema_name of the DMV sys.database_principals.

select * from sys.database_principals

You can also set the default schema with this

ALTER USER [Username] WITH DEFAULT_SCHEMA=dbo;

This means Server directly looks in dbo for the object dbo.mytable.

So all is good, isn’t it? Well, not quite.

SQL Server still has to look up the user’s default schema. Yes, we saved one of two unnecessary reads, but why not save all? Just tell SQL Server where the object is. One additional read, mostly from the cache, may not sound like much, but just imagine a big database system with hundreds of transactions and queries per second. Every unnecessary operation you save can make a difference, and by schema-qualifying your objects you can save at least one, depending on your user-schema-default schema-architecture even two operations for every statement.

Finding Objects

In an architectare where you actually do have several schemas, fully qualifying the object names is an absolute must! Imagine userX and userY each have their own schemas and there is a dbo schema as well.

Your application issues the statement

truncate table customer;

UserX executes the statement in his default schema, which contains the table userX.customer with his/her private customer table.

truncate table userX.customer;

UserY doesn’t have a table customer in his/her default schema, so dbo.customer is truncated.

truncate table dbo.customer;

UserX, finding the private table truncated drops the table in the schema userX. He/she executes the statement again, this time, since userX.customer doesn’t exist anymore, on dbo.customer….

And so on.. I hope you see where I am going with this.

Reusability of Execution Plans

Last and not least, a big performance advantage of SQL Server is the reusability of execution plans. In a scenario like the one above SQL Server cannot rely on the object targeted being the same on every execution. It depends on the user executing the command. So in this case SQL Server will not reuse the execution plan but will recompile the statement every time.

All this may not make a difference in every database and every project you do, but if you make a habit out of using schema.object as your way of writing T-SQL, you will never have to think about, if it could.

%d bloggers like this: