SQL Spatial Join
This should work:
SELECT polyTable.[PolygonID], pointTable.[PointID]FROM [PolygonTable_Name] polyTable WITH(INDEX([SPATIAL_INDEX_NAME]))INNER JOIN [PointTabl_Name] pointTableONpolyTable.Geog.STIntersects(pointTable.Geog) = 1
I have added an index hint " WITH(INDEX(...)) " as this will speed up the query.