Specifying schema for temporary tables
问：
I'm used to seeing temporary tables created with just the hash/number symbol, like this:
CREATE TABLE #Test
(
[Id] INT
)
However, I've recently come across stored procedure code that specifies the schema name when creating temporary tables, for example:
CREATE TABLE [dbo].[#Test]
(
[Id] INT
)
Is there any reason why you would want to do this? If you're only specifying the user's default schema, does it make any difference? Does this refer to the [dbo] schema in the local database or the tempdb database?
答：
It won't make any difference if you are specifying the users default schema, but if the users default schema changes then it will try to keep the temporary table in the dbo schema.
Temp tables are created in tempdb so it means you'd need to maintain the schema in tempdb, and offers no benefit.
跟帖：
If you create a schema on tempdb, and map a user to tempdb with that default schema, running CREATE TABLE #test (v INT) will create the table as "dbo.#test". Trying to create a temp table under a schema other than dbo will ignore the schema you speciy and create it in the dbo schema regardless. So CREATE TABLE dummy.#test (v INT) will also create "dbo.#test". --Jim
从上面最后Jim的跟帖中我们可以得知，临时表的schema默认都是位于tempdb数据库下的dbo，如果我们尝试给临时表声明非dbo的schema，例如：CREATE TABLE dummy.#test (v INT)，SQL Server会忽略在临时表#test前声明的schema名dummy，创建的还是"dbo.#test"，因此给临时表主动声明schema是没有意义的，所有的临时表都会创建在dbo这个系统schema下。