T-SQL 101: 14 Using two part names for SQL Server tables and other objects

If you look carefully at the following simple query:

you’ll notice that I didn’t just say FROM Cinemas, I said FROM dbo.Cinemas. The “dbo.” part is the name of the schema. I talked about schemas in an earlier post. And all through these T-SQL 101 blog posts, I keep mentioning that you should always use the schema name as well as the object name.

By why?

[caption id=“attachment_2806” align=“alignnone” width=“312”] Image by Ken Treloar[/caption]

The first reason is that whenever you write T-SQL scripts of any type, you need to be precise. When you say just Cinemas, SQL Server has to work out which Cinemas table you mean.

Now you might think that you only have one. But SQL Server doesn’t know that when it starts to run your query, and it has to go to look to see if there are others.

Every user has a Default Schema associated with them. When you just say Cinemas, SQL Server first looks to see if there is a Cinemas table (or view) in your default schema. If it doesn’t find one, it then looks in the dbo schema. That’s why it seems to find it just fine when you simply say Cinemas.

Small as it is, why have it doing this extra work?

A worse situation is if you’re writing a query to create the table. If you just say Cinemas, the table would be created in the default schema of the person running the script, not necessarily in dbo. That’s not reliable query writing.

Stored Procedures and other objects too

Exactly the same issues relate to stored procedures. If you say:

EXEC SomeStoredProcedure;

How does SQL Server know if that’s dbo.SomeStoredProcedure or Sales.SomeStoredProcedure ? Sales might be your default schema.

And the same issues arise when you create stored procedures. You want to write scripts that reliably create them where you want them.

But wait, there’s more

A further complication is around query plan caching. If SQL Server caches a query plan for accessing the dbo.Cinemas table (once it’s found it), and you run a query that’s for just the Cinemas table, it doesn’t already know if it can use the same query plan, until it works out which object you’re talking about.

In many places in the T-SQL documentation, you’ll find you’re advised to use two-part object names. They’re not joking. For these and other reasons, please just get used to doing this.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-04-22