Cita: cual es el mencionado Optimizador de Consultas?
El optimizador de consultas (busca query optimizer) es el componente del motor de SQL que determina la mejor manera de ejecutar tu consulta tratando de obtener el resultado más rápido con un mínimo impacto en los recursos del sistema. Esa podría ser una definición.
Dicho componente determina si una consulta dada usará o no un índice, y qué tipo de operación realizará (Table Scan, Index Seek, Index Scan, Merge Join, Loop Join, Hash Match, etc.), si usará o no paralelismo, etc. No es directamente accesible por el usuario, sin embargo, con los "optimizer hints" puedes modificar la manera en que se construye el plan de ejecución para una consulta determinada.
A veces he tenido que trabajar con estándares locales de los cuales no debo desviarme. Sin embargo, muchos de estos estándares han sido implementados por programadores, no por dba's.
Si estoy programando en C++, C#, VB, o lo que quieras, en general es una muy buena idea reusar código con funciones. Una función que calcula el RFC suena como algo útil. En SQL también lo puede ser. Sin embargo una función que busca en una tabla un dato a partir de un ID, puede no ser tan buena idea. El motivo es que si la vas a emplear en un where, esta será invocada una vez quizá. Pero si la usas en el juego de resultados, esta será invocada tantas veces como filas haya en tu resultado. Y el tiempo acumulado de las sucesivas invocaciones puede ser significativo, mucho más si la consulta en la función no tiene un índice adecuado.
En cuanto a las tablas temporales, te puedo decir que en general son usadas en demasía. Algunas veces, pueden ser muy útiles. He trabajado durante los últimos dos años con datawarehouses y datamarts, haciendo a veces joins entre tablas con millones de registros, y en general una tabla temporal es mí última alternativa. El costo de una tabla temporal es por el tiempo que demora su creación física. Y porque en general al crear una tabla temporal nadie les crea indices (lo cual también implica un costo).
Quizá con unos cuantos miles de de registros no tengan problemas, pero si han trabajado con tablas verdaderamente masivas, sabrán que con esta clase de consultas... sin comentarios.
PD: Si buscas RBAR (row by agonizing row) en el contexto de SQL, encontrarás muchos ejemplo de consultas que aparentemente están bien construidas, y que sin embargo operan fila a fila.