Specifying a Schema for Temporary Tables

Question

I'm used to seeing temporary tables created with the hash / number character, for example:

CREATE TABLE #Test ( [Id] INT ) 

However, I recently met a stored procedure code that indicates the name of the schema when creating temporary tables, for example:

 CREATE TABLE [dbo].[#Test] ( [Id] INT ) 

Is there a reason you would like to do this? If you specified only the default user schema, doesn't it matter? Does this apply to the [dbo] in the local database or tempdb database?

Answer 1

This will not make any difference if you specify a default schema for users, but if the default schema for users changes, it will try to save the temporary table in the dbo schema.

Temp tables are created in tempdb, so this means that you need to maintain the schema in tempdb and do no good.