T-SQL coding

Why should I preface objects with an owner?

In my SQL Server consulting travels I often get asked to put together a checklist of items for T-SQL developers; a sort of “dos” and “don’ts” to follow when developing or maintaining T-SQL code for applications. Most of the time this checklist turns out to be a list of commands to follow (i.e. “thou shalt do this” or “thou shalt not do that”) without much context behind each command. Well, I going to try and help you out with a series on the context behind the most common checklist items that I use/see in my career. Let’s begin with the notion of explicitly using the owner or schema name when referencing database objects in T-SQL code.

The checklist item often reads something like this:

Make sure all objects reference the appropriate schema

In other words, don’t code this:

SELECT InvoiceNumber, PartNumber, UnitPrice

FROM InvoiceDetail …

 

Exec usp_MakeWidgets …

 

Instead, code this:

SELECT InvoiceNumber, PartNumber, UnitPrice

FROM Finance.InvoiceDetail …

 

Exec dbo.usp_MakeWidgets …

 

So…what’s the big deal? My stored procedure runs “fine” without schema names in front of my table references! And besides, we only use the dbo schema here at Acme Warehousing!

Yeah, I hear this all the time…especially from application developers. Let me explain why this is a good practice in your T-SQL code.

You may think that your procedure runs “fine”, and most of the time, it probably does (in your humble opinion), but you need to think about running your stored procedure in the environment for which you are writing it (i.e. your production application). When you don’t prefix your objects with schema names, this forces SQL Server to walk through its own hierarchy to select the correct object. First, SQL Server will look for the object under the user’s schema, then it looks in the Master database, then it looks for the object under the database owner (dbo)…well, you get the idea. You are making SQL Server do more work than it has to each time your code is executed; not a big deal if you are the only user, but when your code is executed by hundreds of users using your application this extra work can add up.

Another side benefit of this is better plan re-use. If you don’t qualify your object names then the same code can reference different objects (think about it, what if you had tables called steve.InvoiceDetail, sue.InvoiceDetail, and dbo.InvoiceDetail?). The database engine is written to handle this situation, so your code has the potential of being re-compiled upon re-execution just to figure out which table you should be referencing, which can cause bad plans to show up in cache…and we just don’t want to go there today! Let’s just say that by using 2-part qualified names you are promoting plan re-use and saving the engine time and effort.

So, as the Books Online states, use 2-part naming of objects (that means tables, views, functions, procedures, etc.) always!

Note that I don’t say anything about 3- or 4-part naming of objects; that’s a whole other topic…My advice to you is to NOT add the database name or the server name to any object UNLESS YOU HAVE TO.

 

Advertisements

One thought on “Why should I preface objects with an owner?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s