Schemas and Naming in SQL Server

Excerpt by Don Kiely

Whenever you execute a data selection query in SQL Server, you'll be accessing one or more database objects, so it is important that you understand how things are named. In SQL Server, database object names use a convention that can contain four parts, any of which can be blank, except the object name:

[ server_name. [database_name]. [schema_name]. | database_name.[schema_name]. | schema_name. ] object_name
  • The server_name specifies linked server name or remote server name. A blank implies the current server.
  • The database_name specifies the database name. A blank implies the current database context.
  • The schema_name specifies the name of the schema that contains the object. A blank implies the default schema for the current user or the dbo schema if no other default schema is assigned to the current user.
  • The object_name specifies the name of the object.

In most situations, it is not necessary to use all four parts. However, the recommendation is to use the schema name with the object name, as shown in the following two examples. The first example for the Northwind database will work with or without dbo, because the server uses dbo when no schema is explicitly defined and no default schema is explicitly assigned to the current user.

The second query for the AdventureWorks2012 database will fail if the schema name Sales is omitted, unless the user has Sales set as her default schema. In this case, the Store table was created in the Sales schema. This query will work only for a user with Sales as the default schema.

-- Use Northwind; SELECT CompanyName FROM dbo.Customers; -- "SELECT CompanyName FROM Customers;" will also work. -- USE AdventureWorks2012; SELECT Name FROM Sales.Store; -- "SELECT Name FROM Store;" will fail.

It might be tempting to deal with schemas by keeping all database objects assigned to dbo and avoid creating or assigning any other schemas. However, schemas can be a useful way of creating multiple namespaces in a database, just as namespaces make it easier for .NET programmers to keep track of classes. The AdventureWorks2012 database provides a good example of using schemas as namespaces. You can also assign permissions on a schema that grant the permission to all objects within it, which makes schemas a powerful security tool for protecting data access. 

NOTE In versions before SQL Server 2005, a schema was created automatically for each database user. When a user created an object, the object was automatically created in that user's schema-unless the user was a database owner, in which case the object was created in the dbo schema. In more recent versions of SQL Server, each user does not automatically have a schema. Schemas are created independently of users; users must explicitly be assigned rights to a schema and can be assigned a default schema. Many users can have rights to use any schema and many users can have the same schema as their default.

  ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Select, Where and Clauses course written by expert Don Kiely.

Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted September 03, 2013 from the online courseware SQL Server 2012, Part 8 of 9: T-SQL Select, Where, and Clauses by Don Kiely

SQL Server