Use views to create a custom sql query?
The only real downside that I can think of is that the SQL that is generated can be really verbose, as it is not meant to be read by humans.

Very often, I will take a first pass at functionality for something using Views, grab the SQL, and then refine/optimize.  In fact, using Views to generate the initial SQL can be very handy for figuring out what you need to do your JOINs on.

One catch is that Views knows about internal structure.  If you do something that changes underlying table structure, Views will compensate automagically.  You would need to update your SQL manually.  One place this happens is with Drupal 6 and CCK, when you change fields from single value on a single content type to multiple value and/or add them to multiple content types.

All of that said, the new database abstraction layer and EntityFieldQuery does make life easier, and you may not need to use Views as an intermediate step.