IsNull != IS NULL, the reprise and the solution

About a week or two ago, there was a LONG discussion on the SQLCLR beta newsgroup about the fact that the IsNull property that you use on CLR UDTs won't return TRUE or FALSE inside the server. It returns FALSE or NULL. Turns out that, although you use this property to *indicate* to the engine that your instance is NULL (database NULL, not null reference/value), the engine will optimize things by storing the fact that your instance is NULL. And so, a method called on a NULL instance yields NULL.

This was posted by the SQL CLR team here with a workaround if you *really* wanted this to work right, even inside the server. The workaround was to decorate your IsNull get method with:

[SqlMethod(OnNullCall=true)]

I tried this, it didn't work any better. But it WILL work if you use the correct field on the SqlMethod attribute. It's

[SqlMethod(InvokeIfReceiverIsNull=true)]

The difference is OnNullCall indicates whether a method will be called if any of its input parameters are NULL. This (OnNullCall=false) allows you to use non-SqlTypes as method parameters in your .NET code and not crash if someone passes in a NULL value. InvokeIfReceiverIsNull indicates whether the method will be called if the instance of the class itself is NULL. Obviously, not null class (you can't call a method on a null reference, for example), but database NULL.

This does work as advertised:

CREATE TABLE UDTTab (theUDT sometype);
go
INSERT UDTTab VALUES(NULL);
go
SELECT COUNT(*) FROM UDTTab where theUDT IS NULL
SELECT COUNT(*) FROM UDTTab where theUDT.IsNull = 1
go

both counts return 1.

I suppose its much easier "best practice" to remember is always use the SQL IS NULL in SQL statements. Because it's FASTER. They don't have to instanciate all those NULL UDT instances, just to confirm that IsNull is, indeed, true. And mark your “get” method for those who forget.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.