Prior to SQL 2008, the most common solution was to use a UDF to calculate the great-circle distance between two points on a sphere. [The Haversine formula][1] is probably the most commonly used method.

Of course the Earth is not actually a perfect sphere, but this was considered "good enough" for most uses.

In SQL 2008, as you anticipated, such calculations are simplified and made more accurate by the introduction of the Geography and Geometry data types. Here's a brief sample of how you can use them to simplify distance calculations.

    DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
    DECLARE @loc1 geography
    DECLARE @loc2 geography
    
    
    INSERT INTO @locations 
    VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))	--Note: Lat, Long, SRID
    																--The 4326 is the SRID (spatial reference id) used by SQL as 
    																--a reference to the WGS 84 Standard. This is the same reference
    																--used by the GPS system
    INSERT INTO @locations 
    VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))
    
    SELECT * FROM @locations
    
    SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
    SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'
     
    SELECT @loc1.STDistance(@loc2) / 3.2808399	--STDistance is in meters so we divide to convert to feet 


The SRID is the key to the improved accuracy. The [WGS 84][2] specification to which it refers includes a standardized coordinate system and a reference ellipsoid.  In other words, it accounts for the non-spherical nature of the Earth, giving better results than a pure spherical Great Circle calculation. 

If GIS accuracy is important to your work, this is the simplest way to implement it in SQL 2008.

  [1]: http://en.wikipedia.org/wiki/Haversine_formula
  [2]: http://en.wikipedia.org/wiki/World_Geodetic_System