I'm looking at the geometry data type in Sql Server 2008 and it looks interesting but the documentation does not seem that good. Would I be able to store cones, cylinders and spheres in the WKT format? These features traditionally have a radius and not hundreds of points.
To the best of my knowledge your are going to have to use an external application to render your shape as a large number of points. See this blog for a technique for rendering circles.
One possibility to consider is a CLR proc to render the shapes you need and a view with indexes. I don't know how well that will perform, but it just might work.