What datatype is the null value?

Background

A few weeks ago, I ran into a Transact SQL problem and I was forced to ask myself why two database objects were not compatible.

In the rest of this post, I will share the “foundational”  scenario and try to start answering the question, what datatype is Null.

 

Scenario

Our baseline scenario is to join data from two data-sources.  In our case, data from two sources have being brought into our databases and are now available as two tables.

 

Lab

Let us lay out our database objects.

 

DDL – Tables

 

Let us create our two tables, datatype4null.person and datatype4null.organization.

 

Table – datatype4Null.person

set noexec off
go

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop table datatype4Null.person
*/
if object_id('datatype4Null.person') is not null
begin
    set noexec on
end
go


create table [datatype4Null].[person]
(
      [id] int not null identity(1,1)

    , [name] varchar(255)

    , [employer]  varchar(255) null

    , [dateofBirth]  datetime not null

    , [age] as datediff(year, [dateofBirth], getdate())

    , [gender] bit null

    , [uniqueID]   uniqueIdentifier not null
            constraint defaultDatatype4NullPersonUniqueID default NEWID()

)

go

set noexec off
go

 

Table – datatype4Null.organization

set noexec off
go

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go

/*
    drop table datatype4Null.organization
*/
if object_id('datatype4Null.organization') is not null
begin
    set noexec on
end
go


create table [datatype4Null].[organization]
(
      [id] int not null identity(1,1)

    , [name] varchar(255)

    , [uniqueID]   uniqueIdentifier not null
            constraint defaultDatatype4NullOrganizationUniqueID default NEWID()


)
go

set noexec off
go

 

 

DML – Insert data

Let us insert data into our two tables.

Table – datatype4Null.person – DML

Insert data into the person table.

set nocount on;
go

use [tempdb]
go
 
truncate table [datatype4Null].[person]
go

insert into [datatype4Null].[person]
([name], [employer], [dateofBirth], gender)
values ('Mustapha Thompson', 'NBC News', '4/7/1990', 1)
;


insert into [datatype4Null].[person]
([name], [employer], [dateofBirth], gender)
values ('Stephanie Lo', 'Universal Studios', '3/10/1991', 0)
;

 

 

Table – datatype4Null.organization – DML

Insert data into the organization table.

set nocount on;
go

use [tempdb]
go
 
truncate table [datatype4Null].[organization]
go

insert into [datatype4Null].[organization]
([name])
values ('Staples')
;

 

DDL – Union View

As we will like to expose the data from our datasources as if they were from a single source, we will use a view to do so.

View – datatype4Null.v_entity_basedonTables

Here is the view that uses a union to merge the data from our two tables.

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnTables]
*/
if object_id('[datatype4Null].[v_entity_basedOnTables]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnTables] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnTables]
as

    select 
              1 as [originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[person]  tblP
    
    union all

    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

 

 

DML – Fetch data from Union View

 

Let us fetch data from our union view:
SQL:

select *
from   [datatype4Null].[v_entity_basedOnTables]

 

Output:

datafromUnionOfTwoTables

 

 

So everything is good, we are able to fetch from our union.

 

DDL – View

 

But, what happens if we decide we will need to add a bit of logic and we will place some logic and thus use view instead of tables.

Let us create views rather than tables.

 

View datatype4Null.v_person

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_person]
*/
if object_id('[datatype4Null].[v_person]') is  null
begin
    exec('create view [datatype4Null].[v_person] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_person]
as

    select 
              1 as [originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender


    from    [datatype4Null].[person]  tblP

go

 

View datatype4Null.v_organization

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_organization]
*/
if object_id('[datatype4Null].[v_organization]') is  null
begin
    exec('create view [datatype4Null].[v_organization] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_organization]
as
    
    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

 

 

View datatype4Null.v_entity_basedonViews_DatatypeMismatched

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]
*/
if object_id('[datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]
as

    select 
              tblP.[originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[v_person]  tblP
    
    union all

    select 
              tblO.[originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , tblO.employer
            , tblO.dateofBirth
            , tblO.age
            , tblO.gender

    from    [datatype4Null].[v_organization]  tblO

go

 

 

Query

Let us query our new Union View; the one that query’s the “Views Union“.

    select *
    from   [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]

 

Output:

But, we get an error message.

 

Image:

Msg 245, Level 16, State 1, Line 55
Conversion failed when converting the varchar value 'NBC News' to data type int.

 

Textual:

Msg 245, Level 16, State 1

 

 

Error Diagnosis

Our error is traced back to how SQL Server deduces and assigns datatypes to literal and computed columns.

Our view is defined as :

 

alter view [datatype4Null].[v_organization]
as
    
    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

The value null is defined as as int.

Now thinking forward, to get our datatypes we can issue ” sp_help <object-name>

 

Syntax:

exec sp_help '<object-name>'

 

Sample:

exec sp_help '[datatype4Null].[v_organization]'

 

Output :

datatypeforNullValue

 

Explanation:

From the screen shot above, we can see that SQL Server assigns our null value the int data type.

 

Solution

To fix, we will have to match our datatypes using cast or convert.

An example is pasted below:

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]
*/
if object_id('[datatype4Null].[v_entity_basedOnViews_DatatypeMatched]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]
as

    select 
              tblP.[originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[v_person]  tblP
    
    union all

    select 
              tblO.[originatorCode]
            , cast(tblO.id as int)
            , cast(tblO.uniqueID as uniqueidentifier)
            , cast(tblO.name as varchar)
            , cast(tblO.employer as varchar)
            , cast(tblO.dateofBirth as datetime)
            , cast(tblO.age as int)
            , cast(tblO.gender as bit)

    from    [datatype4Null].[v_organization]  tblO

go

 

Query :

select *
from   [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]

 

Output:

datafromUnionOfTwoViewsUsingMatchedViews

 

Watching / Listening

 

Thanks to God for his Sabbath, my lazy Saturday.

Finally got around to watching Robert Redford’s “Horse Whisperer“.

The movie is almost done, but it is paused as I goggled for which songs goes “I was looking for a soft place to fall“.


A Soft Place To Fall” was written by Allison Moorer, Gwil Owen.

Daylight has found me here again
You can ask me anything, but where I’ve been
Things that used to matter seem so small
When you’re looking for a soft place to fall

Don’t misunderstand me, baby, please
I didn’t mean to bring back memories
You should know the reason why I called
I was looking for a soft place to fall

 

Summary

I think Allison Moorer &  Gwil Owen song’s lyrics makes for a good parting.

Incidentally, Demaryius Thomas story on ESPN ( http://espn.go.com/espn/feature/story/_/id/11830457/denver-broncos-demaryius-thomas-dreams-reuniting-estranged-family ) ended up along the same line.

Eli Saslow closed up his moving tribute by evoking Demaryius’s words :

“I’m making it my responsibility to give her a soft place to land,” he said.

Now that we have taking care of one pallid error message, let us return to things that matter.

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s