SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Recently, one of my regular blog readers emailed me with a question concerning the following error:

Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

This reader has been encountering the above-mentioned error, and he is curious to know the reason behind this. Here’s Rakesh’s email.

Hi Pinal,
I’m a  regular visitor to your blog and I thoroughly enjoy your articles and especially the way you solve your readers’ queries. I work as a junior SQL developer in Austin. Today, when I started to create a TSQL application, I detected an interesting scenario.

It is a common practice for many of us to use the following statements to create a new object.But when I used local temporary table, i.e.

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name='#temp')
DROP TABLE #temp
CREATE TABLE #temp(id INT )

An error was displayed as:  Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

On running the statement
SELECT *
FROM sys.tables

I see the object under name column in sys.tables as ,
#temp_______________________________________________________________________________________________________________0000000002E1

As far as I can discern, my SQL Server is writing the above entry instead of #temp. Therefore,  I’m getting the error as table already exists – There is already an object named ‘#temp’ in the database. Can you please explain me why this happens to local temporary table?

Thanks and Sincerely Yours,
Rakesh

Rakesh, your question is noteworthy. Let’s look at the solution first and then we will look into the behavior.

Fix/Workaround/Solution:

In case of Temporary tables, run following T-SQL. Please note do not run this for any physical tables. Additionally, please note to replace #temp with yourtemptable name.

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(id INT ) 

However, make sure not to run this for physical tables. Additionally, please note to replace #temp with yourtemptable name.
Local temp tables can be created using hash (#) sign prior to table name. They are visible only in current connection.. When connection is dropped its scope ends as well. It is possible to create and use local temp table with the same name simultaneously in two different connections. In order to allow this behavior SQL Server suffixes name of the local temp table with incremental hex digit, which is reset when SQL Services are restarted. It is because of this reason that when looking into sys table it has to compared using LIKE and ‘%’.

Let me create few temporary tables, and then we will observe how hex numbers are suffixed to local temporary tables.

BEGIN
IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(id INT )
SELECT name
FROM sys.tables
WHERE name LIKE '%#temp%'
END
GO 10

Rakesh and my other readers, I encourage all of you to send me interesting and challenging questions that intrigue you as well as observations that you would like to share. As always, I will try my best to answer all your questions. Also, do send me your valuable opinions regarding this article.

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)