Database Object Qualifiers


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.Assets..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).

image from book
Figure 3-1: Grouping objects using a schema in the Object Browser




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net