SQL 2008 中的 TEMPORARY TABLE 和 TABLE VARIABLE 有什么区别? [英] What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?

问题描述

有什么区别:

CREATE TABLE #temp ( [ID] INT)

INSERT INTO #temp
SELECT ...

和

DECLARE @temp TABLE ( [ID] INT)

INSERT @temp
SELECT ...

在 SQL Server 2008 中?

in SQL Server 2008?

推荐答案

临时表在大多数特性上和普通表一样，只是进入 TempDB 而不是当前的 Database，并且在有限的范围后消失，(取决于它们是否是基于会话或全局临时表.但是对临时表中数据的所有更改都会记录到事务日志中，并会产生所有的性能影响.哦，您还可以添加尽可能多的索引或视图、触发器或其他任何内容您想要一个与普通表完全一样的临时表.

Temporary tables are like ordinary tables in most characteristics, except they go into TempDB instead of the current Database, and they dissapear after limited scope, (depending on whether they are session based or global Temp Tables. But all changes to data in Temp tables is logged to the transaction log, with all the performance implications that that entails. otoh, you can also add as many indices or views, or triggers, or whatever else you want to a temp table exactly as you would to a ordinary table.

表变量是一种快捷的内存表(它们也使用临时数据库).不会记录对它们的更改(这会提高性能).但是你只能得到一个索引，(因为不能在初始声明语句之后创建索引，所以你可以在表变量上创建的唯一索引是可以包含在初始表变量声明中的索引......

Table variables are a kind of short-cut in-memory table (they also use temp DB). Changes to them are not logged (this improves performance). But you can only get one index on them, (because indices cannot be created after the initial declaration statement, the only index you can create on a table variable is the one that can be included in the initial table variable declaration...

   Declare @Tab Table (myKey integer Primary Key Not Null, data varchar(20)) 

由于这些特性，临时表更适合大型表(宽且具有多行)和/或在其生命周期中将经历不止一种访问模式的表，而当您需要非常多的访问模式时，表变量是最佳选择窄表(只有键的表，或只有一个数据列的键)，它将始终由该索引键访问...

Because of these characteristics, temp tables are better choice for large tables, (wide and with many rows), and/or that will undergo more than one access pattern during their lifetime, whilst table variables are best when you need a very narrow table (keys only table, or key with only one data column), which will always be accessed by that indexed key...

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