SQL tempdb and constraint names

A weird thing I found out about MS-SQL's tempdb a few days ago was that the server does not hash the constraint names.

I don't know if you know the way tempdb is working in MS-SQL but the basic concept is that when a process is creating a temp table the tempdb is hashing the name to create a unique instance for the running session.

so if you write a code that looks like this:
create table #TestTabe (id int, descript nvarchar(50))

in runtime you will see in the tempdb something that looks like this:
#TestTable________________123213E2B

the main reason is that you can have multiple sessions running at the same time.

Let's add a constraint to the equation:

CREATE TABLE #TestTable(
id int, descript nvarchar(50),
CONSTRAINT [PK_#TestTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)

in runtime you will see something like this:




The normal way tempdb is operating is when the session ends (i.e. stored procedure finished it's execution) or when the user manually drop the table it's deleted from the tempdb with all of it's constraints.

if your session fails for some reason and you didn't catch it the tempdb is not cleaned and the table (with the hashed name) and it's constraints are left in the tempdb.

This is not a big deal in general cause the table name is hashed for each session so the next session will have no problem creating it's own instance of the table.

The weird stuff I found out and kind'a puzzled me was that MSSQL does not hash the constraint name! so the next session that tried to create the table FAILD with the error of "Constraint already exists in the db"!

So until the next cleanup of the tempdb (restart of the sql server should do the job) the process is stuck and will fail every time!

The solution?

Wrap your code with try & catch so you can drop all the temp tables in the case of a failure! The code I saw (not my code of course :-) )was not wrapped and this created an issue.

That's it for today.

Till the next time, keep coding...

Joseph Gozlan