• Oh yeah, almost forgot to mention, your performance difference between the version with global constants and the one with no global constants is probably due to implicit conversions from Decimal and/or SqlDecimal to the .NET Double type.

    That is to say when you define Decimal constants like EarthRadiusInMiles = 3956.0, .NET implicitly converts 3956.0 from Double to Decimal for you. If you define it at the top, it only has to do the implicit conversion once. If you just use 3956.0 in the code in a calculation the implicit conversion would have to take place for every reference. I tried to modify your code, as shown below, to do all internal calculations using the Double data type and convert from/to SqlDecimal only at the beginning and end of the calculation. Please forgive if I have any typos below, I usually write SQL and C#.

    ' Code begins here

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Imports System.Math

    Partial Public Class UserDefinedFunctions

    Public Const EarthRadiusInMiles As Double = 3956.0

    Public Const RadianConversionConst As Double = Math.PI / 180.0

    <SqlFunction()> _

    Public Shared Function Distance( _

    ByVal Latitude1 As SqlDecimal, _

    ByVal Longitude1 As SqlDecimal, _

    ByVal Latitude2 As SqlDecimal, _

    ByVal Longitude2 As SqlDecimal) As SqlDecimal

    ' Return NULL automatically if any nulls are passed in

    ' Use OrElse for short-circuit logic

    If (Latitude1.IsNull OrElse Latitude2.IsNull OrElse Longitude1.IsNull OrElse Longitude2.IsNull) Then

    Return SqlDecimal.Null

    End If

    ' Explicitly cast all inputs to Double and do all calculations as Doubles

    Dim Lat1 As Double = CType(Latitude1.Value, Double)

    Dim Long1 As Double = CType(Longitude1.Value, Double)

    Dim Lat2 As Double = CType(Latitude2.Value, Double)

    Dim Long2 As Double = CType(Longitude2.Value, Double)

    Dim Latitude1Radian As Double = Lat1 * RadianConversionConst

    Dim Longitude1Radian As Double = Long1 * RadianConversionConst

    Dim Latitude2Radian As Double = Lat2 * RadianConversionConst

    Dim Longitude2Radian As Double = Long2 * RadianConversionConst

    Dim LongSpanRadian As Double = Longitude2Radian - Longitude1Radian

    Dim LatSpanRadian As Double = Latitude2Radian - Latitude1Radian

    'intermediate result a

    Dim a As Double = Math.Pow(Math.Sin(LatSpanRadian / 2.0), 2) + Math.Cos(Latitude1Radian) * Math.Cos(Latitude2Radian) * Math.Pow(Math.Sin(LongSpanRadian / 2.0), 2)

    'intermediate result c (great circle distance in radians)

    Dim c As Double = 2.0 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1.0 - a))

    Return New SqlDecimal(EarthRadiusInMiles * c)

    End Function

    End Class

    ' Code ends here

    Mike C