debugsqlModerate
Does CREATE TABLE SomeSchema.#TempTableName have a bug?
Viewed 0 times
bugcreatehavesomeschematemptablenamedoestable
Problem
Simple test-bed:
The above should create a temporary table named
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?
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?
Solution
Both references are valid and will resolve correctly, but the #temp table is created under the
Same answer (on your system, some number I couldn't possibly guess):
Same answer (both 1, which is
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:
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
,
);Code Snippets
SELECT OBJECT_ID('dbo.#MyTempTable');
SELECT OBJECT_ID('SomeSchema.#MyTempTable');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');CREATE TABLE dbo.foo
(
bar INT
,
);Context
StackExchange Database Administrators Q#127413, answer score: 11
Revisions (0)
No revisions yet.