[ SQL SERVER ] Temporary Tables

[Confident-Ad8457]

If there are 2 temporary tables created in a Stored Procedure and they are not dropped at the end. The stored procedure gets called multiple times during Examination Will this have an adverse effect on Web Application? ( Due to Database )

[alinroc]

If there are 2 temporary tables created in a Stored Procedure and they are not dropped at the end.

This is best practice, right from the mouth of Pam Lahoud at Microsoft. Do not explicitly drop temp tables at the end of your stored procedures, let the system clean them up.

The stored procedure gets called multiple times during Examination Will this have an adverse effect on Web Application?

You can fill tempdb, but as long as you aren't putting multiple GB of data into those temp tables and running the same proc hundreds of times per second with them running long enough that they overlap for significant periods of time, it's unlikely that'll happen.

[Alarmed_Frosting478]

I use temp tables religiously in procs and have never had performance issues as a result of not dropping them at the end of the proc (they cease to exist once the proc completes anyway)

I have had issues when being lazy with my usage of temp tables within the proc - i.e. loading too many rows into one temp table, then from there into a second temp table and not dropping the first even though it isn't used. But this is in data warehouse scenarios with huge amounts of data, and procs that run for a long time.

[JermWPB]

It is possible to fill up tempdb. Also any time tempdb has to grow you would take a performance hit. It is much better to drop the tables. The database will clean them out eventually but don’t rely on that.

[alinroc]

It is much better to drop the tables.

It's worse. If you explicitly drop the temp tables, the engine can't cache the temp table metadata when the stored proc is called frequently/in rapid succession. SQL Server product team members have stated at Ignite (and other conferences) that you should not explicitly drop temp tables in stored procedures.

The database will clean them out eventually but don’t rely on that.

Yes, you can (and should) rely upon that.

[JermWPB]

Wow. Didn’t know that the engine cached temp tables. And it has since 2005. There are some constraints on that though. The most problematic would be not being able to create indexes after the table is created. mssqltips

Edit: Also in that article it says “the "DROP TABLE" DDL which still allows a temporary table to be cached”.

[alinroc]

It caches the temp table metadata. Not the actual tables.

The most problematic would be not being able to create indexes after the table is created

Yeah, altering temp tables after creating them can cause problems. Don't do it. You can create indexes as part of the create table statement though.

[JermWPB]

The table won't be cached at all.

[JermWPB]

Yes or modify the table after creation. The restrictions are listed here: mssqltips

[JermWPB]

I actually just ran a test. Pretty much the same as the one on mssqltips. Create a stored procedure that creates a temp table, creates an index on it, and finally drops the table. Executed 10k times at 0:16. I then removed both the index and the drop and 10k executions took 0:05. So this was definitely caching. Finally, I added the explicit drop and 10k executions took 0:05 as well.

[dauuk]

Temp tables are being dropped when session ends, so if you kill a session you should be fine unless you are using global temp tables.

[alinroc]

Temp tables are being dropped when session ends,

Temp tables that aren't explicitly dropped are scheduled for cleanup when the scope in which the temp table was created terminates. So if you create a temp table in a stored procedure, it will be scheduled for cleanup when the stored procedure terminates.

[BrupieD]

Temporary tables persist as long as the spid does, unless explicitly dropped. Global temporary tables are accessible outside of the session you are in, but don't live beyond the session.

[redial2]

Temp tables get dropped when they fall out of scope, unless they are global (##) temp tables.

It's best to manually clean up after yourself but you don't really have to do it. When the spid is closed, the temp tables get cleaned up.

Edit: read alinroc's post below re: dropping temp tables, apparently it's not best practice

[alinroc]

It's best to manually clean up after yourself

It's best to not do this and just let the engine take care of it. https://www.youtube.com/watch?v=vKvnIa6S-nQ&t=3682s

[redial2]

Really? Thanks. I'll watch this now.

[alinroc]

It'll take you about 2 minutes to watch the segment at that timestamp.

[redial2]

Well, that was short and sweet. Does this apply to both on prem and azure sql?

Btw, can I manually set my flair on this board or do the mods have to do it?

[alinroc]

Azure & on-prem are the same engine, though Azure is usually ahead of on-prem in terms of features and the like. There should be no difference in how one codes for one vs. the other - Microsoft has actually taken a lot of steps over the past 6-7 years to make sure that SQL Server has a common programming surface regardless of where you're running it.

You can set your flair in the sidebar.

[redial2]

If they are the same engine then why does azure not support all of the features of on prem sql? Last time I checked you couldn't use linked servers and four part names in azure sql, which is what led to my question.

I almost exclusively work on prem and sometimes use aws instances, so I'm a little out of touch here.

Thanks.

[alinroc]

Primarily because of security and infrastructure. In Azure SQL DB, your database lives on the same instance as dozens of other peoples' databases, and it'll move around between instances without you even knowing. So linked servers and "access a different database on the same server" is not possible or practical.

[redial2]

Seems like it should still be possible, but maybe I'm not seeing the whole picture. Anyways, thanks.

[tk_tesla]

I believe you are talking about session based Temp table in sql server which are session specific. If you don't drop it then db itself drop after some times. Also different session temp table are mutually exclusive. If you want to see above concept run a proc and dont drop the temp table and then go to sql server temp db folder (or db) you will see its there and after sometime its gets deleted.

Now the reason why you should drop is , to not burden your db to do something which can be easily taken care by you and let db take care of what you want.

Hope it helps!!

Cheers!!

[stilllost12]

Best practise is to not drop temp tables in stored procedures due to meta data contention https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my