临时表范围? [英] Temporary Table Scope?

问题描述

我在我的存储过程中使用临时表 #tempTable - 我用它来运行我的 ASP.net 报告(报告服务)

I am making use of temporary tables #tempTable in my stored procedure - that I make use to run my ASP.net Reports (Reporting services)

我正在做类似的事情

例如.代码

SELECT * INTO #tempTable FROM Contacts WHERE ContactID < 10

然后我使用类似的东西

SELECT o.* FROM #tempTable t INNER JOIN Orders o ON t.ContactID =o.ContactID

将值返回到我的报告中，即存储过程的结果

to return values to my reports aka results for the stored procedure

我没有摆脱我的#tempTable

I do not get rid of my #tempTable

即我不要

DROP TABLE #tempTable

我已经读到临时表的范围仅适用于存储过程-因此必须执行上述操作-如果我不执行上述操作，将来会遇到什么问题

I have read that the scope of temporary table is only for the stored procedure - so is doing the above necessary - if I dont do the above what problems will I get into in the future

推荐答案

首先，一旦过程完成，在过程中创建的本地临时表将被删除.来自BOL on Create Table:

First, local temporary tables created within a procedure are dropped once the procedure finishes. From the BOL on Create Table:

在存储过程中创建的本地临时表会在存储过程完成时自动删除.该表可由创建该表的存储过程执行的任何嵌套存储过程引用.调用创建该表的存储过程的进程不能引用该表.

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

如果您的数据访问代码正确打开连接、调用存储过程然后关闭连接，则在该过程中创建的临时表被有效销毁.

If your data access code is properly opening a connection, calling a stored procedure and then closing the connection, the temp table is created in the procedure is effectively destroyed.

我说有效"是为了提出另一点.我不建议在你的程序结束时删除临时表，尽管我会在创建临时表之前添加一个检查，如果存在则删除它(例如 if object_id('tempdb..#Foo') is not空).反对在最后删除临时表的论点是，通过调用 Drop 语句，您将迫使 SQL Server 在等待过程结束的同时消耗资源来销毁该表.相反，如果您让它超出范围，您的过程会立即结束，并且您让 SQL Server 在它自己选择的时间销毁该表.

I say "effectively" to bring up another point. I would not recommend dropping the temp table at the end of your procedure although I would add a check just before I created the temp table and drop it if exists (e.g. if object_id('tempdb..#Foo') is not null). The argument against dropping the temp table at the end is that by calling the Drop statement, you are forcing SQL Server to expend resources to destroy the table then and there while you wait for your procedure to end. If instead, you let it go out of scope, your procedure ends immediately and you let SQL Server destroy the table at a time of its own choosing.

这篇关于临时表范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！