¡Hola, comunidad! Hoy quiero hablarles de un concepto súper útil en SQL: las variables. Las variables te permiten almacenar valores temporales que puedes reutilizar en tu consulta, haciendo que tu código sea más eficiente y fácil de leer.
Introducción a las Variables en SQL
Veamos cómo funcionan y cómo puedes utilizarlas.
¿Qué son las Variables en SQL?
Las variables en SQL son espacios temporales donde puedes almacenar un valor o conjunto de datos para luego usarlos dentro de una consulta o procedimiento. Son útiles cuando necesitas usar un valor en múltiples partes de tu código o si el valor que vas a utilizar depende de un cálculo previo.
Declaración de Variables
En algunos sistemas de bases de datos (como SQL Server o MySQL), las variables se declaran usando la instrucción DECLARE. Luego usamos esa variable en la consulta SELECT para filtrar los resultados.
Existen diferentes tipos de variables, siendo los más comunes:
Lea también: Declarar Impuestos con Infonavit
- Variables escalares: Estas variables almacenan un solo valor. Por ejemplo, puedes usar una variable para almacenar un nombre, una fecha o un número.
- Variables de tabla: Algunas versiones de SQL permiten declarar variables de tipo tabla.
Explorando las Variables de Tabla en SQL
Las variables de tabla son una herramienta poderosa que nos permite manejar conjuntos de datos de manera temporal dentro de nuestras consultas y procedimientos.
¿Qué es una Variable de Tabla?
El tipo de dato table es un tipo de dato especial usado para almacenar un conjunto de resultados y procesarlo en otro momento. Este se usa principalmente para almacenar temporalmente un conjunto de filas que se devuelven como el conjunto de resultados de la función con valores de tabla.
Las funciones y las variables se pueden declarar como de tipo table. Las variables table se pueden usar en funciones, procedimientos almacenados y lotes. Una variable table se comporta como una variable local y tiene un ámbito bien definido. Dentro de su ámbito, la variable table se puede usar como una tabla normal, pudiendo aplicarse en cualquier lugar donde se utilice una tabla o expresión de tabla en las sentencias SELECT, INSERT, UPDATE, y DELETE. La variable de tabla se puede mezclar con cualquier otro conjunto.
Sintaxis y Declaración de Variables de Tabla
La declaración de una variable de tipo table incluye definiciones de columna, nombres, tipos de datos y restricciones, utilizando el mismo subconjunto de información que se usa para definir una tabla en CREATE TABLE. Aquí se incluyen los elementos y definiciones fundamentales.
Los únicos tipos de restricción permitidos en la declaración de una variable de tabla son PRIMARY KEY, UNIQUE, NULL y CHECK. Otros elementos que se pueden especificar incluyen:
Lea también: Guía para Declarar Aportaciones Voluntarias
- collation_name: Especifica la intercalación de la columna. Puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL y solo es aplicable a las columnas de los tipos de datos char, varchar, text, nchar, nvarchar y ntext. Si no se especifica collation_definition, la columna hereda la intercalación de la base de datos actual.
- DEFAULT: Especifica el valor suministrado para la columna cuando no se ha especificado explícitamente un valor durante una inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna, excepto las columnas definidas como marca de tiempo o con la propiedad IDENTITY. Solo un valor constante, como una cadena de caracteres; una función del sistema, como SYSTEM_USER(); o NULL se puede usar como valor predeterminado.
- IDENTITY: Indica que la nueva columna es una columna de identidad. Cuando se agrega una nueva fila a la tabla, SQL Server proporciona un valor incremental único para la columna. Las columnas de identidad se usan normalmente con restricciones PRIMARY KEY para servir como identificador de fila único para la tabla. La propiedad IDENTITY se puede asignar a columnas tinyint, smallint, int, decimal(p,0) o numeric(p,0). Solo se puede crear una columna de identidad para cada tabla. Los valores predeterminados y restricciones DEFAULT enlazados no se pueden usar con una columna de identidad. Se debe especificar los dos argumentos, seed e increment, o ninguno.
- ROWGUIDCOL: Indica que la nueva columna es una columna de identificador único global de fila. Solo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL.
- NULL: Indica si NULL se permite en la variable.
- CLUSTERED/NONCLUSTERED: Se puede indicar que se crea un índice agrupado o no clúster para la restricción PRIMARY KEY o UNIQUE. CLUSTERED solo se puede especificar para una restricción.
Almacenamiento y Ámbito
Una variable de tabla no es una estructura de solo memoria. Dado que una variable de tabla puede contener más datos de los que caben en la memoria, debe tener un lugar en el disco para almacenar datos. Las variables de tabla se crean en la base de datos tempdb, similar a las tablas temporales.
Diferencias Clave: Variables de Tabla vs. Tablas Temporales
Existen diferencias importantes al diseñar un procedure con tablas temporales. El tiempo de ejecución puede variar considerablemente si seleccionamos incorrectamente el tipo de tabla a utilizar.
Para ayudar a comprender estas diferencias, presentamos una comparación:
| Característica | Variable de Tabla (@table) | Tabla Temporal (#table) |
|---|---|---|
| Estadísticas | No se crean estadísticas. Cardinalidad 1 (asumida por el optimizador, antes de la compilación diferida). | Se crean estadísticas automáticamente. Estimaciones de cardinalidad precisas. |
| Recompilaciones | No desencadenan nuevas compilaciones. Están aisladas del lote que las crea, evitando "re-resolución". | Pueden requerir recompilación del query dentro del mismo batch, ya que SQL Server desconoce la definición de la tabla en la primera compilación. |
| Índices | No se podían crear índices explícitamente antes de SQL Server 2014. A partir de SQL Server 2014, se pueden crear índices alineados como parte de la definición de tabla. | Se pueden crear índices explícitamente después de su creación para mejorar el rendimiento. |
| Ámbito | Ámbito local bien definido (variable local). Solo se puede hacer referencia en su ámbito local. | Su ámbito es el de la sesión o conexión. Pueden necesitar "re-resolución" para hacer referencia desde un procedimiento almacenado anidado. |
| Truncar | No se puede truncar (TRUNCATE TABLE). | Se puede truncar (TRUNCATE TABLE). |
| Modificación de estructura | No pueden modificarse una vez que están creadas. | La estructura puede ser modificada (ALTER TABLE). |
| UDF en restricciones | No se pueden usar funciones definidas por el usuario (UDF) en un CHECK CONSTRAINT, columna calculada o DEFAULT CONSTRAINT. | Permite UDF en estas restricciones. |
| Grandes cantidades de filas (>100) | Usar con precaución. En muchos casos, el optimizador crea un plan de consulta suponiendo que la variable de tabla tiene cero o una fila. | Mejor solución. El optimizador usa estimaciones de cardinalidad basadas en datos reales. |
| Optimización basada en costos | No se admiten en el modelo de razonamiento basado en costos del optimizador de SQL Server. | Preferidas cuando se requieren elecciones basadas en costos para lograr un plan de consultas eficaz. |
| Planes de ejecución en paralelo | Las consultas que modifican variables de tabla no generan planes de ejecución de consultas en paralelo. | Pueden generar planes de ejecución en paralelo. |
| Almacenamiento | En la base de datos tempdb. | En la base de datos tempdb. |
Rendimiento y Consideraciones
El hecho de evitar recompilar un procedure no siempre significa que su tiempo de ejecución va a disminuir. Tal vez puede ayudarnos cuando tenemos un sistema altamente transaccional, donde los registros de las tablas no son muy grandes y el procedure de ejecuta varias veces por segundo; en este caso posiblemente funcione mucho mejor con una tabla tipo variable.
Por ejemplo, en algunas pruebas el mismo query con una tabla temporal, se ejecutó en menos de 1 segundo y con una tabla variable 1 minuto 20 segundos. Sus tiempos de ejecución son completamente diferentes. Al generar el plan de ejecución estimado, ambos queries pueden parecer iguales, pero al habilitar el plan de ejecución actual, se puede observar que cambia completamente el plan para el query con la tabla temporal, utilizando un Hash Match en lugar del Nested Loop. Esto sugiere que debemos hacer uso de tablas variables cuando estamos seguros que el plan de ejecución que se genera es el más adecuado y queremos evitar las recompilaciones.
Lea también: ¿A partir de qué ingresos debes declarar?
Siempre debemos probar las dos opciones para determinar cuál es la más eficiente para un escenario específico. En general, se usan variables de tabla siempre que sea posible, excepto cuando hay un volumen significativo de datos y se repite el uso de la tabla. En ese caso, puede crear índices en la tabla temporal para mejorar el rendimiento de las consultas. Sin embargo, cada escenario puede ser diferente.
Limitaciones de las Variables de Tabla
A pesar de sus ventajas, las variables de tabla tienen ciertas limitaciones importantes:
- No se puede truncar.
- No pueden modificarse (su estructura) una vez que están creadas.
- No podemos usar funciones definidas por el usuario (UDF) en un CHECK CONSTRAINT, columna calculada o DEFAULT CONSTRAINT.
- No se crean estadísticas.
- La tabla variable siempre tiene cardinalidad 1, porque no existe en su compilación (antes de la compilación diferida).
- Por este motivo, las variables table deben usarse con precaución si se espera una gran cantidad de filas (más de 100). En estos casos, las tablas temporales pueden representar una mejor solución.
- Las variables table no se admiten en el modelo de razonamiento basado en costos del optimizador de SQL Server. Por lo tanto, no se deben usar cuando se requieren elecciones basadas en el costo para lograr un plan de consultas eficaz. Se prefieren las tablas temporales cuando se requieren opciones basadas en costos.
- Las consultas que modifican variables table no generan planes de ejecución de consultas en paralelo. El rendimiento puede verse afectado cuando se modifican variables table muy grandes o variables table en consultas complejas. Considere la posibilidad de usar tablas temporales en situaciones donde se modifican las variables table.
- No puede usar la instrucción EXEC ni el procedimiento almacenado sp_executesql para ejecutar una consulta de SQL Server dinámica que haga referencia a una variable de tabla, si la variable de tabla se creó fuera de la instrucción EXEC o del procedimiento almacenado sp_executesql. Dado que solo se puede hacer referencia a las variables de tabla en su ámbito local, una instrucción EXEC y un procedimiento almacenado sp_executesql estarían fuera del ámbito de la variable de tabla.
Mejora de Rendimiento en Versiones Recientes
En las variables table no se podían crear índices de forma explícita; en estas variables table tampoco se conservaba ninguna estadística. Sin embargo, a partir de SQL Server 2014 (12.x), se introdujo una sintaxis nueva que permite crear determinados tipos de índice alineados con la definición de tabla. Con esta nueva sintaxis, puede crear índices en las variables de tabla como parte de la definición de tabla.
Adicionalmente, el nivel de compatibilidad de la base de datos 150 mejora el rendimiento de las variables de tabla con la introducción de la compilación diferida de variables de tabla. En Azure SQL Database y a partir de SQL Server 2019 (15.x), la característica de compilación diferida de variables de tabla propaga las estimaciones de cardinalidad basadas en recuentos reales de filas de variables de tabla, lo que proporciona un recuento de filas más preciso para optimizar el plan de ejecución.