wbrianwhite (10/30/2010)
All of the other techniques mentioned can built into a generator
I have a hard time believing this. How would a code generator know enough about your application structure, data size, and database performance to 'decide' now is the time to use an indexed view? CTEs can sometimes be used for performance, if you know which indexes are out there and are analyzing query plans of different approaches for a proc. Otherwise they're useful for code maintainability, which wouldn't be an issue in generated code.
The same way humans do: recognize the pattern. For CTE's, the most useful use is in traversing hierarchies in adjacency list form. Easy enough to recognize.