Name resolution, default schema, implicit schema Part I

  • Article

This series of blogs describe name resolution behavior of schema objects, types and XML schema collections, and how it interacts with default schema, compile plan implicit schema.

The first part will lay some background so that we are at the same page.

The following shows the general syntax for referring to an object/type/XML-Schema-collection in SQL Server:

                [server.] [database.] [schema.] entity

 where:
  • entity is the name of the object/type/XML Schema collection.
  • schema is the schema containing the entity. The schema qualifier lets you refer to an entity in a schema other than default or implicit schema. Only schema entities can be qualified with schema. Schema entities are schema scoped entities including types, XML schema collections, and objects such as tables, views, procedures, functions, DML triggers, constraints, rules, defaults, etc. Non-schema objects, such as DDL triggers, event notifications, cannot be qualified with schema because they are not schema scoped.
  • database is the database context of the entity. This identifier is only valid for schema scoped objects but not types or XML collections.
  • server applies only when you are referencing to an object on linked/remote server. This is the name of the linked server containing the object. The server qualifier lets you refer to an object in a server other than your local server instance. Not all SQL statements allow you to access objects on linked/remote servers. If server part is specified, the object is resolved by delegating to the specified linked/remote server.

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.

[System Objects]

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.

  • System-Stored-Procedure – a System Object that has a name beginning with “sp_” and is either a stored-procedure or an extended-stored-procedure.
  • System-Extended-Stored-Procedure – a System Object that has a name beginning with “xp_” and is either an extended-stored-procedure or a stored-procedure. Unless schema ‘sys’ is qualified with or implied, it is visible only in “master” database (and thus always executes only in master).
  • System View – a System Object that is a view. It can be a backward compatible view, a catalog view, a dynamic management view (DMV) or an Information-Schema view. Unless schema context is implied or it is a backward compatible view, explicit schema ‘sys’ or ‘INFORMATION_SCHEMA’ must be qualified with.
  • System Function – a System Object that is a function (scalar or table) that “exists” (or is visible) in every database. It is recommended that the name begins with “fn_”.

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:

1. Resolve “anydb.dbo.sp_” to “master.dbo.sp_”, but execute in “anydb” context.

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.

2. System table-valued functions must be invoked with special “::” prefix, system scalar-valued functions must be invoked without any schema.

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.

3. Information-Schema views must be referenced using schema “INFORMATION_SCHEMA’, and they exist in master database but are compiled in every database.

4. All other MS-shipped “system” objects in master database, including extended procedures, user defined functions, system tables, have to be invoked by specifying master database, and thus always invoked in master database context.

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.

[Execute-As Context] A procedure/function can be executed with the privileges of its owner or with the privileges of the current user (caller) , or with the privileges of arbitrary user, depending on the procedure/function definition.

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.