SQL Server中的临时表和表变量有什么区别? [英] What's the difference between a temp table and table variable in SQL Server?

问题描述

在SQL Server 2005中，我们可以使用以下两种方式之一创建临时表:

In SQL Server 2005, we can create temp tables one of two ways:

declare @tmp table (Col1 int, Col2 int);

或

create table #tmp (Col1 int, Col2 int);

这两者之间有什么区别?对于@tmp是否仍然使用tempdb，或者是否所有事情都在内存中，我已经读到了相互矛盾的意见.

What are the differences between these two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.

在哪些情况下，一种情况的表现优于另一种情况?

In which scenarios does one out-perform the other?

推荐答案

临时表(#tmp)和表变量(@tmp)之间有一些区别，尽管使用tempdb并不是其中之一，在下面的MSDN链接中.

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.

根据经验，对于中小型数据量和简单使用场景，应使用表变量. (这是一个过于宽泛的准则，当然还有很多例外情况-请参阅下文和后续文章.)

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

在它们之间进行选择时要考虑的一些要点:

Some points to consider when choosing between them:

• 临时表是真实表，因此您可以执行诸如CREATE INDEXes等操作.如果您有大量数据，通过索引访问这些数据将更快，那么临时表是个不错的选择.

• Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.

表变量可以通过使用PRIMARY KEY或UNIQUE约束来具有索引. (如果要使用非唯一索引，只需将主键列作为唯一约束中的最后一列.如果没有唯一列，则可以使用标识列.)SQL 2014也具有非唯一索引.

Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.

表变量不参与事务，并且SELECT与NOLOCK隐式关联.事务行为可能非常有帮助，例如，如果您想在过程中途回滚，那么在该事务过程中填充的表变量仍将被填充！

Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

临时表可能导致存储过程可能经常被重新编译.表变量不会.

Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

您可以使用SELECT INTO创建一个临时表，该表可以更快地编写(适合临时查询)，并且可以让您处理随时间变化的数据类型，因为您无需定义您的临时表结构是预先的.

You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

您可以将表变量从函数传递回去，从而使封装和重用逻辑变得更加容易(例如，使函数将字符串拆分为任意定界符的值表).

You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).

在用户定义的函数中使用表变量可以使这些函数得到更广泛的使用(有关详细信息，请参见CREATE FUNCTION文档).如果要编写函数，则除非临时需要，否则应在临时表上使用表变量.

Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

表变量和临时表都存储在tempdb中.但是表变量(自2005年起)默认为当前数据库的排序规则，而不是采用tempdb默认排序规则的temp表(

Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.

全局临时表(## tmp)是可用于所有会话和用户的另一种类型的临时表.

Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

一些进一步的阅读:

• 马丁史密斯的妙语在dba.stackexchange.com

• Martin Smith's great answer on dba.stackexchange.com

有关两者之间区别的MSDN常见问题解答: https://support.microsoft .com/en-gb/kb/305977

MSDN FAQ on difference between the two: https://support.microsoft.com/en-gb/kb/305977

MDSN博客文章: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

MDSN blog article: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

文章: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

临时表和临时变量的异常行为和性能影响:

Unexpected behaviors and performance implications of temp tables and temp variables: Paul White on SQLblog.com

这篇关于SQL Server中的临时表和表变量有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！