Strange behavior with NULLs

I’ve always enjoyed dorking with NULLs. Couple of new ones. 

I wrote an article a few years back entitled Nulls and Related Oddities. I think it was published by SQL Server Standard, the hard copy magazine that used to be published by the SQLServerCentral.com folks. It was a pretty good read of just dorking with NULL values in the database…I should maybe someday publish a part 2. But not there. I think the mag has gone bye-bye.  Anyway, here’s one that you’ve never thought about maybe – did you know that NULL has a data type? Run the following (SQL Server 2008):

SELECT TOP (1) NULL as Whatisthetype
INTO NullDatatypes
FROM sys.sysobjects

Crack open SSMS and look at the table. Cool. We notice that the default data type for a NULL is an integer. Remember that if you create a placeholder while using SELECT INTO, you always have to CAST the data type that you want by doing a CAST (NULL as char (1)); now you have a NULL type of CHAR.

I know what you are thinking. “Oh there’s no way that a NULL can have a type”. And you’re probably right. The SQL engine has to dedicate a value to a NULL, or what might replace a NULL, so why not an integer type. Joe Celko once told me, “well, the compiler has to assign something!”. And Joe does know databases. Word.

How about this one:

SET ANSI_NULLS OFF
IF NULL = NULL    
    SELECT 1

-----------
1

(1 row(s) affected)

    
IF NULL = CAST(NULL as INT)
    SELECT 1    

-----------
1

(1 row(s) affected)


IF NULL = CAST(NULL as CHAR(1))
    SELECT 1    

-----------
1

(1 row(s) affected)    

First, we turn ANSI_NULLs off and then run our queries. First, NULL equals NULL isn’t too terribly exciting. Casting a NULL as an int shouldn’t be either, since we already said that integer placeholders for NULL is the default type. But how about casting a CHAR? A CHAR NULL equals an int null? Seems strange to me. It would seem that NULL really doesn’t hold types other than in an instance like the SELECT INTO, above. Err, hold that thought and run this one:

IF UPPER(NULL) = LOWER(NULL)
    SELECT 1    

Command(s) completed successfully. 

I really like this one! A lower and an upper Null? LOL.

Have any cool ones to share? Please send them to me – I’d like to add to my collection. But, do it fast. They’ve deprecated the ANSI_NULLs setting. What will we do to kill time when it’s gone?

Lee

--------------------------------

“Hey that's the motherf- I mean... that's the gentleman that had me busted.”  Billy Ray Valentine