THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Bad habits to kick : avoiding the schema prefix

In my last post in this series, I treated the dreadful SELECT * and other ways we get around typing out a column list.  This time I want to discuss the use of the schema prefix.

This has to do with both creating and referencing objects.  Do not make any assumptions about which schema an object belongs to.  All your objects belong to dbo?  Ok, use the dbo prefix anyway.  Why?  Because you will use additional schemas someday, or some 3rd party will force them on you, or even Microsoft (for example, Change Data Capture).  Why leave it up to chance?  Typing "dbo." is not that much work... and once you get into the habit, it will be no work at all.  Again, this is another case where there is nothing lost by being explicit, but there is plenty to lose otherwise.

Without an explicit schema, SQL Server will first look under the schema associated with your login.  This can cause problems, obviously, if you have a table called your_default_schema.foo and there is also a table in the database called dbo.foo.  SQL Server will pick the one under your default schema, and *might* be making the wrong choice, if it is not what you intended, but how can it know any better?  If you tell it explicitly which schema you are after, there is no chance for confusion.  In fact, you might have created one of the objects accidentally, by not using the schema prefix during creation.  (As an aside, you always have to qualify scalar user-defined functions with a prefix.  So, if you use functions a lot, you're probably already well on your way.)

Here is a quick example:

USE [master];
GO

SET NOCOUNT ON;

CREATE LOGIN
foo WITH
PASSWORD
= 'foo',
   
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO
CREATE DATABASE blat;
GO

USE blat;
GO

CREATE TABLE bar VARCHAR(32) ); -- this is dbo.bar!

INSERT barx ) SELECT 'dbo created this.';
GO

SELECT FROM bar;
GO

CREATE SCHEMA foo AUTHORIZATION dbo;
GO

CREATE USER foo FOR LOGIN foo WITH DEFAULT_SCHEMA = foo;
GO

EXEC sp_addrolemember 'db_owner''foo';
GO

EXECUTE AS USER = 'foo';

CREATE TABLE 
bar VARCHAR(32) ); -- this is foo.bar!

INSERT 
barx ) SELECT 'foo created this.';

SELECT 
FROM bar;
GO

REVERT;
GO

SELECT
   
[table] = OBJECT_SCHEMA_NAME([object_id])
       + 
'.' OBJECT_NAME([object_id])
       
FROM sys.tables
      
WHERE [name] = 'bar';
GO

USE [master];
GO

DROP DATABASE blat;
GO

DROP LOGIN foo;
GO

Results:

x
--------------------------------
dbo created this.

x
--------------------------------
foo created this.

table
--------------------------------
dbo.bar
foo.bar

Using schemas is a complex topic, and I don't want to get into all of the security ramifications or try to coerce you into using them.  For those of you that are already using multiple schemas, you have probably already hit most of the big issues.  I just wanted to suggest that you get into the habit of using the prefix whenever you create or reference objects in T-SQL code, even if you are currently only using objects in dbo.  You might thank me later.

I am working on a series of "Bad habits to kick" articles, in an effort to motivate people to drop some of the things that I hate to see when I inherit code.  Up next: inconsistent naming conventions.

Published Sunday, October 11, 2009 11:20 AM by AaronBertrand

Comments

 

Jim Danby said:

I fear that this is too simple a description of a complex topic.

October 11, 2009 12:10 PM
 

AaronBertrand said:

Jim, the issue I wanted to address was leaving out the dbo. prefix in an all-dbo world.  I could spend a week writing about all the security implications and use cases of a multi-schema world, but that wasn't the goal of my post (and I doubt I could do any more justice to that than Books Online and authors before me have already done).  Obviously, if you have some wisdom to share, please do so...

October 11, 2009 12:41 PM
 

Greg Joiner said:

I think Jim's suggestive/supportive comment meant that a simple example of having some comment code to illustrate this serious issue would have improved your article..

a dbo.foo and a Aaron.foo and showing a select foo getting Aaron when you wanted to get a dbo. etc.

October 12, 2009 9:08 AM
 

AaronBertrand said:

Point taken; I've added an example.  Hope it is useful.

October 12, 2009 9:38 AM
 

Brian Tkatch said:

I take the exact opposite position. I always leave the schema off. Then again, i come from an Oracle background where different databases are less used.

I like to copy all my code into a new schema to test something. With one schema called aaron, and another called aaronv2, it is very easy to copy everything over when no schema is mentioned.

This is very similar to websites and relative URLs, which i think is also a very good idea.

The only time i mention a schema, is when the project itself demands that a particular schema be used.

October 12, 2009 12:33 PM
 

Luciano Evaristo Guerche (Gorše) said:

Aaron,

As far as I remember that scenario you described related to schema(s) and table(s) scoping is named "shadowing".

Regards,

October 13, 2009 12:54 PM
 

AaronBertrand said:

I noticed Erland's great article on dynamic SQL has some more ammo in favor of being in the habit of always specifying schema:

http://www.sommarskog.se/dynamic_sql.html#queryplans

October 18, 2009 5:23 PM
 

Aaron Bertrand said:

T-SQL Tuesday, the invention of Adam Machanic (@AdamMachanic), is what he calls a recurring, revolving

July 12, 2011 8:45 AM
 

AaronBertrand said:

Here is another reason to always use the schema prefix on all objects that support them:

http://sqlblog.com/blogs/maria_zakourdaev/archive/2014/09/02/choosing-sequence-instead-of-identity-watch-your-step.aspx

September 2, 2014 10:28 AM
 

Dacius said:

Well said.

June 29, 2015 2:47 PM
 

Forrest said:

A simple way to introduce this concept to new SQL developers is to convince them that learning to use and specify schemas isn't just about extra typing and security; its also a very good organization practice.

On one hand that isn't entirely what schemas are about and you CAN overdo it, but it DOES help you keep your fences up between projects, and you can't accidentally wipe out critical code as easily.

If you've only written a handful of scripts and have no access to the security layer of your environment, then it may be lost on you; but consider taking ALL of your development scripts EVER and simply dumping them in one giant folder on your hard-drive and then trying to find that one query you KNOW you wrote just 6 months ago for a project that would be handy now.

August 12, 2015 2:33 PM
 

stuartd said:

Thank you for spelling "lose" correctly!

January 20, 2016 11:13 AM
 

Jason said:

I always specify the schema in creation scripts for that object. However, inside a proc for example, there are only two scenarios. 1. you want to enable the schema fall through (that is to say, if a jason.Customer table exists, then use that instead of dbo.Customer) which you might do in a shared dev environment. 2. Your objects are named so that they are distinct within that database so that the only difference between two different tables or views is NEVER just the schema. Anything else would be a poor architecture because it would be prone to mistakes.

I'm not saying you can't or shouldn't specify the schema if want to, but I disagree with the premise that it's a best practice that should be followed by all.

March 23, 2016 1:39 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement