asked
Schema qualifying temporary objects?
In the book "Inside Microsoft SQL Server 2005 - Query Tuning & Optimisation" page 274 it says the following. > **Temporary tables with unqualified schema** A temporary table is always > dynamically resolved at execution time > so that it is possible for the same > batch or procedure to create temporary > tables in different schemas. This > capability is a more extreme case of > the dynamic name resolution mentioned > ... A best practice is to make sure > that the schema for temporary tables > is not left ambiguous. I have never explicitly schema qualified my temporary tables to date. Any ideas whether there is any demonstrable benefit to implementing this? From my testing with creating, inserting and selecting from tables called `#foo` and `##foo` (as opposed to `dbo.#foo` and `dbo.##foo`) the `user_id` attribute in `dm_exec_plan_attributes` has a value of `-2` indicating that this is not dependant on implicit name resolution and the plan can be shared among different users. (For local #temp tables the `optional_spid` attribute prevents reuse of the wrong plan).
I have never seen any tests done, and to be honest I rarely specify a schema for my temp tables. If the attribute says that there is no need for the resolution to take place I can only assume it would be quicker than a similar query where resolution was required but it would only be shown to be rights in tests. I would put this in the same category as the naming conventions for stored procs where you should not use sp_ as the name as this 'mis'directs the engine to system sprocs rather than the user one in the local database. ...
I think you would have to test using schemas other than dbo. For example have a stored procedure 'owned' by schema_1 creating temp tables. Grant permissions on the stored proc to schema_2, and execute it with a user whose default schema is schema_2. Keeping everything as dbo might not really show up the (if any) issue?
pure guess (I mean educated opinion!) - haven't got an instance to fire up at present....
I didn't mention in the question but I did try the following already. Creating a new login, adding that into tempdb, creating a new schema and making that the default schema for that login then executing use tempdb; create table #t(c int); select SCHEMA_NAME(schema_id) from sys.tables where object_id=object_id('#t'); drop table #t Even under those circumstances it still seemed to default to `dbo` schema.
