LearnNowOnline

Blog

Schemas and Naming in SQL Server

Excerpt by Don Kiely | September 03, 2013

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.

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