Mike C (7/22/2010)

---

Cool, glad it worked well for you. Another optimization you might try is using the t-sql "returns null on null input" option, which allows sql server to...

Incidently, I tested the "Mike C style" function with minimal variable dimensioning (basically a hybrid of what we were trying on the T-SQL side based on the Kevins' postings).

It results...

As per Mike C's insight, the following is an update to the SQL CLR distance function using global constants where possible and handling the SqlDecimal -> Double -> SqlDecimal processing...

Mike C (7/22/2010)

---

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...

Some interesting results!

I first tested the suggested hypothesis of declaring/setting the pi/180 as a constant in the T-SQL udf.

Average time = 1444.4 milliseconds

Then I tested using no declared variables within...

Kevin Rathgeber (7/21/2010)

---

mzak (7/21/2010)

---

Kevin Rathgeber (7/21/2010)

---

I have a comment regarding your T-SQL

In trying to keep all things equal, Shouldn't you declare a variable at the beginning of your function to...

Kevin Rathgeber (7/21/2010)

---

I have a comment regarding your T-SQL

In trying to keep all things equal, Shouldn't you declare a variable at the beginning of your function to hold the value...

Charles,

Great suggestions and my compliments to a 'procedural coder' pursuing 'set-based' material!

I think it's important to note that the constants not only provided a pre-calculated constant as you indicated but,...

Hi wbrianwhite,

I have looked at the geometry and geography data types / support in the later versions of SQL Server.

I don't feel that I'm familiar enough yet to comment conclusively...

Hi Jeff. Here's the requested t-sql implementation.

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE Function [dbo].[Distance] (

@Lat1 as decimal(18, 6),

@Long1 as decimal(18, 6),

@Lat2 as decimal(18, 6),

@Long2 as decimal(18, 6))

Returns decimal(18, 6) With...

Absolutely.

If you look at my first attempt block of code in the article you will notice multiple calculations with

... * Math.PI / 180.0

In my second attempt, I scoped some constants...

I find it interesting that you are concerned about the time to INSERT records as opposed to SELECT records.

As far as design considerations and the effects on performance, I'm not...

I don't disagree with any attempts to request (or demand if you feel it necessary) additional compensation (perks, pay or paid time off). However, if you were hired to...

I'm not sure if anyone else has played around with the extensibility model provided with IIS 7 but it is really cool and could be a method MSFT could use...

Great list. I would throw in "review security privileges"