How to Control Nullability in Select into for Literal-Based Columns

How to control nullability in SELECT INTO for literal-based columns

It looks like a definitive answer is here. Copying here:

Metadata is determined based on the source column and expressions used
in the SELECT list. Below are the rules:

1. 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

2. Literals, constants, global variables like @@DBTS, @@ERROR etc are considered non-NULLable since they return some value always

3. If expression is a column then nullability is derived from the source column metadata

   So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.

So, it looks like you are safe to use your CAST expression.

Create not nullable column using SQL Server SELECT INTO

It's more simple then I thought:

CREATE TABLE SO_IN
(
    RAW NVARCHAR(10) NOT NULL
) ;
GO

SELECT
    RAW,
    ISNULL(RTRIM(RAW),'') AS RTRIM,
    LTRIM(RAW) AS LTRIM
INTO SO_OUT
FROM
    SO_IN;

SELECT COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'SO_OUT'

Create a nullable column using SQL Server SELECT INTO?

Nullability is inherited from the source column.

You can lose or gain nullability with an expression:

Example (constant literals appear to be problematic - need a good NOOP function which can return NULL):

CREATE TABLE SO5465245_IN
    (
     a INT NOT NULL
    ,b INT NULL
    ) ;
GO

SELECT  COALESCE(a, NULL) AS a
       ,ISNULL(b, 0) AS b
       ,COALESCE(10, NULL) AS c1
       ,COALESCE(ABS(10), NULL) AS c2
       ,CASE WHEN COALESCE(10, NULL) IS NOT NULL THEN COALESCE(10, NULL) ELSE NULL END AS c3
INTO    SO5465245_OUT
FROM    SO5465245_IN ;
GO

SELECT  TABLE_NAME
       ,COLUMN_NAME
       ,IS_NULLABLE
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME LIKE 'SO5465245%'
ORDER BY TABLE_NAME
       ,ORDINAL_POSITION ;
GO

DROP TABLE SO5465245_IN ;
GO

DROP TABLE SO5465245_OUT ;
GO

Setting not null when casting data during SELECT INTO

When creating a new column with SELECT INTO, you can coerce not-null by wrapping the CAST expression in ISNULL. The explict CAST of the literal determines the data type returned by ISNULL as well as the value inserted into the new column. This provides full control over the resultant column type, length, precision, and scale. Without the CAST SQL Server will infer the data type and properties from the specified literal.

Examples:

ISNULL(CAST('' AS varchar(20)),'') AS varcharColumn
ISNULL(CAST(0 AS int),0) AS intColumn

In the case of an existing column, the source column data type and nullability is used by default. You can similarly coerce a different data type with CAST and/or use ISNULL to change the nullability to NOT NULL.

Examples:

ISNULL(CAST(varchar10Column AS varchar(20)),'') AS varcharColumn
ISNULL(intColumn,0) AS intColumn

SQL literal value that is alternative to NULL

You can use function CAST to convert the format to VARCHAR to be considered as string.

Is there a way to make a column's nullability depend on another column's nullability?

Assuming you are on SQL Server or something similar, you can do this with a CHECK constraint on your table. (Unfortunately, MySQL parses but ignores CHECK constraints, so you'd have to use a trigger for that platform.)

If the table already exists:

ALTER TABLE ADD CONSTRAINT CK_ExitDateReason
CHECK (
      (ExitDate IS NULL AND ExitReason IS NULL) 
   OR (ExitDate IS NOT NULL AND ExitReason IS NOT NULL) 
);

If you are creating the table yourself:

CREATE TABLE dbo.Exit (
     ...

   , CONSTRAINT CK_ExitDateReason CHECK ...
);

Using a check constraint is preferable to using a trigger because:

• check constraints are more visible than triggers
• the constraint is part of the table definition, as opposed to code that is run separately, so it's logically cleaner
• I am willing to bet it is faster than a trigger too

Why would using column aliases in a select statement fill in null values with the column name?

In most SQL dialects, single quotes are used as string literals. So in your query:

select 
    A as column1, 
    'B 1' as column2, 
    'C 2' as column3
from {{ ref(`my_data`)}}

You are not selecting from the columns called B 1 and C 2, you are selecting the string literals "B 1" and "C 2", which is why your result is what it is.

In Postgres-like databases, you should use double quotes for names with spaces in them:

select 
    A as column1, 
    "B 1" as column2, 
    "C 2" as column3
from {{ ref(`my_data`)}}

In MySQL-like dialects (including Google BigQuery), you should use backticks instead:

select 
    A as column1, 
    `B 1` as column2, 
    `C 2` as column3
from {{ ref(`my_data`)}}

Null Value Statement

In a CREATE TABLE, the NULL or NOT NULL here varchar(50) null is a constraint that determines whether NULLs are allowed. NOT NULL means no.

When you inserted data, which statement did you run?

INSERT TABLE1 VALUES (Null, 1, First, Null)

or

INSERT TABLE1 VALUES ('Null', 1, First, 'Null')

• The first one uses the keyword NULL, inserts a NULL (not a null value: no such thing, arguably). No values is stored except in the NULL bitmap fields
• The second one has a string "null" and the characters N, U, L, L + 2 bytes for length are stored

When you run SELECT * FROM TABLE1, client tools will show NULL.

To test whether you actually have NULLs or the string NULL, run this

SELECT ISNULL(name, 'fish'), ISNULL(date, GETDATE())  FROM TABLE1

For the SELECTs

--null symbols. No value stored
SELECT * FROM TABLE1 WHERE NAME IS NULL
--string null
SELECT * FROM TABLE1 WHERE NAME = 'NULL'
--empty string
SELECT * FROM TABLE1 WHERE NAME = ' '

Note: null symbol/value is not empty string. It has no value and won't compare. Even to itself.

As for your DBA, the code above with ISNULL will decide what is stored.

Edit: if you are storing null symbol/value, then your DBA should read up on "null bitmap"

---

Related Topics