使用显式create table语句而非select into创建表 [英] Creating a table using explicit create table statement versus select into

问题描述

使用显式的create table语句和加载数据与选择之间是否存在性能差异。此示例仅显示2列，但问题是针对使用非常大的表。下面的示例还使用了临时表，尽管我也想知道使用常规表的效果。我认为它们无论表类型如何都一样。

Are there any performance differences between using an explicit create table statement and loading data versus selecting into. This example just shows 2 columns, but the question is geared towards using very large tables. The example below also uses temporary tables, though I'm wondering the effects upon using regular tables as well. I think they would be the same regardless of table type though.

临时表方案：

--- Explicitly creating temp table first and then loading.
create table #test1 (id int, name varchar(100))
insert into #test1 (id, name) select id, name from #bigTable

--- Creating temp table by selecting into.
select id,name into #test2 from #bigTable

或常规表中命名为＃test2：

or regular tables:

--- Explicitly creating table first and then loading.
create table test1 (id int, name varchar(100))
insert into test1 (id, name) select id, name from #bigTable

--- Creating table by selecting into.
select id,name into test2 from bigTable

进入test2的名字每个人对此有何想法？我认为，显式创建表和加载必须比select into具有更好的性能，因为select into必须评估语句中的表达式才能创建表。

What are everyone's thoughts on this? I think that explicitly creating the table and loading must have better performance than selecting into as select into must evaluate the expressions within the statement in order to create a table.

我们的组织通常通常会显式地创建临时表，将其作为标准做法，而我们想知道所有想法实际上是最佳做法。

Our organization usually creates temp tables explicitly as a standard practice, and we're wondering what everything thinks is actually the best practice.

http://msdn.microsoft.com/en-us/library/ms188029.aspx

推荐答案

创建表使您可以在插入数据之前更好地控制表的定义，例如 NOT NULL ，约束等。使用 SELECT INTO 无法完成的事情。

CREATE TABLE gives you a better control over your table's definition prior to inserting the data, like NOT NULL, constraints, etc. things that you cannot do using SELECT INTO.

SELECT INTO 是最少记录的操作，但是在某些情况下， INSERT..SELECT 也可以最少记录。

请参见数据载入性能指南，尤其是以下部分：总结最小记录条件。

SELECT INTO is a minimally logged operation, but INSERT..SELECT can also be minimally logged, at some conditions.
See The Data Loading Performance Guide, especially the section: Summarizing Minimal Logging Conditions.

简而言之，如果您不关心约束，等等（例如，您想快速创建表的副本）IMHO SELECT..INTO 的优点是代码较短。

否则，您应该使用其他方式，但仍然可以将其最少记录。

Briefly, if you do not care about the constraints, etc. (e.g. you want to quickly create a copy of a table) the advantage of SELECT..INTO IMHO is a shorter code .
Otherwise, you should use the other way, and you would still be able to have it minimally logged.

这篇关于使用显式create table语句而非select into创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！