Follow up questions are the most fun part of writing a blog post. Earlier I wrote about SQL SERVER – Validating Spatial Object with IsValidDetailed Function and today I received a follow up question on the same subject. The question was mainly about how NULL is handled by spatial functions. Well, NULL is NULL. It is very easy to work with NULL. There are two different ways to validate if the passed in the value is NULL or not.

1) Using IsNULL Function

IsNULL function validates if the object is null or not, if object is not null it will return you value 0 and if object is NULL it will return you the value NULL.

DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.ISNULL ObjIsNull
GO
DECLARE @p GEOMETRY = NULL
SELECT @p.ISNULL ObjIsNull
GO

2) Using IsValidDetailed Function

IsValidateDetails function validates if the object is valid or not. If the object is valid it will return 24400: Valid but if the object is not valid it will give message with the error number. In case object is NULL it will return the value as NULL.

DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.IsValidDetailed() IsValid
GO
DECLARE @p GEOMETRY = NULL
SELECT @p.IsValidDetailed() IsValid
GO

When to use what?

Now you can see that there are two different ways to validate the NULL values. I personally have no preference about using one over another. However, there is one clear difference between them. In case of the IsValidDetailed Function the return value is nvarchar(max) and it is not always possible to compare the value with nvarchar(max). Whereas the ISNULL function returns the bit value of 0 when the object is null and it is easy to determine if the object is null or not in the case of ISNULL function. Additionally, ISNULL function does not check if the object is valid or not and will return the value 0 if the object is not NULL.

Now you know even though either of the function can be used in place of each other both have very specific use case. Use the one which fits your business case.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Spatial Database, SQL Spatial