Similar function to STContains in SQL Server 2008

Question

Since the geo-spacial function called STContains is only available in SQL Server 2012, what's the similar function I can use to get the same or similar results in SQL Sever 2008?

Is STIntersects feasible option?

Cheers!

Answer 1

STContains IS available in SQL Server 2008.

DECLARE @s0 AS GEOMETRY = 'POLYGON((4 4, 7 4, 7 7, 4 7 ,4 4))'
DECLARE @s1 AS GEOMETRY = 'POLYGON((5 4.2, 6 4.2, 6 5, 5 5, 5 4.2))'

SELECT *
 FROM (
    SELECT @s0 AS 'Geo', 'S0' as 'Labels'
    UNION ALL   SELECT @s1, 'S1'
 ) AS A
 WHERE A.Geo.STContains(@S1) = 1

See SQL-Fiddle-Demo.