You could migrate your database to PostgreSQL and use PostGIS.
Make sure you have GiST index covering you polygon shape column and use && operator to find the polygons intersecting with the viewport
SELECT * FROM polygons WHERE geometry && ST_MakeBox2D(ST_Point(x1, y1), ST_Point(x2,y2));
There's no quick and easy way but for the benefit of someone else with the same problem the solution is to convert the geography type to a geometry type, take the bounding box (envelope) of the geometry and pull out the 1st (min) and 3rd (max) points.
CGMinLong AS (CONVERT(float, geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(1).Long)) PERSISTED,
CGMinLat AS (CONVERT(float,geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(1).Lat)) PERSISTED,
CGMaxLong AS (CONVERT(float,geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(3).Long)) PERSISTED,
CGMaxLat AS (CONVERT(float,geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(3).Lat)) PERSISTED
Even with the conversions between types and WKB it still performs pretty well and if you use these min and max values instead of the spatial index (for retrieving shapes within a rectangle for display on the screen) you might find, like i have, that it performs more consistently and normally better than a spatial index search.
The search ends up looking like:
WHERE not(CGMaxLong < @minLon OR CGMinLong > @maxLon OR CGMaxLat < @minLat OR CGMinLat > @maxLat
where the @ parameters are your search area.
Best Answer
Given a polygons table with rectangular polygons, and a points table called points, both with geometry columns called "geom", this should return any polygons that contain any of the points:
SELECT polygons.id FROM polygons, points WHERE CONTAINS(polygons.geom, points.geom);