The complete name of a database object consists of four identifiers, concatenated in the following manner:
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.
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:
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
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
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
- Home: Introduction
- Tip 1: T-SQL identifiers
- Tip2: Database object qualifiers
- Tip 3: Character string data types
- Tip 4: Date, time and number data types
- Tip 5: Special data types-Part 1
- Tip 6: Special data types-Part 2
- Tip 7: Local variables
- Tip 8: Global variables
- Tip 9: Table variables
- Tip 10: Flow control statements
- Tip 11: Blocks, looping and conditional statements
- Tip 12: Unconditional and scheduled execution
- Tip 13: Transact SQL cursors
- Tip 14: Cursor related statements
- Tip 15: Problems and justified use of cursors
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.