SELECT INTO 行为和 IDENTITY 属性

Question

我在做一个项目时，发现了使用SELECT INTO语句时的一些有趣行为。如果我有一个表格，并且其中一个列被定义为int identity(1,1) not null，并使用SELECT INTO进行复制，则新表将保留该IDENTITY属性，除非涉及到JOIN操作。如果存在JOIN操作，则新表中的同一列仅被定义为int not null。

以下是一个脚本，您可以运行它以重现这种行为：

CREATE TABLE People (Id INT IDENTITY(1,1) not null, Name VARCHAR(10))
CREATE TABLE ReverseNames (Name varchar(10), ReverseName varchar(10))

INSERT INTO People (Name)
VALUES ('John'), ('Jamie'), ('Joe'), ('Jenna')

INSERT INTO ReverseNames (Name, ReverseName)
VALUES ('John','nhoJ'), ('Jamie','eimaJ'), ('Joe','eoJ'), ('Jenna','anneJ')

--------

SELECT Id, Name
INTO People_ExactCopy
FROM People

SELECT Id, ReverseName as Name
INTO People_WithJoin
FROM People
    JOIN ReverseNames
        ON People.Name = ReverseNames.Name

SELECT Id, (SELECT ReverseName FROM ReverseNames WHERE Name = People.Name) as Name
INTO People_WithSubSelect
FROM People

--------

SELECT OBJECT_NAME(c.object_id) as [Table],
    c.is_identity as [Id Column Retained Identity]
FROM sys.columns c
where 
OBJECT_NAME(c.object_id) IN ('People_ExactCopy','People_WithJoin','People_WithSubSelect')
    AND c.name = 'Id'

--------

DROP TABLE People
DROP TABLE People_ExactCopy
DROP TABLE People_WithJoin
DROP TABLE People_WithSubSelect
DROP TABLE ReverseNames

我注意到WithJoin和WithSubSelect查询的执行计划都包含了一个联接操作符。如果我们处理更大的行集，不确定哪个性能更好。

有人能否解释一下，并告诉我是否有办法在联接中利用SELECT INTO并仍然保留IDENTITY属性？

- Justin Swartsel

Answer 1

来自Microsoft：

当一个存在的标识列被选入到新表中时，新列会继承其IDENTITY属性，除非满足以下条件之一：

The SELECT statement contains a join, GROUP BY clause, or aggregate function.

Multiple SELECT statements are joined by using UNION.

The identity column is listed more than one time in the select list.

The identity column is part of an expression.

The identity column is from a remote data source.

如果以下条件中有任何一个为真，则该列将被创建为NOT NULL而不是继承IDENTITY属性。如果在新表中需要标识列但没有这样的列，或者您想要与源标识列不同的种子或递增值，请使用IDENTITY函数在SELECT列表中定义该列。

您可以按照他们建议的使用IDENTITY函数并省略IDENTITY列，但那么您将失去该列的值，因为IDENTITY函数将生成新值，即使使用ORDER BY也不容易确定这些值。

- Tom H

---

我认为这些很难确定，因为ID字段可能存在间隙，所以我会说这是不可能的。 - Conrad Frix

在单个SELECT INTO语句中，我认为您不会遇到这个问题。IDENTITY列中的间隙通常是由于在多个插入之间发生回滚所致。问题在于ID可以在查询计划的任何位置生成，这意味着不一定按任何给定顺序生成。 - Tom H

SELECT INTO 语句相当于使用 Justin 的示例中的 SET IDENTITY_INSERT ON。如果在 SELECT INTO 之前执行 DELETE FROM People where Name = 'Jamie'，则 Joe 和 Jamie 保留了 ID 为 3 和 4。使用 INDENTITY 函数将无法保留这些 ID。您每 5 秒钟才能编辑一次评论。（单击此框以关闭） - Conrad Frix

正确。这就是我试图表达的意思。如果你认为我需要重新措辞，那么我很愿意听取建议。 - Tom H

Answer 2

我认为除了手动构建CREATE TABLE语句，SET IDENTITY_INSERT ON，插入现有值，然后SET IDENTITY_INSERT OFF之外，你没有太多可以做的。是的，你会失去SELECT INTO的好处，但除非你的表很大并且你经常这样做，否则[耸肩]。当然，这并不好玩，也不像SELECT INTO那么漂亮或简单，但你可以在某种程度上以编程方式实现它，假设有两个表，一个具有简单的identity（1,1），和一个简单的INNER JOIN：

    SET NOCOUNT ON;

DECLARE
    @NewTable SYSNAME = N'dbo.People_ExactCopy',
    @JoinCondition NVARCHAR(255) = N' ON p.Name = r.Name';

DECLARE
    @cols TABLE(t SYSNAME, c SYSNAME, p CHAR(1));

INSERT @cols SELECT N'dbo.People', N'Id', 'p'
    UNION ALL SELECT N'dbo.ReverseNames', N'Name', 'r';

DECLARE @sql NVARCHAR(MAX) = N'CREATE TABLE ' + @NewTable + '
(
';

SELECT @sql += c.name + ' ' + t.name 
    + CASE WHEN t.name LIKE '%char' THEN 
        '(' + CASE WHEN c.max_length = -1 
            THEN 'MAX' ELSE RTRIM(c.max_length/
            (CASE WHEN t.name LIKE 'n%' THEN 2 ELSE 1 END)) END 
        + ')' ELSE '' END
    + CASE c.is_identity
    WHEN 1 THEN ' IDENTITY(1,1)' 
    ELSE ' ' END + ',
    '
    FROM sys.columns AS c 
    INNER JOIN @cols AS cols
    ON c.object_id = OBJECT_ID(cols.t)
    INNER JOIN sys.types AS t
    ON c.system_type_id = t.system_type_id
    AND c.name = cols.c;

SET @sql = LEFT(@sql, LEN(@sql)-1) + '
);

SET IDENTITY_INSERT ' + @NewTable + ' ON;

INSERT ' + @NewTable + '(';

SELECT @sql += c + ',' FROM @cols;

SET @sql = LEFT(@sql, LEN(@sql)-1) + ')
    SELECT ';

SELECT @sql += p + '.' + c + ',' FROM @cols;

SET @sql = LEFT(@sql, LEN(@sql)-1) + '
    FROM ';

SELECT @sql += t + ' AS ' + p + ' 
    INNER JOIN ' FROM (SELECT DISTINCT
        t,p FROM @cols) AS x;

SET @sql = LEFT(@sql, LEN(@sql)-10) 
    + @JoinCondition + ';

SET IDENTITY_INSERT ' + @NewTable + ' OFF;';

PRINT @sql;

根据上述表格，下面是可以传递给 EXEC sp_executeSQL 而非 PRINT 的内容：

CREATE TABLE dbo.People_ExactCopy
(
    Id int IDENTITY(1,1),
    Name varchar(10) 
);

SET IDENTITY_INSERT dbo.People_ExactCopy ON;

INSERT dbo.People_ExactCopy(Id,Name)
    SELECT p.Id,r.Name
    FROM dbo.People AS p 
    INNER JOIN dbo.ReverseNames AS r 
     ON p.Name = r.Name;

SET IDENTITY_INSERT dbo.People_ExactCopy OFF;

我没有处理其他复杂性，比如DECIMAL列或其他具有max_length等参数的列，也没有处理可空性，但如果您需要更大的灵活性，这些事情并不难添加。

在下一个版本的SQL Server（代号“Denali”）中，您应该能够使用新的元数据发现函数更轻松地构建CREATE TABLE语句-它们可以在指定精度/比例/长度、处理MAX等方面为您完成大部分繁重工作。您仍然必须手动创建索引和约束；但是您也无法通过SELECT INTO获得这些内容。

我们真正需要的是允许您说出类似“CREATE TABLE a IDENTICAL TO b;”或“CREATE TABLE a BASED ON b;”这样的DDL…尽管有人已经提出了这个问题，但却被拒绝了（这是关于将表复制到另一个模式的概念，但同样的概念也适用于同一模式中具有不同表名的新表）。http://connect.microsoft.com/SQLServer/feedback/details/632689

- Aaron Bertrand

Answer 3

我知道这个回答有点晚了，但是像我一样仍在寻找解决方案的人，可以尝试以下方法：

你不能使用JOIN运算符来继承IDENTITY列属性。但是你可以使用WHERE子句，像这样：

SELECT a.* INTO NewTable FROM MyTable a WHERE EXISTS (SELECT 1 FROM SecondTable b WHERE b.ID = a.ID)

这个方法是可行的。

- Queue Mann