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
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
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.