SQL Server – Investigating CREATE TABLE SomeSchema.#TempTableName Bug

sql serversql-server-2008-r2sql-server-2012tempdb

Simple test-bed:

USE tempdb;
GO

/*
    This DROP TABLE should not be necessary, since the DROP SCHEMA
    should drop the table if it is contained within the schema, as
    I'd expect it to be.
*/
IF COALESCE(OBJECT_ID('tempdb..#MyTempTable'), 0) <> 0 
    DROP TABLE #MyTempTable;

IF EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'SomeSchema') 
    DROP SCHEMA SomeSchema;
GO

CREATE SCHEMA SomeSchema AUTHORIZATION [dbo]
CREATE TABLE SomeSchema.#MyTempTable /* specifying the schema
                                        should not be necesssary since
                                        this statement is executed inside
                                        the context of the CREATE SCHEMA
                                        statement
                                     */
(
    TempTableID INT NOT NULL IDENTITY(1,1)
    , SomeData VARCHAR(50) NOT NULL
);
GO

INSERT INTO tempdb.SomeSchema.#MyTempTable (SomeData) VALUES ('This is a test');

SELECT *
FROM tempdb.SomeSchema.#MyTempTable;
GO

SELECT *
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'SomeSchema';

SELECT s.name
    , o.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name LIKE '%MyTempTable%';

DROP SCHEMA SomeSchema;
DROP TABLE #MyTempTable;

The above should create a temporary table named #MyTempTable in the tempdb under the schema named SomeSchema; however it does not. Instead the table is created in the dbo schema.

Is this expected behavior? I realize this is certainly an edge-case around the use of schema-specific temp tables; however it would be nice if the engine either provided an error when attempting to create a schema-bound temporary table, or actually did bind it to the schema specified in the DDL.

Also, I do not presently have access to SQL Server 2014 or 2016; does it work as expected on those platforms?

Best Answer

Both references are valid and will resolve correctly, but the #temp table is created under the dbo schema.

Same answer (on your system, some number I couldn't possibly guess):

SELECT OBJECT_ID('dbo.#MyTempTable');
SELECT OBJECT_ID('SomeSchema.#MyTempTable');

Same answer (both 1, which is dbo):

SELECT schema_id FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.#MyTempTable');
SELECT schema_id FROM sys.tables WHERE [object_id] = OBJECT_ID('SomeSchema.#MyTempTable');

Being able to specify a schema doesn't buy you anything because you're not going to have collisions (two same-named #temp tables under different schemas) within a session, right?

This is expected behavior. A #temp table is tied to a session, but not to a specific schema. And it works the same all the way up to 2016 CTP 3.2. The parser is probably forgiving, allowing the meaningless schema name in much the same way it allows this errant trailing comma:

CREATE TABLE dbo.foo 
(
        bar INT
        ,
);
Related Question