Have you ever encountered difficulties when working with spatial data types in SQL Server? If so, you’re not alone. In the past, when a spatial object had an issue or was created with invalid coordinates, SQL Server would simply provide a generic error message without much information. This made it incredibly challenging to debug and fix the problem.

Fortunately, SQL Server 2012 introduced a new function called IsValidDetailed() that has made working with spatial data types much easier. This function allows you to check if a spatial object is valid or not, and if it’s not valid, it provides detailed information about the reason for the invalidity.

Let’s take a look at an example to better understand how IsValidDetailed() works:

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

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

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

DECLARE @p GEOMETRY = 'CIRCULARSTRING(2 2, 4 4, 0 0)'
SELECT @p.IsValidDetailed()
GO

DECLARE @p GEOMETRY = 'CIRCULARSTRING(2 2, 4 4, 0 0)'
SELECT @p.IsValidDetailed()
GO

DECLARE @p GEOMETRY = 'LINESTRING(2 2, 4 4, 0 0)'
SELECT @p.IsValidDetailed()
GO

The above code demonstrates various scenarios with both valid and invalid spatial objects. When executing the queries, you will notice that if the spatial object is valid, the function will return a message indicating its validity. However, if the spatial object is invalid, the function will provide a detailed explanation of the reason for the invalidity.

This new function has greatly simplified the debugging process when working with spatial data types. It allows developers to quickly identify and correct any issues with spatial objects, making their lives much easier.

So the next time you encounter a problem with a spatial object in SQL Server, remember to utilize the IsValidDetailed() function to gain valuable insights into the issue at hand.