When Andy recently added his latest piece in the Worst Practices series,

about adding a column without thinking, it made me think about a worst practice

I have been trying to combat for a long time. Maybe not as specific as most

other worst practices, but one of the worst practices I often encounter is plain

and simple laziness. One mistake that people often do just because they are lazy

is to not qualify the names of objects with owner name. I will discuss this

here, and in a later article I will discuss some other mistakes caused by

laziness.

Using qualified names

Always remember to qualify object names with the owner of the object. This is

good for performance for at least two reasons. The first is that when you

specify an object name without qualifying it with owner name, SQL Server will

first look for an object owned by the current logged on user. If not found, an

object with the same name but owned by dbo will be used. So if a user named John

is logged on to Northwind and executes SELECT OrderID FROM Orders, SQL Server

will look for the object John.Orders in Northwind, and when not found dbo.Orders

will be used. If he would have executed SELECT OrderID FROM dbo.Orders instead

SQL Server would not have needed to look for an object named John.Orders. This

might not affect performance in any notable way, but it is also important to

avoid using an unexpected object. Imagine an application that logs on as this

user John and executes a SELECT OrderID FROM Orders statement. This works fine

until one day someone logged in as John decides to create a table called

John.Orders (for whatever reason). Now the application will suddenly start

returning OrderIDs from the new table John.Orders, and this bug might be pretty

hard to find.

The other reason it is good for performance to qualify object names with

owner name is that there is a much better chance of SQL Server reusing execution

plans for statements written that way. For instance, if you are using

sp_executesql to execute dynamic SQL statements you must actually fully qualify

object names for SQL Server to reuse the execution plans. Fully qualifying an

object name means specifying all four parts of it, i.e.

server_name.database_name.owner_name.object_name. If you omit any of these a

default will be used. Default server and database is of course the server and

database where the query is executed, and default owner_name is as earlier

specified dbo. But even though a default is used, SQL Server will not reuse the

execution plan for statements specifying differently qualified object names. You

can see this for yourself in SQL Profiler by running the following small script.

Start Profiler and connect it to your server. Add all events in the Stored

Procedures event class and remove all other events.

DECLARE @sql nvarchar(200)
DECLARE @params nvarchar(100)
DECLARE @intvar int
-- Create statement using fully qualified names
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM dbo.Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 3
EXEC sp_executesql @sql, @params, @empid = @intvar
-- Execute the same statement again, reusing the previous execution plan
-- Note SP:ExecContextHit in Profiler
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar
-- Same statement but not using fully qualified names
-- Note there is no SP:ExecContextHit in Profiler
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

When you run the script you should see an SP:CacheInsert when the first query

is executed, but when it is executed again using only a changed value for @empid

you will instead see an SP:ExecContextHit. This means that SQL Server could

reuse the execution plan. Finally, when the third query is executed, you will

again see an SP:CacheInsert, even though the result of the query is exactly the

same as for the prior one. The reason is as I explained earlier that the second

statement does not qualify the name for Orders in the same way as the first

statement does.

Specifying the owner name is not just important when executing SELECT

statements. It is also important, arguably even more important, to qualify

objects with owner name when creating them. For instance, if John would create a

table without specifying the owner of it he would become the owner himself. This

means that any other user who wants to execute a SELECT statement against this

table must qualify the table name with his owner name when SELECTing from it. If

they would also always remember to qualify object names it might not be that

much of a problem, but since we know people are lazy (or do not know what it

means to qualify a name) they probably will not always do that. So when John

creates his table his statement should begin with CREATE TABLE dbo.TableName ...

There are of course exceptions when you really want to create a table with John

as owner, but in those cases he should specify that, i.e. CREATE TABLE

John.TableName ... Why is this important? Imagine that John creates this table

in a database on a test server and saves the statement in a script. Later, when

this table is to be created in the production database it might be someone else

that executes this script, and the table will get a different owner.

It is even more important to qualify names of objects used inside a stored

procedure. Otherwise objects that are not qualified with owner name and

referenced in SELECT, INSERT, UPDATE and DELETE statements will default to the

owner of the stored procedure, not the person executing the stored procedure.

Again, these objects might be those you want, but it is always better to

explicitly qualify the names. If the procedure is used as an access control

mechanism for data in tables (i.e. direct access to tables is restricted and all

users must use procedures that SELECT data from them) yu might run into another

problem. If those object names for the tables that are restricted are not

qualified in the procedure only the creator of the procedure will be allowed to

access them through it.

Finally, if you do not qualify object names used in procedures with the

statements CREATE/ALTER/DROP TABLE, TRUNCATE TABLE, CREATE/DROP INDEX, UPDATE

STATISTICS and DBCC commands you might cause havoc in the system. The reason for

this is that object names are resolved at run-time, using the name of the user

that executes the procedure as the default owner for objects that are not fully

qualified. Imagine that the user Jane has a table called Orders where she keeps

her order info. Now she needs a quick and easy way to empty it so she creates a

procedure like the one below:

CREATE PROCEDURE TruncateOrders
AS
BEGIN
  TRUNCATE TABLE Orders
END

She tries the procedure in a test environment and it works great, her Orders

table gets truncated so she moves the procedure to the production environment.

Then one day John needs to empty the data from Jane's Orders table, so he

executes the procedure (by running EXEC Jane.TruncateOrders). Guess what

happens? Well, if there is a table called John.Orders it would be truncated, and

if there is not, then dbo.Orders will be truncated since dbo is the default that

is used when SQL Server does not find an object owned by the current user.

So, for the reasons listed above I would recommend you to make it a habit to

always specify all objects names using the syntax owner_name.object_name.

And I am not just saying you should write your procedures this way, I want you

to make it a habit to always do it, even for quick ad-hoc queries that

you just execute once. If you make it a habit you will not forget it when it is

really necessary and important.