Is it possible to create TWO LOCAL TEMP TABLES with the same name?

10
1158

AIM:- Is it possible to create two local temp tables with same name?
create two temp tables with the same name

Description:- The answer for this question is YES, it is possible to create two local temp tables with the same name. You must be surprised that how it could be possible. Well, I am going to burst your balloon of curiosity very soon. Let me tell you, this is an interesting question which I have been asked by many friends on facebook. Finally, I thought of writing about this question and today I will share my experience with all of you. A beginner of SQL can tell you that we can’t create two tables with same name in SQL database which is absolutely correct. But here the question is different i.e. can we create two local temp tables with the same name, I stress on this word local temp tables.
Before we jump into the practical part by creating local temp tables, first we should validate our concept that we cannot create two tables with the same name in our database. Let’s proceed into this by creating permanent (normal) tables with the same name. Later we will see what will happen, what problems arise and how to resolve these issues. Let’s see this with a quick example.

–Validating our concept that 2 tables cannot be created with the same name.

Here I am going to create 2 normal Tables with Same NAME i.e. TEST1. Press execute button for creating the table TEST1 into PHPRING database and later read the table TEST1 with the most powerful query in SQL i.e. Select statement.

Create normal table with the name TEST1

In the above screen shot, we can clearly see that the table TEST1 is created successfully and we can also read the columns (ID and name) that the TEST1 table contains.

Now, we will create another table with same name i.e. TEST1 within the same database PHPRING and see what’s going to happen. Will the error occur or oracle is going to change its rule?

Error Message while creating normal table with same name TEST1

Great!!! As expected we can clearly observe the error message (saying already an object named”test1″ is existed in the database) in the above screenshot. It means we validate our concept that we cannot create two tables with the same name.

We will now aim towards our main question i.e. can we create two LOCAL TEMP TABLES with the same name?

Here, I am going to create a LOCAL TEMP TABLE with a name TEST1. Before going to start, we should know what a temp table is and what are the types of Temp table?

Temporary tables are basically two types –

1. Local temp tables (#).

2. Global temp tables (##).

A temporary table is same as a normal table, but the only difference between them is that a temp table are created in Temp database and these tables exists till your connection exists i.e. we cannot see these local temp tables in other connections. It will be automatically dropped when you close the connection.

For example – I have two connections named as conn1, conn2. In connection1, I am going to create one temp table named as TEST1, after that I will open connection2  and will call the temp table which exists in the database (tempdb) with same name (TEST1). But unfortunately we cannot access that temp table in connection2. Why? Because temp table is only permitted and limited to connection1.

You must be wondering how can we all know which temp table is Local and which table is Global. Let me give you one simple solution –

  • The table prefix with one hash symbol (#) is a LOCAL temp table, whereas
  • The table prefix with two hash symbols (##) is a Global temp table.

So in this post, I will go with local temp tables. Global temp tables are out of the scope in this article. We can learn about global temp tables into later articles of q Lab if anybody have question relating to to this. So, let’s hit the bull’s eye by moving on with practical demonstatration.

 –Create local temp table #TEST1.

create table #test1(id int,name varchar(10))

As we learned from above discussion that all Temp tables only exists in tempDB, let we see our table TEST1 is existing in tempDB or not?

Creating local temp table with name TEST1

In the above screen shot, we can clearly observe that the table #TEST1 exists in tempdb and the connection ID is 53.

Now, let’s create another temp table with same name TEST1 in another connection. After that we can see whether it allows us to create or it throws an error.

Creating temp table with the same name TEST1

In the above screen shot, we can clearly see another temp table with same name #TEST1 is created but the only difference is that the other temp table is created in another connection id i.e. 51.

Finally we created two local temp Tables with the same name i.e. TEST1. As we saw practically that we can create two local temp tables with the same name, we should look into theoretical portion i.e. why this happen and how SQL server allows us to create two temp tables with the same name TEST1.

Reason:- SQL server appends some random numbers at the end of local TEMP TABLE name. This is the only reason for SQL server to allow creating two temp tables with the same. SQL server will provide some random number to the local temp tables. In the above screen shot we can clearly see the extensions of two tables the first table was ended with extension 000000000017 and second table was ended with extension 00000000001c, we cannot mention these extensions at the creation of these two tables, but who provides those extensions to two temp tables. SQL server internally provides those extensions to these tables for identification.

Summary:-

  1. Tried to create two normal tables with the same names but got an error at the time of creating second table.
  2. Created TWO LOCAL TEMP tables with same NAME (TEST1) and saw how the SQL server allows us to create this time.

With this we come to the conclussion that we can create two local temp tables with the same name. Hope you enjoyed this article of q Lab section. Your queries and comments are always welcome.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 10.0/10 (18 votes cast)
VN:F [1.9.22_1171]
Rating: +22 (from 22 votes)
Is it possible to create TWO LOCAL TEMP TABLES with the same name?, 10.0 out of 10 based on 18 ratings
  • once again thank u so much for your support bro(chander sharma)

  • thanks for ur motivation bro. Once again thank u so much for your support bro(chander sharma)

  • Beena

    Thanks Avinash….it’s something new I’ve learnt today. I’m still a beginner to sql server.

    After reading your article, I started analysing, is a table name in tempdb is linked with each separate connection, if that’s the case, then user can create same table name for every connection……the unique way to differentiate between the same two table names in tempdb, is the connection id and table name. Is this correct?

    • first of all thank u so much for visiting my article .am very glad hope u found this one as usefull.

      coming to your doubt ,
      yup your are correct my friend .we can create local temp tables with same name by using different connections,but we cant create two normal tables even though we have different connection.please mind this point beena.

      thanks

  • Beena

    Thanks for clarifying on this.

  • venki

    how to map data sets and data definitions in report plz let me know

    • Hi Venki,

      Can you please elaborate your question?

      Thanks,
      Avi

  • shrikant

    I think title of the post is confusing to the reader.
    You need to specify “can we.. ………. In single question?

    Post is good. Great work.