Name resolution, default schema, implicit schema Part II

Last section clarified some background, let us continue with Name Resolution Algorithm for Types and XML Schema Collections

[Algorithm]

As implied by general syntax for referencing entities, types or XML schema collections cannot be referenced across database. If schema name is unspecified, SQL server uses “sys first” algorithm as outlined below.

1. First, Look in the “sys” schema if not DDL access, then

2. Look in the active default schema, then

3. Look in “dbo” schema.

If schema name is specified, SQL server simply looks in the designated schema.

Note:

· Database collation is used to match schema, type and XML schema collection names.

· DDL access includes CREATE/DROP/ALTER on types or XML schema collections.

[Backward Compatibility]

During upgrade, system types are put in “sys” schema, user defined types are put in “dbo” schema. In SQL 2000, user defined types can only be created thru sp_addtype. This procedure is modified in SQL2005 to always put newly created types in “dbo” schema. Since types can only be referenced using single part name in SQL 2000, above algorithm clearly satisfies backward compatibility requirement.

Note that new type DDL can be used to create types in schemas other than “dbo” and “sys”. It is recommended to qualify with schema when referencing user defined types in applications, if active default schema cannot be dependent on.

XML schema collections are newly introduced in SQL2005, so there is no backward compatibility issue with it.

 

Next time will look at name resolution algorithm for objects.