A good introduction to the alternatives, but I take exception to the statement that "it is impractical to create new views for every query you write." Certainly, you wouldn't create a new view for EVERY query, just as you won't use Temp Tables or CTEs in EVERY query.

For the most part CTEs replace subqueries and potentially make the queries more readable, but don't seem to offer much in the way of performance improvement. They do this by allowing reuse (like a view), but also by keeping the entire query contained. This can be good but also reduces code reuse--a view can be used in more than one query. Their main functional benefit over a view is their ability to use the parameters in a SP. This benefit is shared by a TVF, however there are limitations to what can be done in a function, but again Functions are available for code reuse while CTEs are not.

Any design decision will, of course, need to be made in the context of a specific need, balancing code reuse, performance, maintainability, and security.

As Always YMMV,

--

JimFive