DDL Avanzado, Internals y
Optimización de Consultas
Para escribir SQL eficiente, debemos dejar de ver la base de datos como una "caja negra". En este capítulo, abriremos el motor para entender cómo se almacenan y eliminan físicamente los datos (Páginas de 8KB), cómo funciona el Álgebra Relacional detrás de un JOIN y cómo interpretar un Plan de Ejecución para evitar bloqueos masivos.
2. Ingeniería de Eliminación (DDL Internals)
No es solo borrar; es cómo afecta al Log de Transacciones y al Disco.
| Comando | Tipo | Mecanismo Interno (The "Under the Hood") | Impacto Log | Reset Identity |
|---|---|---|---|---|
| DELETE | DML | Row-by-Row: El motor lee cada página, localiza la fila, la marca como borrada y escribe el cambio. Consume mucha I/O. | Alto (1 registro por fila) | |
| TRUNCATE | DDL | Page Deallocation: No toca los datos. Simplemente marca las páginas de 8KB como "libres" en el mapa de asignación (GAM/SGAM). | Mínimo (Solo desasignación) | |
| DROP | DDL | Elimina la metadata del objeto en sys.objects. La estructura deja de existir. Libera espacio inmediatamente. |
Mínimo | N/A |
Mito Común
"TRUNCATE no se puede deshacer (Rollback) porque no guarda log."
Realidad Técnica
Falso. TRUNCATE sí registra la operación en el log (la desasignación de páginas). Si está dentro de una transacción (BEGIN TRAN ... ROLLBACK), es completamente reversible.
3. Fundamentos: Álgebra Relacional
El lenguaje matemático que el optimizador de consultas entiende.
Selección ($\sigma$)
Filtro horizontal de filas.
SQL: WHERE precio > 100
Proyección ($\pi$)
Selección vertical de columnas.
SQL: SELECT nombre, apellido
Producto Cartesiano ($\times$)
Combina cada fila de A con cada fila de B. Crecimiento exponencial.
SQL: CROSS JOIN
Anatomía de un JOIN
Nota: Los motores modernos optimizan esto para no materializar todo el cartesiano en memoria, usando Hash Match o Merge Join.
4. Operadores Físicos de Unión
Cómo el motor une los datos realmente en la CPU y RAM.
Nested Loops
Complejidad: O(N*M)
Toma una fila de la tabla pequeña (Outer) y busca coincidencias en la tabla grande (Inner).
Una tabla es muy pequeña y la otra tiene un índice en la columna de unión.
Hash Match
Costo: CPU + RAM (TempDB)
Crea una tabla Hash en memoria con las filas de la tabla más pequeña y luego escanea la otra tabla para hacer match.
Tablas grandes, sin ordenar y sin índices adecuados.
Merge Join
Costo: Muy bajo (O(N+M))
Lee ambas tablas simultáneamente y avanza punteros. Requiere que ambas entradas estén ordenadas.
Ambas columnas de unión están ordenadas (tienen índices Clustered).
5. Laboratorio de Performance
Interpretación forense de SET STATISTICS IO.
Anatomía de la Salida (Messages Tab)
La Métrica Reina.
Número de páginas de 8KB leídas de la memoria caché (Buffer Pool). Si este número es alto, tu consulta es ineficiente, incluso si es rápida (porque todo estaba en RAM).
Lectura de Disco.
Páginas que NO estaban en memoria y tuvieron que ser traídas del disco duro. Es la operación más lenta.
Predicción del Motor.
El motor predice qué páginas necesitarás y las carga antes de que las pidas. Útil en Table Scans grandes.
The SQL Architect Protocol
Eres el Lead Data Engineer de LegacyRetail S.A.. La base de datos es un desastre de archivos planos y el servidor se bloquea cada mañana. Tienes dos misiones críticas:
El Arquitecto (Diseño 3NF)
- Analiza el archivo
raw_sales_dump.csv. - Diseña un esquema normalizado separando Clientes, Productos y Sucursales.
- Implementa integridad referencial (FK) estricta.
El Auditor (Tuning)
- Replica el incidente del CROSS JOIN.
- Activa
STATISTICS IOy captura la evidencia del desastre. - Compara métricas contra tu solución optimizada (INNER JOIN).