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:

  1. it gives code

    ISNULL (orderid + 0, -1) as orderid,
    --this will remove IDENTITY property and make column not null
  2. 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, 
isnull (cast(orderdate as date), '19000101') as orderdate,
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.

  • Hello Aaron, thanks for answering my question. I understood what makes a column is nullable or not null when using Select...Into statement in SQL Server. – user2061466 Feb 7 '16 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.