The following shows the general syntax for referring to an object/type/XML-Schema-collection in SQL Server:
[server.] [database.] [schema.] entity
You can include spaces around the periods separating the parts of the reference to the entity, or omit database or schema part between the periods. If names of any part include special characters, you can use quoted identifier (BOL Identifier section).
In SQL 2000, types are owned by users and they are not schema scoped, i.e. their names are unique in the containing database. The only way to reference a given type is to use single part (entity) name.
In SQL2005, System Objects are shipped with SQL Server in resource database. (See resource database CSS Material) These include views, scalar- and table-functions, stored-procedures, and extended-stored-procedures. Except Information-Schema (ANSI) views that belong to system schema “INFORMATION_SCHEMA”, all system objects belong to a new, reserved system schema “sys”. (Note: If a user database has a schema called “sys” it cannot be upgraded.)
These system objects can be visible (or resolvable) in every database, and compiled and/or executed in a database as if it were created in that database. The following are feature-definitions of the different types of System Objects.
Most of these system objects are migrated from SQL2000, and they were contained in schema “dbo” or “system_function_schema” (unbind able and hidden, only contains system functions) of master database. Back then, SQL Server name resolution and execution context have following special behaviors to handle system objects:
When you run either of the following two batches:
USE my_database; EXECdbo.sp_my_procedure; go EXEC my_database.dbo.sp_my_procedure; go
SQL Server 2000 searches master.dbo before my_database.dbo, therefore finding the system procedure sp_my_procedure if it exists. This means that a fully-qualified 3-part (database, schema and object) name may not literally resolve to what it says it should, rather a system object from master database could be resolved instead and evaluated in the context of the specified database. In other words, user procedures (if resolved) are always compiled and executed in the database where they exist, but system procedures are compiled and executed in every database. Clearly, we are hijacking user’s namespace in this scenario, i.e. if a user created procedure, which is contained in schema “dbo”, has the same name as system procedure, the user will not be able to execute this procedure.
Important Note: In above example, SQL 2000 considers dbo.sp_my_procedure in master as system procedure only if it is marked as MS-shipped. If it is not marked as MS-shipped, then the user procedure in my_database will be resolved before it. However, if the user procedure in master was resolved indeed, then it would be compiled and executed in master database context, except SQL 65 system tables contained in it, which are compiled in my_database context. This behavior was for backward compatible. It is still preserved in SQL2005 for TSQL procedures (not CLR procedures) but will be removed in some future releases.
In SQL 2000, system functions were contained in hidden schema “system_function_schema”, which cannot be referenced. User defined functions must be qualified with schema. As a consequence, this rule makes system functions unambiguously invoked. In SQL2005, the “::” syntax is being deprecated, rather it is recommended to qualify with schema “sys”. Compatibility will be maintained only for the System Functions shipped in SQL2000 by keeping an internal table to map the old syntax to the replacement system function.
Some important Terminologies --
[User’s Default Schema] In SQL 2000, there is an implicit relationship between users and schemas. Users (actually all database principals) by default are owners of a schema that is of the same name as the user (database principal). This implicit relationship is explicitly persisted upon upgrade from SQL 2000. SQL2005 enables an association of a user with a default schema that is not of the same name as the user, or multiple users to the same default schema. If a user’s default schema is not specified while creating the user, dbo is the default schema.
Standalone Execute-As/SETUSER can be executed in current session or inside procedure/function. [The Current Login/User] The session login/user is either the logged-in login/user or the login/user associated with the remote linked/remote-server session. Without invoke any Execute-As context, either thru procedure/function or standalone, the current login/user is the session login/user.
On database context switch, the user registered in the new database that is mapped to current login is the new current user. If no user is registered for the login, guest user is used.
On entering an Execute-As context (except execute as caller), the specified login/user becomes the current login/user. On exiting an Execute-As context, the current login/user reverts to the previous current login/user, that is, the current login/user that triggered the Execute-As context switch.
[Active Default Schema] Active default schema is schema of the SQL module if entity referenced by a statement (except DDL, dynamic SQL, a.k.a. EXECUTE statements, or intrinsic such as object_id()) inside a procedure, function or view. Otherwise, active default schema is current user’s default schema.
Let us continue with name resolution algorithm next time.