Adam


>And I use the Sub-select syntax A LOT now. It basically means you never really need VIEWs anymore, which I used to need for all sorts of situations.

I think Views are good when you can set the conditions up once and just keep on using it. I'm lucky that until this I haven't had a situation where a parameterised bit of simple SQL (for a given value of simple) or script hasn't sufficed

>The huge advantage of sub-selects is that the whole statement is visible in one place and you don't end up with dependencies. There are still times when a centralized VIEW, which can be accessed by a number of other SELECTs is useful, but there is also the risk of someone altering a view and generating unintended consequences.

and since its treated like a table much less subject to review

Roy

A final critical drawback for VIEWs for me is that you need full control of a database to ALTER, whereas you can rewrite any piece of SQL at will.

If your database has a lot of up-time (and worse if it is replicated and therefore multi-instance) it can be very hard to find ways to ALTER.