Get started Bring yourself up to speed with our introductory content.

Database object qualifiers

The name of a database object has four identifiers. These identifiers must comply with the rules for T-SQL identifiers. This is referred to as the object's fully qualified name. The following excerpt outlines the different schemas that can be created to logically group database objects.

The complete name of a database object consists of four identifiers, concatenated in the following manner:

[[[server.][database].][schema].]database_object

Each of these identifiers must comply with the rules described in the previous section. Server, database, and schema are often referred to as database object qualifiers. The complete name of the object is often referred to as the fully qualified name, or four-part name.

Schema is a part of the name that is used to logically group database objects. You can also think of them as a namespace. Two database objects can have the same name as long as they belong to different namespaces. SQL Server 2005 databases by default have a couple of namespaces: dbo (as default schema), INFORMATION_SCHEMA (defined by SQL-92 Standard for system views that provide metadata information), sys (for system tables and views), and guest.

In SQL Server 2005, a user can create database objects that belong to any schema. If a user does not specify the schema, SQL Server 2005 will make it part of the default dbo schema. It is possible to define another schema and assign it as a default schema to any user. You can find more details about schemas in BOL.

NOTE
Before SQL Server 2005, schemas were tied with users that created objects. If the object was created by the user who created the database (or any member of the db_owner fixed database role or sysadmin server role), SQL Server will record the owner as dbo. In other cases, the username of whoever created the object will be assigned as the object owner. Therefore, this segment of object name used to be called owner.

When you are referencing the object, if you do not specify the name of the schema, SQL Server will first try to find the object in the sys schema. If such an object does not exist, SQL Server will try to locate it in the schema named the same as the user. If it does not exist there either, SQL Server will attempt to find it in the dbo schema. For example, to resolve the stored procedure name ap_Eq_List specified by using dsunderic without a schema name, SQL Server 2005 will try to find it as:

  • sys.ap_Eq_List
  • dsunderic.ap_Eq_List
  • dbo.ap_Eq_List

TIP
To avoid this waste of time, always try to explicitly specify the schema.

Server and database are (naturally) the names of the server and the database in which the object is stored.
You do not have to use all the qualifiers all the time. You can omit the server name and/or the database name if the database object is located in the current database and/or on the current server. You can also omit using the schema name when referencing a database object in one of the default schemas. For example, when you are connected to the Asset5 database on the SQLBox server, instead of typing
SQLBox.Asset5.dbo.ap_Eq_List
you can use any of the following:
 

ap_Eq_List
dbo.ap_Eq_List
Asset5.dbo.ap_Eq_List
Asset5..ap_Eq_List
SQLBox.Asset5..ap_Eq_List
SQLBox...ap_Eq_List
SQLBox..dbo.ap_Eq_List

NOTE
You can also use consecutive periods to skip qualifiers.

But the real advantage of a schema is that you can use it to group database objects such as those in the Object Browser (see Figure 3-1).


Basic Transact-SQL Programming Constructs


The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.

This was last published in February 2007

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close