sql 表变量 临时表_何时使用SQL临时表与表变量

sql 表变量 临时表

It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.

将数据存储在SQL Server临时表中而不是操纵或使用永久表非常有益。 假设您要对表进行完全DDL或DML访问，但没有该表。 您可以使用现有的读取访问权限将数据提取到SQL Server临时表中并从中进行调整。 或者您没有权限在现有数据库中创建表，则可以创建一个可以操作SQL Server临时表。 最后，您可能需要只在当前会话中可见数据。

SQL Server supports a few types of SQL Server temp tables that can be very helpful.

SQL Server支持几种类型SQL Server临时表，这些表可能非常有用。

Before we proceed, if you want to follow along with any code samples, I suggest opening SQL Server Management Studio:

在继续之前，如果您想跟随任何代码示例，建议打开SQL Server Management Studio：

本地SQL临时表 (Local SQL temp tables)

Local SQL Server temp tables are created using the pound symbol or “hashtag” followed by the table name. For example: #Table_name. SQL temp tables are created in the tempdb database. A local SQL Server temp table is only visible to the current session. It cannot be seen or used by processes or queries outside of the session it is declared in.

本地SQL Server临时表是使用井号或“井号”后跟表名创建的。 例如：#Table_name。 SQL临时表在tempdb数据库中创建。 本地SQL Server临时表仅对当前会话可见。 在声明它的会话之外的进程或查询无法看到或使用它。

Here’s a quick example of taking a result set and putting it into a SQL Server temp table.

这是一个将结果集放入SQL Server临时表的快速示例。

 
/*Insert Databases names into SQL Temp Table*/
BEGIN TRY
DROP TABLE #DBRecovery
END TRY
BEGIN CATCH SELECT 1 END CATCH
 
SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]),
    DBName = [name],
    RecoveryModel = [recovery_model_desc]
INTO #DBRecovery
FROM sys.[databases]
WHERE [recovery_model_desc] NOT IN ('Simple')
 

One of the most often used scenarios for SQL Server temp tables is within a loop of some sort. For example, you want to process data for a SQL statement and it you need a place to store items for your loop to read through. It provides a quick and efficient means to do so. See the code sample above, your loop can now reference the SQL Server temp table and process the records that meet the criteria of your goal.

SQL Server临时表最常用的方案之一是某种形式的循环。 例如，您要处理SQL语句的数据，并且需要一个位置来存储项目以供循环读取。 它提供了一种快速而有效的方法。 请参阅上面的代码示例，您的循环现在可以引用SQL Server临时表并处理符合目标条件的记录。

Another reason to use SQL Server temp tables is you have some demanding processing to do in your sql statement. Let’s say that you create a join, and every time you need to pull records from that result set it has to process this join all over again. Why not just process this result set once and throw the records into a SQL temp table? Then you can have the rest of the sql statement refer to the SQL temp table name. Not only does this save on expensive query processing, but it may even make your code look a little cleaner.

使用SQL Server临时表的另一个原因是您在sql语句中需要执行一些苛刻的处理。 假设您创建了一个联接，并且每次需要从该结果集中提取记录时，它都必须重新处理该联接。 为什么不只处理一次该结果集并将记录扔到SQL临时表中呢？ 然后，您可以让其余的sql语句引用SQL临时表名称。 这不仅节省了昂贵的查询处理，甚至可能使您的代码看起来更简洁。

There is one point that I want to make however. If the session that we’re working in has subsequent nested sessions, the SQL Server temp tables will be visible in sessions lower in the hierarchy, but not above in the hierarchy. Please allow me to visualize this.

但是，我要指出一点。 如果我们正在处理的会话具有后续的嵌套会话，则SQL Server临时表将在层次结构中较低的会话中可见，而在层次结构中的较高会话中则不可见。 请允许我将其形象化。

In this quick diagram, a SQL temp table is created in Session 2. The sessions below it (sessions 3 and session 4) are able to see the SQL Server temp table. But Session 1, which is above session 2, will not be able to see the SQL Server temp table.

在此快速图中，在会话2中创建了一个SQL临时表。在它下面的会话（会话3和会话4）可以看到SQL Server临时表。 但是，在会话2之上的会话1将无法看到SQL Server临时表。

The SQL temp table is dropped or destroyed once the session disconnects. Many times you’ll see developers use the “DROP #Table_Name” command at the end of their statement just to clean up. But it is entirely up to you and what you’re trying to accomplish.

一旦会话断开连接，SQL临时表将被删除或销毁。 很多时候，您会看到开发人员在声明的末尾使用“ DROP #Table_Name”命令只是为了清理。 但这完全取决于您和您要完成的工作。

Also note, that in the event of name conflict (remember that SQL Server temp tables are created in the tempdb) SQL server will append a suffix to the end of the table name so that it is unique within the tempdb database. But this process is transparent to the developer/user. You can use the same name that you declared as it’s confined to that session.

还要注意，在名称冲突的情况下（请记住，SQL Server临时表是在tempdb中创建的），SQL Server将在表名的末尾附加一个后缀，以便它在tempdb数据库中是唯一的。 但是此过程对开发人员/用户是透明的。 您可以使用声明的名称，该名称仅限于该会话。

全局SQL临时表 (Global SQL temp tables)

Global SQL temp tables are useful when you want you want the result set visible to all other sessions. No need to setup permissions. Anyone can insert values, modify, or retrieve records from the table. Also note that anyone can DROP the table. Like Local SQL Server temp tables, they are dropped once the session disconnects and there are no longer any more references to the table. You can always use the “DROP” command to clean it up manually. Which is something that I would recommend.

当您希望结果集对所有其他会话可见时，全局SQL临时表很有用。 无需设置权限。 任何人都可以在表中插入值，修改或检索记录。 另请注意，任何人都可以删除表。 像本地SQL Server临时表一样，一旦会话断开连接就将其删除，并且不再有对该表的引用。 您始终可以使用“ DROP”命令手动对其进行清理。 我建议这样做。

To create a global SQL temp table, you simply use two pound symbols in front of the table name. Example: ##Global_Table_Name.

要创建全局SQL临时表，您只需在表名前面使用两个井号即可。 示例：## Global_Table_Name。

表变量 (Table Variables)

Table variables are created like any other variable, using the DECLARE statement. Many believe that table variables exist only in memory, but that is simply not true. They reside in the tempdb database much like local SQL Server temp tables. Also like local SQL temp tables, table variables are accessible only within the session that created them. However, unlike SQL temp tables the table variable is only accessible within the current batch. They are not visible outside of the batch, meaning the concept of session hierarchy can be somewhat ignored.

使用DECLARE语句，可以像创建任何其他变量一样创建表变量。 许多人认为表变量仅存在于内存中，但事实并非如此。 它们驻留在tempdb数据库中，就像本地SQL Server临时表一样。 与本地SQL临时表一样，表变量只能在创建它们的会话中访问。 但是，与SQL临时表不同，该表变量只能在当前批处理中访问。 它们在批处理之外不可见，这意味着可以忽略会话层次结构的概念。

As far as performance is concerned table variables are useful with small amounts of data (like only a few rows). Otherwise a SQL Server temp table is useful when sifting through large amounts of data. So for most scripts you will most likely see the use of a SQL Server temp table as opposed to a table variable. Not to say that one is more useful than the other, it’s just you have to choose the right tool for the job.

就性能而言，表变量对少量数据（仅几行）很有用。 否则，在筛选大量数据时，SQL Server临时表很有用。 因此，对于大多数脚本，您很可能会看到使用SQL Server临时表而不是表变量。 并不是说一个比另一个有用，只是您必须为工作选择正确的工具。

Here is a quick example of setting up and using a table variable.

这是设置和使用表变量的快速示例。

 
DECLARE @TotalProduct AS TABLE 
(ProductID INT NOT NULL PRIMARY KEY,
 Quantity INT NOT NULL)
 
 INSERT INTO @TotalProduct
         ( [ProductID], [Quantity] )
 SELECT
  A.[ProductID],
  [Quantity] = SUM(B.Quantity)
FROM dbo.Product AS A
INNER JOIN dbo.SalesDetails AS B ON A.ProducitID = B.ProductID
 

We’ve created a table variable that will hold information regarding total quantities of a certain product sold. This is a very simplified example, and we wouldn’t use it if it contained a lot of rows. But if we were only looking at a few products this could really well. Once the table variable is populated you can then join this as a table to yet another table and gather whatever information you need. So there is a lot of flexibility and allows the developer to be quite creative.

我们创建了一个表变量，该变量将保存有关特定产品销售总量的信息。 这是一个非常简化的示例，如果它包含很多行，我们将不使用它。 但是，如果我们只看几种产品，那可能真的很好。 一旦填充了表变量，您就可以将该表作为一个表连接到另一个表，并收集所需的任何信息。 因此，它具有很大的灵活性，并且使开发人员具有很大的创造力。

Also, on a final note, in terms of transactions on table variables. If a developer rolls back a transaction which includes changes to the table variables, the changes made to the table variables within this particular transaction will remain intact. That is to say, other parts of this transaction in question will be rolled back, but anything referencing the table variable will not, unless that portion of your script is in error.

另外，最后要注意的是，关于表变量的事务。 如果开发人员回滚包括对表变量所做的更改的事务，则在此特定事务内对表变量所做的更改将保持不变。 也就是说，该事务的其他部分将被回滚，但是引用表变量的任何内容都不会回滚，除非脚本的该部分出错。

翻译自: https://www.sqlshack.com/when-to-use-temporary-tables-vs-table-variables/

sql 表变量 临时表