SQL Server - Using spatial data I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user.

The data is being sent from each user once in a second. For every location being reported there is a query that checks other users around that user and returns data about them.

I'm using Lat/Lon as float fields and run a scalar-function to find the closest users.

I guess that approach is not the best. I know there are spatial data constructs in SQL Server, but don't know if they are good. Should I use them? If so, what is the best approach?

What approach should I take to make this operation as fast and scalable as possible?

Maybe NoSQL is better for spatial data?

P.S - I'm talking about thousands and tens of thousands users at any given time.
Yes you should use spatial indexes, but you should also consider there are limitations that simple sargability concerns can resolve.

A spatial index is good for finding locations in a particular area. It's not good for working out which distance is shortest.

So... when trying to find the closest item, start with a small distance and find items in that area. Something like:

select id
from dbo.locations
where location.STIntersects(@point.STBuffer(@somedistance)) = 1;


...and slowly increase the value of @somedistance until you find what you're looking for. This will allow Seeks to be done, quickly eliminating locations that are not of interest.