April 2, 2024 at 3:30 am
Hi
I am getting this error message when I try to re-use a temp table. The code is a bit long so I won't post it here. Here is a template on how the temp tables are being used. What am I doing wrong?
Code:
DROP TABLE IF EXISTS #TEMP1
DROP TABLE IF EXISTS #TEMP2
DROP TABLE IF EXISTS #TEMP3
DROP TABLE IF EXISTS #TEMP4
DROP TABLE IF EXISTS #TEMP5
DROP TABLE IF EXISTS #TEMP6
DROP TABLE IF EXISTS #TEMP7
select...
into #TEMP2
from #TEMP3
DROP TABLE #TEMP2
DROP TABLE #TEMP3
DROP TABLE #TEMP4
DROP TABLE #TEMP5
DROP TABLE #TEMP6
DROP TABLE #TEMP7
select...
into #TEMP2
from #TEMP4
Error:
There is already an object named '#TEMP2' in the database.
Thank you
April 2, 2024 at 7:30 am
one thing I should mention/clarify...
each time I re-use #temp2 I am using a completely different select statement so the columns going into #temp2 will be different.
it is odd that I cannot simply drop the temp tables...
April 2, 2024 at 10:49 am
You need to add a GO before the second insert.
April 2, 2024 at 12:28 pm
April 2, 2024 at 1:14 pm
Don't reuse the same temp table for different sets of columns. It makes debugging confusing, and it subverts the way SQL Server manages temp tables.
One of the small optimizations SQL Server uses is that doesn't necessarily drop a temp table at the end of a stored procedure. It can reuse it rather than having to create the same table again. So, don't bother dropping temp tables within stored procedures -- SQL Server will handle that.
If you need to repopulate a temp table (e.g., looping through data) -- with the same columns --, just truncate it.
April 2, 2024 at 3:07 pm
Thank you everyone.
This is very interesting. Is this a bug within SS that you cannot drop a #temp table inside a SP using the DROP TABLE command? Or is there another way to drop them?
The only workaround I found was to give each #temp a new name but I was really trying to avoid that.
April 2, 2024 at 3:26 pm
Thank you everyone.
This is very interesting. Is this a bug within SS that you cannot drop a #temp table inside a SP using the DROP TABLE command? Or is there another way to drop them?
The only workaround I found was to give each #temp a new name but I was really trying to avoid that.
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.
April 2, 2024 at 9:22 pm
Thank you everyone.
This is very interesting. Is this a bug within SS that you cannot drop a #temp table inside a SP using the DROP TABLE command? Or is there another way to drop them?
The only workaround I found was to give each #temp a new name but I was really trying to avoid that.
He didn't say you can't drop the temp table - what he commented on was how SQL Server handles temp tables. If you want to specifically drop the temp tables at the end of your procedure or script you can do that with no issues.
The problem you are having is that you can't create a table (temp or not) - then drop that table - and *in the same batch* create the same table again.
This is also a code smell - almost certainly can be done without all the temp tables.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2024 at 8:17 am
As someone who used temp table and had a lot of issues with it, I highly recommend start creating table into DB and use it.
TempDB is good only if you keep a little bit of data which will be used later on, with less as possible columns and records. If you make it to big, and then use it, everything is going to be slower, because there no indexes, no optimizations nothing into this tempdb. Since I moved into creating table which has the same purpose as tempdb, I am absolutely reborn. Also you doing what are you trying here does not seems to be a problem using normal tables. Also creation of indexation even when this table probably wont be so big, will help improving the speed of the query.
April 17, 2024 at 4:10 pm
As someone who used temp table and had a lot of issues with it, I highly recommend start creating table into DB and use it.
TempDB is good only if you keep a little bit of data which will be used later on, with less as possible columns and records. If you make it to big, and then use it, everything is going to be slower, because there no indexes, no optimizations nothing into this tempdb. Since I moved into creating table which has the same purpose as tempdb, I am absolutely reborn. Also you doing what are you trying here does not seems to be a problem using normal tables. Also creation of indexation even when this table probably wont be so big, will help improving the speed of the query.
I appreciate the perspective you've shared regarding the use of temp tables, but I'd like to offer a different view on the robust capabilities and advantages of using tempdb in SQL Server.
First, it's important to note that the size limitation of temp tables is primarily governed by the size of the tempdb database itself. Tempdb is specially optimized for short-lived data, and its performance can actually be enhanced if it's hosted on faster storage systems, which is a common practice. This setup helps in achieving better performance compared to regular databases that might not reside on the fastest storage media available.
Moreover, the assertion that tempdb does not allow for indexing is not accurate. Temporary tables can indeed have indexes created on them just like permanent tables. Indexes can be strategically used to speed up queries involving temporary tables, effectively optimizing performance in complex operations.
Another key advantage of using tempdb is the isolation it provides from the production database environment. Operations on tempdb are generally more lightweight in terms of logging and do not affect the transaction log of the main database, thus minimizing the impact on overall database performance.
Tempdb also offers unique flexibility for managing volatile data. It supports different types of temporary objects such as local and global temporary tables, temporary stored procedures, and table variables. This variety allows developers to choose the most appropriate type of temporary storage according to their specific requirements, whether it’s for individual sessions or shared use across sessions.
The system’s ability to handle transactions involving temporary tables is also noteworthy. Tempdb can efficiently manage large volumes of data without the long-term overhead of data persistence, which is essential in environments where data needs to be manipulated and then discarded or aggregated without permanent storage.
In addition, the use of tempdb can simplify the management of data modifications and state within a session, supporting complex analytical operations within a contained and optimized environment.
Lastly, using temporary tables in tempdb can be crucial for ad-hoc data processing and quick data isolation, which are essential in development, testing, or even production scenarios where data needs to be manipulated securely and independently of the main datasets.
While it's true that there are scenarios where permanent tables might be beneficial, tempdb and temporary tables offer significant performance and management benefits that should not be overlooked. Each use case has its optimal solution, and understanding the capabilities and limitations of each approach is key to effective database management.
April 18, 2024 at 5:18 am
I appreciate the perspective you've shared regarding the use of temp tables, but I'd like to offer a different view on the robust capabilities and advantages of using tempdb in SQL Server.
...........
Thank you,
Well seems that in my case I have the mentioned situation were tempdb is simply not suitable for the job. We are using tempdb, but always for small batches of data, short term usage.. We have specific situation where from 200GB we went to 1600GB tempdb, and yet we were always ending up with an full tempdb and the query stopped. Now with a table, the query works without problem.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply