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
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?