在 sql server 中创建、使用和删除临时表的首选方法是什么?

本文介绍了在 sql server 中创建、使用和删除临时表的首选方法是什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

限时送ChatGPT账号..

在 SQL Server 存储过程中使用临时表时，是首选做法；

When using temp tables in SQL Server stored procs, is the preferred practice to;

1) 创建临时表，填充它，使用它然后删除它

1) Create the temp table, populate it, use it then drop it

CREATE TABLE #MyTable ( ... )

-- Do stuff

DROP TABLE #MyTable

2) 检查它是否存在，如果存在则删除它，然后创建并使用它

2) Check if it exists, drop it if it does, then create and use it

IF object_id('tempdb..#MyTable') IS NOT NULL
DROP TABLE #MyTable

CREATE TABLE #MyTable ( ... )

3) 创建它，当它超出范围时让 SQL Server 清理它

3) Create it and let SQL Server clean it up when it goes out of scope

CREATE TABLE #MyTable ( ... )

-- Do Stuff

我阅读了这个答案及其相关评论，这在重复使用临时表的情况下很有用SQL Server 将截断表但保留结构以节省时间.

I read in this answer and its associated comments, that this can be useful in situations where the temp table is reused that SQL Server will truncate the table but keep the structure to save time.

我的存储过程可能会经常被调用，但它只包含几列，所以我不知道这在我的情况下到底有多大优势.

My stored proc is likely to be called pretty frequently, but it only contains a few columns, so I don't know how advantageous this really is in my situation.

推荐答案

您可以在您的场景中测试并查看一种方法是否优于另一种.我听说过这种重用好处，但我自己没有进行任何广泛的测试.(我的直觉是明确删除我创建的任何 #temp 对象.)

You could test and see if one method outperforms another in your scenario. I've heard about this reuse benefit but I haven't performed any extensive tests myself. (My gut instinct is to explicitly drop any #temp objects I've created.)

在单个存储过程中，您永远不必检查表是否存在 - 除非该过程也可能从另一个可能创建了同名表的过程中调用.这就是为什么有意义地命名#temp 表而不是使用#t、#x、#y 等的好习惯.

In a single stored procedure you should never have to check if the table exists - unless it is also possible that the procedure is being called from another procedure that might have created a table with the same name. This is why it is good practice to name #temp tables meaningfully instead of using #t, #x, #y etc.

这篇关于在 sql server 中创建、使用和删除临时表的首选方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！