SQL Server Why is geography::Point inside function non-deterministic?

Question

I store Latitude and Longitude (float null) data from the user, and want to create a persisted computed column of Geography data type based on these values (with the intention of using a spatial index on this column).

The computed column formula is:

(case when @lat IS NULL OR @lng IS NULL then NULL else [geography]::Point(@lat,@lng,(4326)) end)

This works as expected when persisted is set to true on the computed column (returns a Geography value when Latitude and Longitude are present, returns null otherwise.

However, I wish to abstract this into a user-defined function, as other tables will be using the same calculation. I have created the following function:

CREATE FUNCTION [dbo].[GetGeography] 
(
    @lat float null,
    @lng float null
)
RETURNS geography
AS
BEGIN   
    RETURN (case when @lat IS NULL OR @lng IS NULL then NULL else [geography]::Point(@lat,@lng,(4326)) end)
END

When I try to use this function in the computed column formula, and set persisted to true, I get the following error message:

Computed column 'Geog_LatLng' in table 'Tmp_Locations' cannot be persisted because the column is non-deterministic.

There is no documentation about the determinism of POINT, and the documentation on Deterministic Functions doesn't mention geography or point .

The short-term solution is to just replicate the formula across all tables, but I would like to know if it is possible to abstract the formula into a user-defined function.

I would suggest trying to create the function `with schemabinding` and see if that clears up your issue. — Ben Thul, May 26 '19 at 16:25
This may explain why you need schema binding: [The Halloween Problem](https://sqlperformance.com/2013/02/sql-plan/halloween-problem-part-4#schemabinding) — Alex, May 26 '19 at 18:42

score 0 · Answer 1 · answered May 27 '19 at 08:50

The solution is to use with schemabinding:

CREATE FUNCTION [dbo].[GetGeography] 
(
    @lat float null,
    @lng float null
)
WITH SCHEMABINDING -- <--- modified
RETURNS geography
AS
BEGIN   
    RETURN (case when @lat IS NULL OR @lng IS NULL then NULL else [geography]::Point(@lat,@lng,(4326)) end)
END

An explanation is in the Docs.

SQL Server Why is geography::Point inside function non-deterministic?

1 Answers1