title: SELECT INTO behavior and the IDENTITY property
body: <p>I've been working on a project and came across some interesting behavior when using SELECT INTO. If I have a table with a column defined as <code>int identity(1,1) not null</code> and use SELECT INTO to copy it, the new table will retain the IDENTITY property unless there is a join involved. If there is a join, then the same column on the new table is defined simply as <code>int not null</code>.</p>
  
  <p>Here is a script that you can run to reproduce the behavior:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I noticed that the execution plans for both the WithJoin and WithSubSelect queries contained one join operator. I'm not sure if one will be significantly better on performance if we were dealing with a larger set of rows.</p>
  
  <p>Can anyone shed any light on this and tell me if there is a way to utilize SELECT INTO with joins and still preserve the IDENTITY property?</p>