Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
The Training Kit for 70-461 states that 'Temporary tables are created in tempdb in the dbo schema.', however it doesn't go into any more info than that, so I'm not sure if that means only when you don't specify a schema. – Mark Sinkinson Jan 26 at 22:43
up vote 9 down vote accepted

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
        ,
);
share|improve this answer
    
interesting bit about the comma. And I'm loving that you're putting that comma at the start of the line, lol! – Max Vernon Jan 26 at 23:00
    
Probably in large part because temp tables are actually created in TempDB and a local schema wouldn't work (unless you were actually in TempDB of course) – Kenneth Fisher Jan 26 at 23:01
    
So there clearly is code that ignores the schema name when creating a temporary table; and that code is silent. – Max Vernon Jan 26 at 23:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.