Select Into removes IDENTITY property from target table I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement.


  source table name: Sales.Orders
  column name: orderid
  column property: int type, not null, defined as a primary key, identity property is defined on the column)
  Column name: custid
  column property: int type, foreign key, null
  target table name: Sales.MyOrders


In the 70-461 exam book - Querying Microsoft SQL Server 2012, chapter 10 p336:


it gives code

ISNULL (orderid + 0, -1) as orderid,
--this will remove IDENTITY property and make column not null

Make custid column not null in target able

ISNULL (custid, -1) as custid, --make column not null



I don't understand these two code items, though it works.

Could someone tell me the concepts behind the code?

The code provided in the book:

select isnull (orderid + 0, -1) as orderid, 
isnull (custid, -1) as custid, 
empid,
isnull (cast(orderdate as date), '19000101') as orderdate,
shipcountry,
freight
into Sales.MyOrders
from Sales.Orders
where shipcountry = N'Norway';
The behaviors you're curious about are explained in the documentation:


  When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
  ...- The identity column is part of an expression.
  ...If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.


And in this thread from UC:


  Metadata is determined based on the source column and expressions used
  in the SELECT list. Below are the rules:
  
  Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the
  engine. So if you use CAST(somecol as char(8)) then the expression is NULLable.
  ...
  So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.