CODEWITHSUNDEEP
sql-servertemp-tables
Andios
Andios

Reputation: 1

Temporary table was created in SQL Server but the table name was appended with extra characters

I tried to create a temporary table in SQL Server Management Studio v17.9. 

CREATE TABLE #test1 
(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    MyDate DATETIME2(0) NOT NULL,
    MyValue VARCHAR(25) NOT NULL
);

The table was created and was put in the "Temporary Tables folder under System Database/tempdb/Temporary Tables. and the table name is

dbo.#test1__________________________________________________________0000000000A5

I have no idea how many underscore were there. Why was the name appended such long characters?

As a a result I couldn't use it.

Upvotes: 0

Views: 2015

Answers (1)

Gauravsa
Gauravsa

Reputation: 6524

As other users have already stated in comments, sql server appends id and underscore to a temp table. These ids in the end is unique for a particular connection.

For fun and knowledge, you may try creating a stored procedure that creates/alters/drop a temp table. Then run the stored procedure twice exec1, exec2 on the same procedure. You will see different temp tables with same name with underscore and different id’s.

Upvotes: 2

Related Questions