One of my favorite things about ColdFusion compared to other languages is the CFQUERY tag. It is just a real thing of beauty. I rarely use it any more, however, because I use DataMgr for my every day CRUD work (actually I use Manager.cfc, but that is another story). I rarely have to write any SQL code any more.
Rarely isn't the same as never, though. I have actually found a spectrum of cases for which I need to write custom SQL. First is the case where I need something more complicated than what DataMgr is built to handle and I don't need any of the functionality that DataMgr provides. In this case, I can easily move to using CFQUERY and I do so happily.