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 ( x VARCHAR(32) ); -- this is dbo.bar!
INSERT bar( x ) SELECT 'dbo created this.';
GO
SELECT x 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 ( x VARCHAR(32) ); -- this is foo.bar!
INSERT bar( x ) SELECT 'foo created this.';
SELECT x 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.