Today, let us quickly try to understand an error message as below.
“Database name ‘tempdb’ ignored, referencing object in tempdb.”.
Many of us would have got this message (I do prefer to call this as a warning message, not error message), however, we would not have noticed as it is not causing any fatal results. With this post, I would like to share my thoughts and like to have your views on this as well.
Let us first see when would you get this message?
use tempdb
GO
create Table #Temp_Table(Col1 int)
GO
Select * From tempdb..#Temp_Table
Now, interesting thing, when the query is changed a bit as below, the message is vanished.
Somehow, I was not convinced the above method as a solution, because that was actually against my understanding of 4 part naming convention (servername.databasename.schemaname.objectname). So, we further tried to test with few more combinations to understand how it works internally.
A simple query as below showed #Temp_Table is created with dbo schema.
As next step, tried to create a schema and a new object is created. If you look at the schema of the object, we can clearly see that it is associated with dbo schema not the new schema. That means, the objects created in tempdb database is always creating in dbo, not in any other schema. In fact, the schema part is actually ignoring for temp tables.
create schema testschema
GO
create Table testschema.#Temp_Table10(Col1 int)
Points to ponder
There is NO need of specifying schema for objects created in tempdb database.
Even we specify a schema name, SQL Server simply ignores the schema part.
If you enjoyed this blog post, please share it with your friends!
SQLZealots 