Listing 1: NULL Test Code

drop table NULLtest
GO
drop table NULLtest2
GO


Listing 1. Callout A: create the tables
create table NULLtest (
   colA	int		null,
   colB	nvarchar(10)	null)
GO

create table NULLtest2 (
   colA	int		null,
   colB	int		null,
   colC	nvarchar(10)	null)
GO

Listing 1. Callout B: load the tables
insert into NULLtest values (1, 'aaaaa');
insert into NULLtest values (2, 'bbbbb');
insert into NULLtest values (NULL, 'ccccc');

insert into NULLtest2 values (1, 1, 'aaaaa');
insert into NULLtest2 values (2, 2, 'bbbbb');
insert into NULLtest2 values (3, NULL, 'ccccc');


Listing 1. Callout C: display the table content
select * from NULLtest;
select * from NULLtest2;

Listing 1. Callout D: try to create a primary key on a nullable column
ALTER TABLE NULLtest WITH NOCHECK 
ADD CONSTRAINT PK_NULLtest PRIMARY KEY CLUSTERED (colA)
GO


Listing 1. Callout E: do NULLs truly propagate? Test this string concatenation...
-- first with CONCAT NULL YIELDS NULL turned on and ANSI_NULLs turned ON... */
select cast(colA as nvarchar) + cast(colB as nvarchar) + colC AS 'Concatenated Value' from NULLtest2;


Listing 1. Callout F: get an average value when there's NULL in the mix?  */
-- first with ANSI_NULLS turned on... 
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
select avg(cast(colB as decimal))from NULLtest2
-- then with ANSI_NULLS turned off... 
SET ANSI_NULLS OFF
SET ANSI_WARNINGS ON
select avg(cast(colB as decimal))from NULLtest2
-- turn off the ANSI warnings and the warning message goes away...
SET ANSI_NULLS ON
SET ANSI_WARNINGS OFF
select avg(cast(colB as decimal))from NULLtest2


Listing 1. Callout G: get a sum of values when there's NULL in the mix
-- first with ANSI_WARNINGS ON
SET ANSI_WARNINGS ON
select sum(colA) AS 'Sum of column A' from NULLtest;
-- then with ANSI_WARNINGS turned off... 
SET ANSI_WARNINGS OFF
select sum(colA) AS 'Sum of column A' from NULLtest;


Listing 1. Callout H: what happens when you try to join on NULL?
select * from NULLtest a JOIN NULLtest2 b ON a.colA = b.colB where b.colB is NULL;
-- no rows returned


Listing 1. Callout I: create a "dummy" dimension record for NULL foreign keys
--SET IDENTITY INSERT NULLtest ON
insert into NULLtest values (-1, '');
--SET IDENTITY INSERT NULLtest OFF


Listing 1. Callout J: substitute -1 for missing foreign keys in the fact table
update NULLtest2 set colB = -1 where colB IS NULL


Listing 1. Callout K: what happens when you try to join on NULL?
select * from NULLtest a JOIN NULLtest2 b ON a.colA = b.colB;