Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything built-in to import shapefiles?"
Short answer is "no, not built in". There's some vendor products, like SAFE Software's FME series, including an inexpensive consumer version called "Microsoft SQL Server Data Loader" as well as their well-known FME Desktop. And if you have ESRI software, there's likely a shapefile-to-SQL loader in there. But folks just starting out are likely to have heard of a free tool called Shape2SQL. It's not the only free tool out there, but its quite easy to use.
One problem that arises reasonably frequently when importing shapefiles into the GEOGRAPHY data-type is that SQL Server's GEOGRAPHY is sensitive to ring order. Many shapefile authors are not. This manifests itself as an error in Shape2SQL (you can skip the offending row(s) IIRC) because a GEOGRAPHY with the wrong ring order will be almost always look to SQL Server like its greater than a hemisphere. That's an error in SQL Server 2008/2008 R2, but not in SQL Server 2012, where GEOGRAPHY instances of greater than a hemisphere are supported. You won't get the error (unless he's explicitly checking for larger than hemisphere, but I don't think that's the case) but you will have the wrong shape (e.g. instead of Iceland, you'll have everything that ISN'T Iceland!).
If you're sure that you shouldn't have any GEOGRAPHY instances of more than a hemisphere, here's an easy fix for SQL Server 2012.
— assumes you are using SRID 4326 (default) and have a column named "Geog" in table named "spatialtable"
declare @h geography = 'FULLGLOBE'; — SRID 4326 is default
update spatialtable
set Geog = Geog.ReorientObject()
where Geog.STArea() > @h.STArea() / 2;
BTW, you can also use this method if you're doing a conversion from GEOMETRY (which doesn't care about ring order) to GEOGRAPHY and might have ring order problems. If you might have instances greater than a hemisphere, then you will have to "eyeball-check" (meaning, check the instance in the SSMS spatial results tab for reasonableness) instances on both sides of the hemisphere cutoff. If you're on SQL Server 2008, SAFE Software can automatically correct geographies greater than a hemisphere.
@bobbeauch