Originally published in 2009, I updated this in September 2019 with an example showing an effect on the plan cache.

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 object called your_default_schema.foo and there is also an object in the database called dbo.foo. SQL Server will pick the one under your default schema. This might be the wrong choice, but how can it know what you intended? 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];
SET NOCOUNT ON;
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 WITHOUT LOGIN;
GO
 
ALTER ROLE db_owner ADD MEMBER foo;
GO
 
EXECUTE AS USER = N'foo';
GO
 
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] = s.name + '.' + name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.[name] = 'bar';

Results:

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

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

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

Impact on Execution Plans

Another scenario to be aware of is when you have users or applications with different default schemas executing the exact same query – they can't share that query plan because one of the plan attributes forces the plan to be cached differently. Let's create a table, a separate schema, and a user with that schema as their default:

CREATE TABLE dbo.SomeTable(i int);
GO
CREATE SCHEMA SecurityGroup;
GO
CREATE USER Guard WITHOUT LOGIN WITH DEFAULT_SCHEMA = SecurityGroup;
GO
GRANT SELECT ON dbo.SomeTable TO Guard;
GO

Now, we're going to run an ad hoc query against dbo.SomeTable, without mentioning the schema, first as ourselves (presumably dbo or sa) and then as Guard:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;
GO
SELECT i FROM SomeTable;
GO
 
EXECUTE AS USER = N'Guard';
GO
SELECT i FROM SomeTable;
GO
REVERT;

Both queries get the same results – an expected empty resultset from dbo.SomeTable. The interesting part is in the plan cache:

SELECT 
  t.[text], 
  p.size_in_bytes, 
  p.usecounts --, [schema_id] = pa.value, [schema] = s.name
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
LEFT OUTER JOIN sys.schemas AS s
  ON s.[schema_id] = CONVERT(INT, pa.[value])
WHERE 
  t.[text] LIKE N'%SELECT%SomeTable%'
  AND t.[text] NOT LIKE N'%dm_exec%'
  AND pa.attribute = N'user_id';

Results:

text                        size_in_bytes    usecounts
------------------------    -------------    ---------
SELECT i FROM SomeTable;    16384            1
SELECT i FROM SomeTable;    32768            1

If you uncomment the additional columns, you'll see that the plan attribute that varies is the (default) schema name.

Now, let's run the same batch above, but now let's add the schema prefix:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;
GO
SELECT i FROM dbo.SomeTable;
GO
 
EXECUTE AS USER = N'Guard';
GO
SELECT i FROM dbo.SomeTable;
GO
REVERT;

Then we can check the plan cache again, and this time the results are different:

text                        size_in_bytes    usecounts
------------------------    -------------    ---------
SELECT i FROM SomeTable;    16384            2

With the schema prefix, we reuse the same plan and the schema attribute is NULL.

There are exceptions, of course, like when you actually *want* a single query to adapt to the default schema of the user. But I suspect these cases are few and far between (and might be worthy of a design review), unless you have a use case like Stack Overflow.

Summary

Using schemas is a complex topic, and I don't want to get into all of the security ramifications. 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, even if you are currently only using objects in dbo. You might thank me later.