Dropping temp table . Is there any difference using below sql server 2016 vs sql 2022.

Question

Dropping temp table . Is there any difference using below sql server 2016 vs sql 2022.

Jose 0

Temp table is not being dropped when using same query migrating from SQL 2016 to sql 2022 .

Drop table if exists #temp1

Erland Sommarskog 120.6K Reputation points MVP Moderator

2023-12-30T10:44:05.18+00:00

You would need to be more specific. Exactly how are you concluding that the temp table is being dropped?
Jose 0 Reputation points

2023-12-31T16:12:08.41+00:00

We are doing sql server migration from 2017 to 2022 . After migration we could note the query drop table if exists #temp1 not working in Sal server 2022 . The next query is stating that the table already exists
Erland Sommarskog 120.6K Reputation points MVP Moderator

2023-12-31T16:28:53.7766667+00:00
Again, you need to be more specific. Can you share a piece of code that is not working for you?

I can run the code below repeatedly on SQL 2022:

DROP TABLE IF EXISTS #temp1 CREATE TABLE #temp1(a int NOT NULL)
Dan Guzman 9,391 Reputation points

2023-12-31T17:37:10.3266667+00:00

Is the instance collation the same on both instances? You'll get these symptoms if the SQL 2022 instance collation is case-sensitive but the SQL 2017 instances is not and the object names differ by case (e.g. DROP TABLE IF EXISTS #temp1;CREATE TABLE #Temp1(a int NOT NULL);).

1 answer

Your answer

Erland Sommarskog 120.6K Reputation points MVP Moderator

2023-12-30T10:44:05.18+00:00

You would need to be more specific. Exactly how are you concluding that the temp table is being dropped?
Jose 0 Reputation points

2023-12-31T16:12:08.41+00:00

We are doing sql server migration from 2017 to 2022 . After migration we could note the query drop table if exists #temp1 not working in Sal server 2022 . The next query is stating that the table already exists
Erland Sommarskog 120.6K Reputation points MVP Moderator

2023-12-31T16:28:53.7766667+00:00

Again, you need to be more specific. Can you share a piece of code that is not working for you?

I can run the code below repeatedly on SQL 2022:

DROP TABLE IF EXISTS #temp1 CREATE TABLE #temp1(a int NOT NULL)
Dan Guzman 9,391 Reputation points

2023-12-31T17:37:10.3266667+00:00

Is the instance collation the same on both instances? You'll get these symptoms if the SQL 2022 instance collation is case-sensitive but the SQL 2017 instances is not and the object names differ by case (e.g. DROP TABLE IF EXISTS #temp1;CREATE TABLE #Temp1(a int NOT NULL);).

Answer 1

LiHongMSFT-4306 31,551

Hi @Jose

How about this code:

IF OBJECT_ID(N'tempdb..#Temp1') IS NOT NULL
BEGIN
DROP TABLE #Temp1 
END 
GO

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jose 0 Reputation points

2024-01-02T02:47:53.7333333+00:00

Yes this is only i did for now . But want to know why drop table if exists works in sql 2017 but not in 2022 .
Erland Sommarskog 120.6K Reputation points MVP Moderator

2024-01-02T22:08:58.72+00:00

DROP TABLE IF EXISTS works in SQL 2022. If you want to know why it does not work for you, you need show more exactly what you are doing. For instance a screenshot with your code and the error message. My guess is the same as Dan Guzman's: it's related to the server collation, rather than the version of SQL Server.

By the way, what does "SELECT @@version" report?

Share via

Dropping temp table . Is there any difference using below sql server 2016 vs sql 2022.

1 answer

Your answer