IS NULL vs. IsNull

When using CLR UDTs, there has been some confusion regarding the difference between the T-SQL IS NULL construct and the required UDT property IsNull. Admittedly, this is not well-documented, so I thought I'd try to clear this up.

To avoid having to store null UDTs, once an instance of a UDT has been found to be null, it is persisted using normal null-handling methods of the server: the server doesn't waste time serializing or deserializing the UDT if it doesn't have to, and it doesn't waste space to store it either. This check is performed every time a UDT is brought over from the CLR, which means that using the IS NULL construct to check for nullity of UDTs should always work.

As an example, consider a table with the schema Points(id int, location Point), where Point is a CLR UDT. And let's consider the difference between the following queries, both of which return the ids of points with non-null locations:

Query 1:
SELECT idFROM PointsWHERE NOT (location IS NULL) -- equivalently: WHERE location IS NOT NULL

Query 2:
SELECT idFROM PointsWHERE location.IsNull = 0

In Query 1, the normal null-handling is used, and there is no deserialization of UDTs needed. Query 2, on the other hand, has to deserialize each object---more specifically, as we'll see below, each non-null object---and call into the CLR to obtain the value of the IsNull property. Clearly, using IS NULL will exhibit better performance. Really, there should never be a reason to read the IsNull property of a UDT from T-SQL code.

So, what is the use of the IsNull property? First, it is needed to determine whether a value is null from within CLR code. Second, the server needs a way to test whether an instance is null, so this property is used by the server. After it determines it is null, then it can use its native null handling to handle it.

Since IS NULL is always preferred over IsNull in T-SQL code, one could argue that we ought either hide the IsNull property from T-SQL code, or rewrite references to IsNull as calls to IS NULL. My own view here is there's no compelling reason to do so: special casing should generally be avoided, and this in no way inhibits functionality. Others may disagree.

One final note: that the get() method of IsNull is not special-cased in any way leads to behavior that may be abstruse, but which is entirely consistent. If a Point variable @p is null, then @p.IsNull will, by default, evaluate to "NULL", not "1". This is because by default the "OnNullCall" parameter of the IsNull get() method defaults to false. Since the object is null, when the property is requested the object is not deserialized, the method is not called, and a default value of "NULL" is returned. This is the case for all UDT methods, but means that the following code will not return the ids of all points which have null locations:

SELECT idFROM PointsWHERE location.IsNull = 1

To have IsNull evaluate to "1" on a null object, the get() method would have to have to be annotated with with "SqlMethod(OnNullCall=true)". Remember: for IsNull, this is probably a bad idea; just use IS NULL. For other methods setting this to true may be reasonable.

- Isaac Kunen, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

Comments

• Anonymous
  June 27, 2005
  can we find a full tutorial for all the new clauses of tsql... i mean some informative article, tutorial for new version of TSQL