Does SQL Server need extensions to do spatial like Oracle does? Just wondering if there will be extra costs for implementing a SQL Server backend to SDE.
@pecoanddeco is correct. As of SQL Server 2008, the spatial capabilities are built in. They even come with the express editions.
Just to be 100% clear, though, the spatial capabilities are not completely necessary for ArcSDE. ArcSDE uses its own spatial format by default, which means that you do not need a spatially enabled database in the backend. However, if you have a spatially enabled database in the backend, you have much more flexibility to use the database native spatial types instead of the SDE Binary spatial type.
All the SQL Server spatial stuff comes as part of the standard license so there's no extra cost, so it's a bit different to the Oracle Locator/Spatial split.
There a bit more detail here (though it's a bit buried):