Since the 2008 version, SQL Server has has support for spatial data. This support also extends to associated tooling like Management Studio. Any query you run against a table/view that contains a geometry/geography column will also show the results visually in the Spatial results tab.
It also turns out queries that return adhoc rows of geospatial data also work in this fashion, for example the result of geometry::STGeomFromText(). These queries don't even have to be against actual tables/views!
These results can also be aggregated together using the SQL UNION ALL keyword.