Name resolution, default schema, implicit schema Part IV

This post will talk about implicit schema used in compile plan and conclude the topic of Name resolution, default schema, implicit. 

 

A compile plan may refer to non-qualified schema object. It needs to record that fact an “implicit” schema is used (Name resolution detect such scenario and default schema is recorded in the compile plan, which is used as part of the plan cache lookup key). Batches with unqualified object names result in non-reuse of query plans. For example, in "SELECT * FROM MyTable", MyTable may legitimately resolve to Alice.MyTable if Alice issues this query, and she owns a table with that name. Similarly, MyTable may resolve to Bob.MyTable. In such cases, SQL Server does not reuse query plans. If, however, Alice issues "SELECT * FROM dbo.MyTable", there is no ambiguity because the object is uniquely identified, and query plan reuse can happen. (See the uid column in sys.syscacheobjects. It indicates the user ID for the connection in which the plan was generated. Only query plans with the same user ID are candidates for reuse. When uid = -2, it means that the query does not depend on implicit name resolution, and can be shared among different user IDs.)

 

Refer to Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 for expanding on implicit schema impact on query plans. https://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx