I agree with Ninja, this will likely quickly become one of the longest threads on the site.

With that said, I personally have never seen a sql procedural solution which is better than a properly done and optimized set based solution. If anyone can provide an example, I would be very interested.

Two things of note though are that it is sometimes faster to code the procedural solution than it is to code the set based one, largely due to habit. It is sometimes the case that that savings in programmer time is far more valuable than the processing speed given up, especially if it is a script being written for one time use. The other is that I do not consider adding a query hint to be moving into procedural programming.

It may be true that using a query hint partially breaks a purely declarative model, but it is still dealing with set, still being written in an otherwise declarative fashion, and still dealing with the hardware at a relatively high level. Whether or not they are purely declarative, they can definitely provide tremendous performance benefits, and I particularly use with (nolock) and (noexpand) on a regular basis.