Teoría BBDD

14
Abr
2025

Tablas temporales vs. variables de tabla en SQL Server: diferencias que sí importan

Las tablas temporales y las variables de tabla en SQL Server son como los destornilladores y las llaves inglesas de nuestra caja de herramientas: parecidas en concepto, pero con propósitos, comportamientos y peculiaridades bien distintos. Ambos mecanismos nos permiten trabajar con conjuntos de datos intermedios sin necesidad de recurrir a tablas permanentes, pero quien haya intentado usarlos indistintamente en entornos reales sabe que las diferencias importan. Y mucho.

Vamos a destripar ambas opciones con calma, ver cómo se comportan, qué ventajas ofrecen y en qué situaciones conviene (o no) usarlas. Porque sí, aunque parezcan intercambiables, tratarlas como tal suele acabar en consultas lentas, planes de ejecución misteriosos y DBA rezando en voz baja.

Tablas temporales: las inquilinas del tempdb

Las tablas temporales (#TablasTemporales) son lo más parecido a una tabla normal que podemos crear en tiempo de ejecución sin dejar huella permanente. Se crean en la base de datos tempdb, y existen mientras dure la sesión (o el scope) que las creó. Podemos definir índices, claves primarias, restricciones, estadísticas… Vamos, que son tablas de verdad, aunque vivan en alquiler. Su sintaxis es familiar y directa:

CREATE TABLE #ClientesTemp (
    Id INT PRIMARY KEY,
    Nombre NVARCHAR(100),
    FechaAlta DATETIME
);

O incluso más común aún:

SELECT *
INTO #VentasTemp
FROM Ventas
WHERE Fecha >= '2025-01-01';

El plan de ejecución que las acompaña suele ser robusto, especialmente si trabajamos con cantidades de datos considerables. SQL Server genera estadísticas automáticas sobre las columnas, lo cual permite un plan más ajustado al volumen real. Esto, que parece un detalle técnico sin importancia, marca la diferencia entre una consulta que vuela y otra que arrastra los pies como yo un lunes por la mañana antes del primer café.

¿Inconvenientes? Algunos. El uso de tempdb implica competencia con otros procesos que también están abusando del mismo recurso. Además, su ciclo de vida depende del contexto: si las creamos en un procedimiento almacenado y lo llamamos varias veces, conviene borrarlas explícitamente con DROP TABLE o usar IF OBJECT_ID(…) IS NOT NULL.

Y por supuesto, cuidado con el scope: una tabla temporal creada dentro de un procedimiento no es accesible desde fuera. Pero eso no debería sorprendernos. Tampoco esperamos que un DECLARE de una variable sobreviva al final del procedimiento.

Variables de tabla: pequeñas, rápidas… y caprichosas

Las variables de tabla (@VariablesDeTabla) se introdujeron como una forma rápida y elegante de manejar pequeños conjuntos de datos sin el overhead de una tabla temporal completa. Son ideales para almacenar unas cuantas filas, iterar lógicamente sobre ellas o devolver resultados simples.

La sintaxis es limpia:

DECLARE @Productos TABLE (
    Id INT,
    Nombre NVARCHAR(50)
);

Y su ciclo de vida es exactamente el del bloque donde se declaran. No hay que preocuparse por borrarlas ni por interferencias externas. Hasta aquí todo bien.

Ahora viene el problema: SQL Server no genera estadísticas sobre variables de tabla. Nunca. Ni en 2008 ni en 2022. Esto significa que el optimizador trabaja a ciegas. Literalmente: asume que una variable de tabla tiene una única fila. Da igual si tiene 1 o 10.000. El plan de ejecución será el de una tabla de una fila. Y eso, amigos, rara vez termina bien.

¿Hay excepciones? Desde SQL Server 2019, con OPTION (RECOMPILE), el optimizador puede estimar el número real de filas en algunos casos. Pero es una tirita en una fractura abierta. A veces ayuda, otras no. Y seguir usándolas a ciegas es una receta para la frustración.

¿Entonces son inútiles? No, ni mucho menos. Funcionan de maravilla cuando el número de filas es pequeño (menos de 100 suele ser seguro) y cuando las operaciones son simples. Pero si metemos un JOIN, un GROUP BY o empezamos a empujar lógica compleja… mejor sacar la artillería de verdad: tabla temporal.

Tablas temorales vs Variables de tabla: lo que no te dice la documentación

Hablemos claro. Las diferencias no están solo en la sintaxis o el ámbito. Lo importante es cómo se comportan bajo carga, cómo afectan al plan de ejecución y qué tipo de mantenimiento requieren. Para compararlas vamos a ver uno a uno los aspectos más interesantes.

Estadísticas

Las tablas temporales sí generan estadísticas; las variables de tabla no. Esto significa que las temporales permiten planes de ejecución más óptimos en escenarios con muchos datos. Las variables, no.

Soporte de índices

Ambas opciones permiten claves primarias y restricciones únicas. Desde SQL Server 2014 es posible definir índices secundarios en variables de tabla, pero solo dentro de la declaración y con sintaxis limitada. En tablas temporales podemos crear cualquier tipo de índice, incluidos los columnstore, sin restricciones adicionales.

Transacciones

Las variables de tabla no se ven afectadas por ROLLBACK. Si algo falla, su contenido sigue ahí, lo cual puede ser bueno… o un bug encubierto. Las tablas temporales, en cambio, participan en las transacciones como cualquier otra tabla.

Almacenamiento y persistencia

Ambas opciones viven en tempdb, aunque las variables lo hagan de forma menos visible. Pero a nivel físico, no hay magia: no están «en memoria», como algunos aún creen. Eso sí, las temporales suelen dejar más rastro en el sistema de archivos si no se gestionan bien.

Variables-tabla-2

Además, como ya hemos comentado las tablas temporales duran lo que dura la sesión, es decir, mientras no las borremos o cerremos esa sesión seguirán ahí. Las variables tipo tabla, por el contrario duran lo que dura la ejecución del lote (batch).

Variables-tabla-1

Si veis en la imagen, la segunda consulta no encuentra nada. Esto pasa porque después del “GO” ya se considera otro lote.

Paralelismo

Las tablas temporales pueden beneficiarse del paralelismo en las consultas; las variables de tabla, salvo casos contados y versiones muy recientes de SQL Server, no.

Lectura y escritura

En escenarios de alto volumen, las operaciones sobre variables de tabla pueden ser considerablemente más lentas que sobre tablas temporales. Aunque el coste del DECLARE parezca nulo, el impacto acumulado en los planes de ejecución mal optimizados se paga caro.

Casos de uso, ¿Cuándo elegir tablas temporales o variables de tabla?

Una variable de tabla nos viene de perlas cuando queremos devolver una pequeña tabla desde una función, cuando estamos en mitad de un script complejo que necesita guardar una docena de valores intermedios, o cuando buscamos claridad sin sacrificar rendimiento (porque sabemos que los datos son pocos y controlados). Por norma general, no deberíamos usarlas para más de 100 registros.

Una tabla temporal brilla en todo lo demás: cargas intermedias, transformaciones complejas, conjuntos de datos que van a vivir varias etapas, o cuando necesitamos analizar y refinar el rendimiento de una consulta. Incluso para esas subconsultas que usamos varias veces en una misma consulta y pueden llegar a ser pesadas.

También conviene recordar que hay un tercer actor en esta historia: las tablas temporales globales (##TablaGlobal) y las tablas de memoria (MEMORY_OPTIMIZED). Pero eso ya es otro capítulo. O varios.

Conclusión

Elegir entre una tabla temporal y una variable de tabla no debería depender del estado de ánimo, sino del uso que le vamos a dar. Si los datos son escasos, la lógica es sencilla y no necesitamos estadísticas ni índices complejos, la variable funciona. Pero si hay que unirse a otras tablas, mover volumen o exprimir rendimiento, la tabla temporal es la opción profesional.

No olvidemos que el optimizador de SQL Server toma decisiones basadas en lo que sabe. Y con una variable de tabla, lo que sabe es poco. Si le damos una tabla temporal bien definida, con índices y estadísticas, puede hacer su trabajo. Si le damos una caja cerrada con un «ya te apañarás», no esperemos milagros.

Así que, la próxima vez que tengamos que elegir entre DECLARE @Tabla y CREATE TABLE #Temp, pensemos dos veces. Porque sí, ambas pueden almacenar datos. Pero sólo una de ellas está preparada para aguantar una jornada completa sin pedir un café doble a mitad de camino. Y no, no es la variable.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 1 comentario
11
Abr
2025

Procedimientos almacenados para auditoría en SQL Server; Cuando el «sálvese quien pueda» no es estrategia

Llevamos años insistiendo en la importancia de tener auditoría en los sistemas. Y no, no hablamos de perseguir al becario por haber hecho un DELETE sin WHERE. Hablamos de una estrategia seria, sostenible y bien diseñada para dejar constancia de qué ocurre dentro de nuestras bases de datos. Y aquí es donde los procedimientos almacenados (sí, esos viejos conocidos de mil batallas) pueden jugar un papel muy digno si sabemos usarlos con cabeza.

Porque seamos honestos: muchos sistemas de auditoría empiezan con buenas intenciones y terminan convertidos en un Frankenstein lleno de triggers, jobs ocultos y tablas que nadie se atreve a truncar. Pero no tiene por qué ser así. Vamos a hablar de cómo diseñar procedimientos almacenados que nos permitan registrar operaciones relevantes, mantener la trazabilidad sin comprometer el rendimiento, y, sobre todo, que no nos obliguen a pedir perdón cada vez que ejecutamos un SELECT contra la tabla de auditoría.

¿Por qué procedimientos almacenados y no triggers o Extended Events?

Antes de lanzarnos a picar código como si no hubiera mañana, pongamos un poco de contexto. Sí, podríamos usar triggers para capturar operaciones DML. Sí, Extended Events son una maravilla para ciertas trazas. Pero aquí venimos a hablar de soluciones controladas, personalizables y que se integren de forma limpia con nuestras operaciones diarias.

Los procedimientos almacenados nos permiten centralizar la lógica de inserción, modificación y eliminación, y al mismo tiempo registrar en una tabla de auditoría todo lo que nos interesa: quién, cuándo, qué y desde dónde. Si el acceso a los datos está canalizado a través de SPs, tenemos el terreno perfecto para auditar sin traumas. ¿Que no todo pasa por SPs? Entonces el problema no es la auditoría, es el diseño general de la aplicación. Y eso es otra guerra.

Diseño de la tabla de auditoría: no empecemos la casa por el tejado

Antes de ponernos a escribir procedimientos, necesitamos tener clara nuestra tabla de auditoría. No, no vale con un INSERTED tirado en una tabla con columnas tipo Campo1, Campo2, y una nota que diga “aquí va lo que sea”. Una buena tabla de auditoría tiene estructura, contexto y propósito. Una estructura mínima (y funcional) podría ser algo así:

CREATE TABLE dbo.Auditoria
(
    IdAuditoria INT IDENTITY(1,1) PRIMARY KEY,
    TablaAfectada SYSNAME NOT NULL,
    Operacion NVARCHAR(10) NOT NULL,
    Usuario NVARCHAR(128) NOT NULL,
    Fecha DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    HostName NVARCHAR(128) NULL,
    Aplicacion NVARCHAR(128) NULL,
    ClaveRegistro NVARCHAR(512) NULL,
    Datos NVARCHAR(MAX) NULL
);

Y aquí hay que hacer una pausa. Porque sí, el campo Datos contiene JSON, pero no es de tipo JSON. ¿Por qué? Porque SQL Server, hasta la versión 2022, no soporta el tipo JSON nativo en entornos on-premises. En Azure SQL Database ya existe, y todo apunta a que SQL Server 2025 lo incorporará. Pero mientras tanto, usamos NVARCHAR(MAX).

Y sí, uso JSON. No, no me he pasado al NoSQL. Pero si queremos registrar los datos afectados sin necesidad de 50 columnas por cada tabla, el JSON nos da flexibilidad. 

No porque nos guste —aunque a muchos sí—, sino porque es lo que hay. Y además, no nos engañemos, el JSON nos da lo que necesitamos: almacenar estructuras flexibles y trabajar con ellas fácilmente gracias a funciones como JSON_VALUE, OPENJSON o ISJSON(). En resumen: guardamos JSON como texto, pero con cabeza.

Procedimientos almacenados con lógica de auditoría: así sí

Ahora que tenemos clara la tabla, vamos a por la lógica. El patrón es simple: cada procedimiento almacenado que realice operaciones sobre los datos deberá incluir una llamada al procedimiento de auditoría, justo después de la operación. Imaginemos un SP de actualización de empleados. Este es un ejemplo limpio y funcional, luego vosotros lo complicáis lo que queráis.

CREATE PROCEDURE dbo.ActualizarEmpleado
    @IdEmpleado INT,
    @Nombre NVARCHAR(100),
    @DepartamentoId INT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Empleados
    SET Nombre = @Nombre,
        DepartamentoId = @DepartamentoId
    WHERE IdEmpleado = @IdEmpleado;

    IF @@ROWCOUNT = 0 RETURN;

    DECLARE @Datos NVARCHAR(MAX);
    DECLARE @Clave NVARCHAR(512);

    SELECT @Datos = (
        SELECT @Nombre AS Nombre, @DepartamentoId AS DepartamentoId
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );

    SET @Clave = CAST(@IdEmpleado AS NVARCHAR);

    EXEC dbo.InsertarAuditoria 
        'Empleados',
        'UPDATE',
        @Clave,
        @Datos;
END

Y el procedimiento InsertarAuditoria, que sigue siendo igual de simple:

CREATE PROCEDURE dbo.InsertarAuditoria
    @Tabla SYSNAME,
    @Operacion NVARCHAR(10),
    @Clave NVARCHAR(512),
    @Datos NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Auditoria (TablaAfectada, Operacion, Usuario, HostName, Aplicacion, ClaveRegistro, Datos)
    SELECT 
        @Tabla,
        @Operacion,
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        APP_NAME(),
        @Clave,
        @Datos;
END

Nada de magia negra, solo lógica clara, controlada y auditable en sí misma.

Cuándo montar auditoría (y cuándo no): porque registrar cada pestañeo no es auditar

No todo merece una entrada en la auditoría. Si registramos cada acceso, cada SELECT o cada ejecución de SP, acabaremos con una tabla de 2 TB que nadie se atreve a consultar y que ralentiza cada INSERT. Hay que auditar cambios de estado relevantes: inserciones, modificaciones, eliminaciones y, dependiendo del negocio, consultas que tengan implicaciones legales o de privacidad. Auditar todo “por si acaso” es tan útil como llevar paraguas en el desierto. Y casi igual de cómodo.

Versionado de datos vs auditoría: primos lejanos

Una confusión habitual es pensar que la auditoría y el versionado son lo mismo. Spoiler: no lo son. Auditar es dejar constancia de qué ocurrió, quién lo hizo y cómo. Versionar es guardar el estado completo anterior de un registro, por si hay que volver atrás o comparar. Podemos combinar ambos enfoques, pero no deberíamos mezclarlos como si fueran equivalentes.

Para el versionado tenemos técnicas distintas como el libro de contabilidad, o incluso estructuras propias si el estándar se nos queda corto. Si lo que queremos es saber quién cambió qué, la auditoría nos basta. Si queremos saber qué valores tenía antes, entonces toca sacar la artillería del versionado.

Auditoría y rendimiento: ese delicado equilibrio

Registrar operaciones añade carga. No importa lo minimalista que sea el SP de auditoría: es una operación más por cada modificación. Si nuestra base de datos gestiona 10.000 cambios por minuto, quizás tengamos que plantear estrategias adicionales, como colas (Service Broker, por ejemplo) o particionado de la tabla de auditoría.

Y no, no vale con poner la tabla en otra base de datos para “que no moleste”. Separar el almacenamiento está bien, pero si la transacción espera a que el log de la otra base escriba… seguimos teniendo cuello de botella.

Diseñar una auditoría eficiente es como diseñar un buen índice: no basta con saber que existe, hay que entender cómo afecta a todo lo demás.

Conclusión

Los procedimientos almacenados siguen siendo una herramienta poderosa para implementar auditorías en SQL Server, especialmente cuando tenemos control sobre cómo accede la aplicación a los datos. Nos permiten registrar cambios con contexto, mantener el rendimiento bajo control y evitar la opacidad de soluciones como triggers masivos o rastreos indiscriminados.

Y sí, usamos JSON. Aunque hoy lo almacenamos como NVARCHAR(MAX), es una decisión consciente, no un apaño. Cuando SQL Server soporte el tipo JSON nativamente, y lo hará porque ya lo hace en Azure, estaremos listos para aprovecharlo sin rehacer nada.

Así que no, no necesitas un monstruo de triggers ni una solución de terceros para saber qué está pasando en tu sistema. A veces, basta con un poco de disciplina, procedimientos bien pensados… y la firme convicción de que auditar no es opcional. Es supervivencia.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
07
Abr
2025

Contextos de seguridad en SQL dinámico: permisos, procedimientos y sp_executesql

En mi artículo anterior os hablé de cómo construir SQL dinámico de forma segura usando sp_executesql, y cómo evitar riesgos como el SQL Injection. Sin embargo, hay un aspecto igual de crítico que no se suele tener en cuenta: el contexto de seguridad desde el que se ejecuta el código dinámico y su impacto en los permisos.

Muchos desarrolladores se sorprenden cuando un procedimiento almacenado que funciona con SQL «normal» deja de funcionar al pasar a SQL dinámico, a pesar de tener los mismos permisos. El motivo está en cómo SQL Server maneja los permisos de ejecución implícitos y qué ocurre cuando usamos sp_executesql. En este artículo voy a tratar de explicarlo paso a paso.

Permisos implícitos al ejecutar un procedimiento almacenado

Cuando concedemos a un usuario permiso para ejecutar un procedimiento almacenado, por ejemplo:

GRANT EXECUTE ON dbo.usp_ConsultaVentas TO app_user;

Ese usuario puede ejecutar el procedimiento sin necesidad de tener permisos directos sobre las tablas internas que use dicho procedimiento. Es decir, aunque no tenga SELECT sobre Sales.SalesOrderHeader, si el procedimiento ejecuta esta consulta:

SELECT * FROM Sales.SalesOrderHeader;

… el usuario podrá obtener los datos. Esto ocurre porque el contexto de ejecución del procedimiento es el del propietario, y si el procedimiento y las tablas tienen el mismo dueño, SQL Server permite ese acceso mediante el mecanismo de ownership chaining (encadenamiento de propiedad). Este comportamiento es clave para encapsular la lógica de negocio sin exponer directamente las tablas subyacentes.

¿Qué ocurre con los permisos cuando usamos SQL dinámico?

Aquí viene la trampa. Si dentro del procedimiento usamos SQL dinámico con sp_executesql como:

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Sales.SalesOrderHeader';
EXEC sp_executesql @sql;

… entonces se rompe la cadena de propiedad, y SQL Server evalúa los permisos como si el usuario estuviera ejecutando directamente la consulta, no como si formara parte del procedimiento. En otras palabras: aunque el usuario tenga permiso de ejecución sobre el procedimiento, necesitará permisos explícitos de SELECT sobre la tabla referenciada en el SQL dinámico.

Esto puede llevar a errores difíciles de diagnosticar si no se comprende cómo funciona el contexto de seguridad.

Demostración del problema de permisos

Imaginemos este escenario:

  • Usuario app_user
  • Procedimiento dbo.usp_Informe
  • Tabla Sales.SalesOrderHeader
  • Escenario app_user tiene EXECUTE sobre usp_Informe, pero no SELECT sobre la tabla
  • Código del procedimiento:
CREATE PROCEDURE dbo.usp_Informe
AS
BEGIN
    -- Esto funcionará
    SELECT COUNT(*) FROM Sales.SalesOrderHeader;

    -- Esto lanzará error si el usuario no tiene SELECT explícito
    DECLARE @sql NVARCHAR(MAX) = 'SELECT COUNT(*) FROM Sales.SalesOrderHeader';
    EXEC sp_executesql @sql;
END

El primer SELECT se ejecuta correctamente gracias al ownership chaining. El segundo da error:

Msg 229, Level 14, State 5: The SELECT permission was denied on the object...

Cuándo se rompe y cuándo no

Para que se mantenga el ownership chaining, se deben cumplir dos condiciones:

  • Los objetos deben pertenecer al mismo propietario (normalmente dbo)
  • La consulta no debe usar SQL dinámico
  • En cuanto usamos EXEC, sp_executesql o EXECUTE AS con un contexto diferente, se interrumpe esa cadena y SQL Server valida los permisos del usuario directamente.

¿Cómo se soluciona el problema de permisos?

Existen varias estrategias según el contexto, pero las más comunes son estas:

1. Firmar el procedimiento con un certificado

Es la solución más profesional. Se firma el procedimiento con un certificado que tenga los permisos necesarios sobre las tablas, y el usuario ejecuta el procedimiento sin tener permisos directos. Requiere más trabajo, pero es la solución más segura y escalable, especialmente en entornos regulados o críticos.

2. Conceder permisos explícitos sobre las tablas

Es la opción más directa pero rompe el aislamiento que buscamos al encapsular la lógica dentro de procedimientos. Puede ser válido en entornos internos o controlados.

GRANT SELECT ON Sales.SalesOrderHeader TO app_user;

Pero en ese caso ya no protegemos las tablas detrás de la lógica del procedimiento.

3. Evitar usar SQL dinámico innecesariamente

Si el acceso a los datos no requiere construir partes dinámicas (columnas, nombres de tabla, filtros condicionales avanzados), es mejor mantener el SQL como texto plano. Así se conserva el contexto de permisos del procedimiento.

En ocasiones, usamos SQL dinámico cuando lo que realmente necesitamos es ejecutar una u otra consulta en función de un parámetro de entrada del procedimiento. En estos casos es mejor crear una lógica con IF que códigos dinámicos. 

Por ejemplo, este procedimiento con código SQL dinámico:

CREATE PROCEDURE sp_ConsultaDinamica
    @TIPO INT
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT * FROM '+
               CASE @TIPO
                   WHEN 1 THEN 'Clientes'
                   WHEN 2 THEN 'Proveedores'
                   WHEN 3 THEN 'Productos'
               END;
    
    EXEC sp_executesql @sql, N'@TIPO INT', @TIPO;
END
GO

… lo podríamos sustituir por este:

CREATE PROCEDURE sp_ConsultaDinamica
    @TIPO INT
AS
BEGIN
    IF @TIPO = 1
        SELECT * FROM Clientes;
    IF @TIPO = 2
        SELECT * FROM Proveedores;
    IF @TIPO = 3
        SELECT * FROM Productos;
END
GO

Conclusión

El uso de SQL dinámico no es solo una cuestión de sintaxis o seguridad frente a inyecciones. También tiene implicaciones directas en el modelo de permisos y seguridad de SQL Server. Es importante entender que al usar sp_executesql, el procedimiento pierde la protección que le daba la cadena de propiedad, y el motor evalúa los permisos como si se tratase de una ejecución independiente. Este comportamiento puede ser confuso si no se conoce, pero una vez lo interiorizamos, se convierte en una herramienta poderosa para diseñar arquitecturas seguras y mantenibles. Si estamos diseñando procedimientos que deben proteger las tablas subyacentes, debemos considerar firmar con certificados, controlar cuidadosamente los permisos, o bien evitar SQL dinámico cuando sea posible.  

En el artículo anterior explicamos cómo generar SQL dinámico de forma segura desde el punto de vista sintáctico y de rendimiento. Pero ahora, sabemos que también hay que hacerlo de forma segura desde el punto de vista del contexto de ejecución.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
02
Abr
2025

Orden de los registros en SQL Server cuando no usamos ORDER BY

Cuando trabajamos con SQL Server, ya seamos DBAs, analistas o desarrolladores, a menudo nos topamos con una situación que puede parecer trivial, pero que esconde una trampa para quienes no conocen el funcionamiento interno del motor de SQL Server: ¿cuál es el orden de los resultados cuando no se especifica explícitamente un ORDER BY? El otro día me lo preguntó un compañero y quería también compartirlo con vosotros. La respuesta, aunque sencilla, tiene implicaciones importantes tanto para la lógica de negocio como para el rendimiento y la consistencia de nuestras aplicaciones.

El mito del orden “natural” en SQL Server

Todos hemos oído alguna vez frases como “esta tabla siempre me devuelve los datos en orden de inserción” o “los resultados salen ordenados por la clave primaria aunque no lo indique”. Y aunque en muchas ocasiones estas afirmaciones parecen cumplirse, lo cierto es que confiar en un orden implícito es una práctica peligrosa que puede romperse en cualquier momento. SQL Server, por diseño, no garantiza ningún tipo de orden si no se especifica expresamente una cláusula ORDER BY.

El optimizador y la aleatoriedad controlada

Esto pasa porque el motor de SQL Server tiene como objetivo principal devolver el resultado correcto de la consulta en el menor tiempo posible. Esto implica que, cuando no hay una indicación explícita de orden, el optimizador tiene plena libertad para usar el plan de ejecución que considere más eficiente, incluso si eso implica devolver los datos en un orden distinto cada vez que se ejecuta la misma consulta.

La forma en que SQL Server accede a los datos —ya sea mediante un table scan, index scan, index seek, lookup o incluso los hash match— influye directamente en el orden en el que los registros son devueltos. Y como estos planes pueden variar en función de las estadísticas, la carga del sistema o incluso la edición de SQL Server, el orden final de los resultados es impredecible.

El rol de los índices: ¿orden oculto?

Es cierto que muchas veces el orden “parece” coincidir con el de un índice, en especial cuando se usa un index scan. Por ejemplo, si tenemos una tabla con una clave primaria basada en un índice clustered, es habitual que un SELECT * FROM Tabla sin ORDER BY devuelva los datos según ese índice clustered. Pero esto no es una garantía, ni una promesa del motor.

Un cambio en el plan de ejecución, una actualización estadística, o una simple alteración en el número de registros puede hacer que SQL Server decida usar otro índice, o incluso hacer un table scan, y romper ese orden «natural».

Casos prácticos: cuándo cambia el orden y por qué

Supongamos una tabla de pedidos con una clave primaria sobre OrderID. Si hacemos:

SELECT OrderID, CustomerID, OrderDate FROM Sales.Orders;

En la mayoría de las ejecuciones obtendremos los datos ordenados por OrderID. Pero si añadimos un WHERE, un JOIN, un TOP, un GROUP BY, o incluso una clausula INCLUDE en un índice, el plan de ejecución puede variar, y con ello el orden.

En una prueba con AdventureWorks, podréis observar cómo una simple adición de una condición WHERE puede provocar un cambio de plan de ejecución de un Clustered Index Scan a un NonClustered Index Seek, seguido de un Key Lookup, y con ello se alterará el orden de los resultados.

El problema de confiar en el azar

Imaginemos que una aplicación espera que los resultados vengan ordenados por fecha. La visualización puede estar funcionando correctamente durante meses, pero tras una reorganización de índices o una actualización del motor, el plan de ejecución cambia. De pronto, los datos aparecen en orden aparentemente aleatorio. El fallo no está en SQL Server, sino en haber confiado en algo que nunca fue una garantía.

A nivel de lógica de negocio, esta suposición puede tener consecuencias nefastas, especialmente en procesos de paginación, importaciones, exportaciones de datos o cálculos acumulativos.

El caso especial de las funciones TOP sin ORDER BY

Un caso particularmente peligroso es el uso de TOP(n) sin un ORDER BY. Por ejemplo:

SELECT TOP 1 FirstName, LastName FROM Person.Person;

¿El primer registro? ¿Según qué criterio? Puede que hoy obtengamos un empleado llamado «Tolomeo», mañana «Herminia» y pasado «Anacleto». El motor devolverá el primero que encuentre según el plan actual, que puede cambiar sin previo aviso. Este es uno de los errores más comunes en desarrollo, y conviene tenerlo siempre presente.

Orden en columnstore, tablas temporales y paralelismo

Cuando trabajamos con índices columnstore, la aleatoriedad del orden aún se amplifica más. Este tipo de almacenamiento columnar está optimizado para escaneos masivos, y el orden de los registros no es algo relevante desde el punto de vista del motor. Además, el uso de paralelismo, buffers intermedios y reordenamientos internos hacen que cada ejecución pueda devolver los datos en un orden distinto. 

En consultas complejas con operaciones UNION, CTE, tablas temporales o incluso operadores spool intermedios, la combinación de resultados puede alterar el orden. Una operación Merge Join, por ejemplo, puede forzar un orden intermedio, pero si se sustituye por un Hash Match debido a un cambio en las estadísticas, ese orden desaparece.

¿Y en Azure SQL?

En el caso de bases de datos en Azure, ya sea en modo SQL Database o en SQL Managed Instance, el comportamiento es exactamente el mismo. La arquitectura del servicio no cambia esta característica. De hecho, dado que el motor puede escalar dinámicamente o balancear cargas, es aún más crítico no confiar en ningún tipo de orden implícito.

Conclusión

Como práctica profesional, debemos asumir que ninguna consulta en SQL Server garantiza un orden si no usamos expresamente la cláusula ORDER BY. Esta regla es especialmente importante cuando diseñamos procedimientos almacenados, informes o integraciones que dependen del orden de los datos.

Aunque el comportamiento “parezca” consistente, no debemos basarnos en lo que ocurre hoy, sino en lo que el motor puede decidir hacer mañana. En nuestras pruebas y validaciones, es conveniente forzar el uso de ORDER BY siempre que sea necesario incluso cuando el conjunto de datos es pequeño, para asegurar que nuestras aplicaciones sean consistentes, mantenibles y previsibles. 

Pero cuidado, como suelo comentar en mis formaciones, el ORDER BY es una operación especialmente costosa, no debemos abusar de ella cuando no sea necesario tener ordenados los datos. Sin embargo, esto no quiere decir que sea solo un adorno estético para los datos, es una parte fundamental de la lógica de las consultas. Sin él, estamos a merced del optimizador, y eso nunca es buena idea.

¿Te gustaría que prepare un vídeo para youtube con pruebas para demostrar visualmente estos comportamientos? Pídemelo y lo montamos.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
31
Mar
2025

Buenas prácticas al definir procedimientos almacenados

El uso de procedimientos almacenados es una de las bases del desarrollo robusto en SQL Server. Aportan encapsulamiento, seguridad, rendimiento y reutilización del código. Pero como cualquier otro elemento persistente de base de datos, su eficacia no reside solo en lo que hacen, sino en cómo están definidos y estructurados. En este artículo repasaremos una serie de buenas prácticas que deberíamos aplicar de forma sistemática al crear procedimientos almacenados, tanto desde el punto de vista funcional como desde una perspectiva de mantenimiento y rendimiento.

Estructura básica de los procedimientos almacenados

Todo procedimiento almacenado debería comenzar por una cabecera clara, que incluya las opciones de sesión imprescindibles. Estas opciones afectan al comportamiento del objeto y quedan almacenadas junto con su definición, por lo que no deben dejarse al azar:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE dbo.ObtenerClientesActivos
    @Pais NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.Clientes
    WHERE Pais = @Pais AND Activo = 1;
END;
GO

Las opciones ANSI_NULLS y QUOTED_IDENTIFIER son obligatorias para garantizar compatibilidad con vistas indexadas, funciones deterministas y otras funcionalidades. Y SET NOCOUNT ON evita el envío innecesario de mensajes como “(x filas afectadas)”, lo cual mejora el rendimiento en operaciones masivas o dentro de cursores.

Nombres coherentes y significativos para procedimientos almacenados

Otro de los mejores consejos que podemos seguir a la hora de crear procedimientos almacenados es que los nombres deben seguir una convención clara y consistente. Lo ideal es utilizar un prefijo común, como el esquema dbo o app, y un verbo que indique claramente la acción. Evitemos prefijos de sistema como sp_, ya que están reservados para procedimientos del sistema. Cuando un procedimiento almacenado empieza por sp_ SQL Server realiza una búsqueda prioritaria en la base de datos master, lo que afecta al rendimiento y puede provocar colisiones de nombres. Si quieres que el prefijo especifique claramente que es un procedimiento almacenado yo soy más partidario de prefijos como usp_ (User Stored Procedure) pero, esto ya va en gustos. Te dejo unos ejemplos de lo que para mi serían buenos nombres:

  • dbo.usp_ObtenerClientesPorCiudad
  • app.usp_ActualizarStockProducto
  • dbo.usp_InsertarPedidoCabecera

Declara los parámetros de los procedimientos almacenados de forma explícita y validada

Todo parámetro debe tener un tipo de dato bien definido, preferiblemente sin utilizar tipos innecesariamente amplios como NVARCHAR(MAX) o INT si con TINYINT es suficiente. Además, es una buena práctica validar parámetros críticos antes de ejecutar lógica compleja:

IF @Pais IS NULL
BEGIN
    RAISERROR('El parámetro @Pais no puede ser NULL.', 16, 1);
    RETURN;
END

Esto permite fallos controlados y evita errores más adelante que sean difíciles de rastrear. Si no quieres usar RAISERROR también puedes dejar log en una tabla diseñada para este fin.

Evita la lógica excesiva en un solo procedimiento almacenado

Un error común es concentrar demasiada lógica en un solo procedimiento, lo que dificulta su mantenimiento, pruebas unitarias y reutilización. Si un procedimiento está realizando múltiples tareas (validación, inserción, actualización, notificación…), es mejor dividirlo en partes más pequeñas y orquestarlas desde uno principal.

Además, si reutilizamos lógica compartida entre procedimientos, podemos encapsularla en funciones o en procedimientos secundarios.

Utiliza transacciones de forma segura y explícita en tus procedimientos almacenados

Si un procedimiento realiza más de una operación que debe completarse como una unidad, debemos protegerla con una transacción. Pero cuidado, de nada sirve iniciar una transacción si no vamos a contar con que puede haber errores y tenemos planificado rollback cuando sea necesario.

BEGIN TRY
    BEGIN TRANSACTION;

    – Operaciones críticas
    UPDATE Stock SET Cantidad = Cantidad - @Cantidad WHERE ProductoID = @ProductoID;
    INSERT INTO LogTransacciones (...)

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK;

    RAISERROR('Error en la operación: %s', 16, 1, ERROR_MESSAGE());
END CATCH;

Esto garantiza integridad de datos y un control efectivo de los errores. Nuevamente, también puedes usar una tabla de log en vez de RAISERROR.

Documenta todos tus procedimientos almacenados

Seamos claros, para mi este punto no es opcional. Una breve cabecera que explique qué hace el procedimiento, qué parámetros recibe y qué devuelve es una inversión de tiempo que ahorra horas de análisis futuro. Especialmente en equipos grandes o proyectos duraderos, esta práctica marca una gran diferencia.

/*
Nombre: dbo.ObtenerClientesPorPais
Descripción: Devuelve todos los clientes activos de un país concreto.
Parámetros:
  @Pais (nvarchar) – Nombre del país
Autor: Roberto Carrancio - www.soydba.es
Fecha: 2025-03-31
*/

Cuidado con el uso de SELECT * en procedimientos almacenados

El uso de SELECT * dentro de código que se va a persistir puede ser cómodo al principio, pero es una mala práctica y te dará problemas en cuanto el esquema cambie. Rompe la estabilidad del contrato de salida del procedimiento y complica la integración con aplicaciones externas. En su lugar, deberíamos listar explícitamente las columnas:

SELECT ClienteID, Nombre, Email
FROM dbo.Clientes
WHERE Activo = 1;

Añade a tus procedimientos almacenados un control de errores robusto

Todo procedimiento que modifique datos debe incluir manejo de errores. Además del bloque TRY…CATCH, conviene capturar el código y mensaje del error para diagnóstico:

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

    RAISERROR('Error %d: %s', @ErrorSeverity, 1, @ErrorNumber, @ErrorMessage);
    RETURN;
END CATCH;

También puede ser útil registrar errores en una tabla de log para su análisis posterior.

Cuida el rendimiento de los procedimientos almacenados desde el diseño

Cuantas veces hemos oido eso de “Es que en desarrollo funcionaba…”. Cada vez que definimos un procedimiento, estamos estableciendo un plan de ejecución potencialmente reutilizable. Además, como son objetos que se van a persistir tenemos que tener en cuenta que los datos van a crecer en algún momento y el rendimiento tiene que seguir siendo óptimo. Aunque con datos de desarrollo o en etapas tempranas del proyecto pueda parecer que no hace falta invertir tiempo en la optimización te prometo que tu yo del futuro te agradecerá que lo hayas pensado a tiempo. Algunas recomendaciones básicas podrían ser:

  • No uses OPTION (RECOMPILE) si no es estrictamente necesario (casi nunca lo es).
  • Declara los parámetros con tipos de datos que coincidan con las columnas.
  • Evita subconsultas complejas innecesarias.
  • Usa EXISTS en lugar de COUNT(*) si solo interesa saber si hay filas.
  • Indexa correctamente las tablas que usas como origen o destino.

Pruebas, idempotencia y versionado de los procedimientos almacenados

En general, es buena práctica que los procedimientos sean idempotentes si su función lo permite (es decir, que al ejecutarse más de una vez no cambien el resultado final).

Además, es recomendable mantener versiones numeradas y fechadas en sistemas donde los procedimientos evolucionan con el tiempo, y anotar los cambios relevantes en el propio código fuente.

Conclusión

Definir procedimientos almacenados no es solo cuestión de escribir lógica T-SQL funcional. Es una disciplina que requiere previsión, consistencia y enfoque estructurado. Aplicando estas buenas prácticas, conseguimos objetos más fiables, mantenibles y adaptables a largo plazo.

Tengo en mi lista de posibles futuros artículos aspectos complementarios a este artículo como procedimientos para auditoría, patrones para operaciones masivas o generación dinámica de SQL con seguridad. Mientras tanto, si aún no lo has hecho, te invito a revisar nuestro artículo sobre opciones SET imprescindibles al crear procedimientos, un complemento perfecto para consolidar estos conceptos.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
28
Mar
2025

Opciones SET: configuraciones que transforman SQL Server

En nuestro día a día trabajando con SQL Server, a menudo escribimos consultas y procedimientos almacenados sin detenernos a pensar en el contexto de ejecución que los rodea. Sin embargo, en segundo plano, hay una serie de configuraciones que condicionan profundamente cómo se interpretan, ejecutan y optimizan nuestras instrucciones T-SQL. Me refiero a las opciones que se establecen con la instrucción SET, y que van mucho más allá de unas simples “banderas”.

En este artículo repasamos las opciones SET más relevantes, agrupándolas por su impacto funcional y destacando aquellas que no deberían faltar en ningún entorno profesional. No todas las opciones son igual de conocidas, pero su uso adecuado marca la diferencia entre un código fiable y uno plagado de inconsistencias.

¿Qué hace realmente SET en T-SQL?

La instrucción SET en T-SQL permite establecer opciones de sesión que afectan al comportamiento del motor en aspectos como comparación de valores nulos, comportamiento de las transacciones, uso de cursores, sintaxis permitida o restringida, interpretación de identificadores o precisión y formato de resultados

Estas opciones afectan a la sesión actual y, en algunos casos, se heredan en la creación de objetos como procedimientos, vistas o funciones. Por ello, es fundamental entender qué estamos configurando, especialmente en entornos donde se combinan herramientas de desarrollo, procesos automatizados y ejecución mediante SQL Server Agent.

Opciones SET más críticas para el desarrollo y despliegue

Algunas opciones son especialmente relevantes porque afectan de forma directa a la definición y comportamiento de objetos persistentes. Estas deben estar activadas siempre antes de crear procedimientos almacenados, índices filtrados, vistas indexadas o columnas calculadas indexadas.

ANSI_NULLS

Ya lo analizamos en profundidad en este artículo sobre ANSI_NULLS, pero recordamos que debe estar activado para permitir comparaciones nulas según el estándar ANSI. Obligatorio para vistas indexadas, columnas calculadas e índices filtrados.

QUOTED_IDENTIFIER

Controla si las comillas dobles se interpretan como delimitadores de identificadores. Debe estar activado para usar funcionalidades modernas como índices XML, MERGE, vistas indexadas y más. Lo explicamos en detalle en este artículo dedicado.

ANSI_PADDING

Afecta a cómo se almacenan los valores en columnas CHAR y VARCHAR, así como BINARY y VARBINARY. Su valor queda grabado en la definición de columnas y también es imprescindible al crear índices en columnas de longitud fija.

Opciones SET que afectan a la lógica de ejecución

Algunas opciones SET influyen directamente en cómo se evalúan las instrucciones T-SQL, en especial las condiciones, errores y tipos de datos.

ARITHABORT

Si está activado, provoca que una consulta se detenga ante errores de desbordamiento aritmético (como división por cero). Es obligatorio tenerlo activado para usar vistas indexadas y estadísticas precisas durante la optimización de consultas.

CONCAT_NULL_YIELDS_NULL

Controla si concatenar NULL con una cadena devuelve NULL o no. Es recomendable mantenerlo activado para seguir el comportamiento ANSI:

NUMERIC_ROUNDABORT

Si está activado, cualquier redondeo de datos DECIMAL o NUMERIC provocará un error. Por defecto está desactivado, y debe permanecer así si queremos trabajar con vistas indexadas o funciones deterministas.

Opciones SET que afectan a transacciones y control de errores

Vamos a ver ahora esas opciones SET que definen cómo se van a comportar nuestras transacciones, en especial ante errores.

XACT_ABORT

Al activarlo, cualquier error en una transacción hace que se aborte automáticamente, lo que evita estados intermedios o inconsistentes. Es especialmente útil cuando se trabaja con transacciones distribuidas o procedimientos de mantenimiento.

IMPLICIT_TRANSACTIONS

Al activarse, cada instrucción que modifica datos inicia una transacción automáticamente, que debe cerrarse de forma explícita. Aumenta el control, pero puede provocar bloqueos si se olvida un COMMIT o ROLLBACK.

Opciones SET que afectan a cursores y resultados

Otro de los grupos de opciones SET son las que afectan a cursores y a los resultados de las consultas.

CURSOR_CLOSE_ON_COMMIT

Determina si los cursores abiertos se cierran automáticamente al hacer COMMIT. Por defecto está desactivado, permitiendo que el cursor siga abierto. En general, es buena práctica mantener el control explícito de los cursores pero implica llevar cierto cuidado y acordarnos de cerrarlos cuando terminemos de trabajar con ellos.

ANSI_WARNINGS

Activa advertencias para operaciones con datos truncados, errores de tipo de datos, división por cero, etc. Debe estar activado para crear vistas indexadas y columnas calculadas.

Opciones SET de formato

Por último, vamos a ver el grupo de opciones SET que nos permiten configurar los formatos y comportamientos de los datos.

DATEFIRST, LANGUAGE, DATEFORMAT

Estas opciones controlan la interpretación de fechas y días de la semana, especialmente en funciones como DATENAME, DATEPART o expresiones con formatos ambiguos. Son críticas en sistemas multi-región o migraciones.

SET DATEFIRST 1;        -- Semana empieza en lunes
SET LANGUAGE 'Spanish'; -- Nombres en español
SET DATEFORMAT dmy;     -- Día / Mes / Año

TEXTSIZE

Controla el tamaño máximo (en bytes) de datos TEXT, NTEXT o IMAGE devueltos por una consulta. En algunos entornos, limitarlo evita retornos innecesarios de grandes volúmenes de datos binarios.

Buenas prácticas: estandarización en scripts y entornos

Para evitar inconsistencias entre entornos, sesiones o herramientas, es recomendable fijar siempre las opciones clave de forma explícita en la cabecera de scripts de despliegue, procedimientos y vistas. Un bloque típico sería este:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;

Además, en los jobs de SQL Server Agent o entornos que generan T-SQL de forma dinámica, estas configuraciones deben añadirse manualmente, ya que el valor por defecto suele ser distinto al del entorno interactivo de SSMS.

Conclusión

Las opciones SET en T-SQL no son meros ajustes cosméticos, son configuraciones que determinan cómo se comporta SQL Server en aspectos fundamentales como la lógica booleana, el almacenamiento físico de los datos, el control de errores y la compatibilidad ANSI. Conocerlas y aplicarlas correctamente es una responsabilidad crítica para cualquier desarrollador o DBA. Establecerlas de forma explícita no solo previene errores, sino que garantiza que nuestros objetos sean coherentes, portables y preparados para integrarse con las capacidades más avanzadas del motor de base de datos.

¿Quieres profundizar más en el impacto de estas opciones en vistas indexadas o columnas calculadas? Te recomendamos leer nuestros artículos sobre QUOTED_IDENTIFIER y ANSI_NULLS y ANSI_PADDING, donde exploramos con más detalle cómo afectan a la creación y uso de objetos persistentes en SQL Server.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 1 comentario
26
Mar
2025

ANSI_NULLS y ANSI_PADDING en SQL Server: configuración esencial para objetos duraderos

En el ecosistema de SQL Server, ciertas opciones de sesión pueden parecer simples banderas de configuración sin mayor trascendencia. Sin embargo, cuando hablamos de ANSI_NULLS y ANSI_PADDING, nos referimos a directivas que afectan de forma directa y permanente a la definición de objetos como procedimientos, índices, vistas o tablas. Ignorar su correcto uso puede derivar en errores sutiles, comportamientos inesperados y problemas de compatibilidad en entornos modernos. En este artículo profundizaremos en su función, implicaciones y mejores prácticas.

Introducción: más que opciones de sesión

Tanto ANSI_NULLS como ANSI_PADDING son opciones heredadas del estándar ANSI SQL y forman parte de las configuraciones que influyen en cómo SQL Server interpreta y almacena los datos. Lo relevante es que, al igual que QUOTED_IDENTIFIER, estas opciones no solo afectan a la sesión actual, sino que quedan asociadas al objeto creado o modificado en ese contexto.

Esto significa que si definimos una tabla, índice o procedimiento con una de estas opciones desactivadas, su comportamiento futuro dependerá de ese estado, aunque cambiemos la configuración más adelante. Veamos en detalle cómo afecta cada una.

ANSI_NULLS: el comportamiento de las comparaciones con NULL

Cuando ANSI_NULLS está activado, se sigue el estándar ANSI para el tratamiento de valores nulos. Esto implica que cualquier comparación de una columna con valor NULL mediante el operador = o <> devolverá FALSE, ya que los NULL no se consideran iguales ni distintos a ningún otro valor, incluido otro NULL.

Ejemplo con ANSI_NULLS ON:

SET ANSI_NULLS ON;
SELECT * FROM Empleados WHERE Departamento IS NULL; -- correcto
SELECT * FROM Empleados WHERE Departamento = NULL;  -- no devuelve resultados

En cambio, si desactivamos ANSI_NULLS, SQL Server permite que Departamento = NULL devuelva resultados, rompiendo la lógica ANSI y provocando código no portable y difícil de mantener.

Requisitos del motor

SQL Server exige que ANSI_NULLS esté activado para crear:

  • Vistas indexadas
  • Índices en columnas calculadas.
  • Índices en columnas filtradas
  • Funciones con determinismo
  • Replicación, CDC, tablas temporales o cualquier funcionalidad avanzada

Además, al igual que con QUOTED_IDENTIFIER, el estado de ANSI_NULLS se graba con los objetos persistentes como procedimientos almacenados. Y una vez definidos, no se puede cambiar este comportamiento salvo recreando el objeto.

ANSI_PADDING: control del almacenamiento de datos tipo CHAR y BINARY

Mientras que ANSI_NULLS afecta a la lógica de comparación, ANSI_PADDING determina cómo se almacenan los valores que se insertan en columnas CHAR, VARCHAR, BINARY o VARBINARY, especialmente cuando se trata de cadenas más cortas que la longitud definida en la columna.

Cuando ANSI_PADDING está activado los valores de tipo CHAR y BINARY se rellenan con espacios o ceros hasta la longitud declarada. Además los valores de tipo VARCHAR y VARBINARY se almacenan tal cual, sin relleno y los valores NULL se almacenan correctamente.

En cambio, cuando está desactivado, los valores de VARCHAR y VARBINARY se truncan al final si terminan con espacios o ceros. Las columnas CHAR y BINARY, por su parte, mantienen su comportamiento de relleno, pero los efectos secundarios en columnas NULL o con valores dinámicos pueden ser impredecibles.

Un ejemplo concreto:

SET ANSI_PADDING OFF;
CREATE TABLE TestPadding (
    Codigo VARCHAR(10) NULL
);

INSERT INTO TestPadding VALUES ('ABC     ');  -- puede truncarse a 'ABC'

Este comportamiento puede parecer inocuo, pero si más adelante se cambia a ANSI_PADDING ON, las futuras inserciones pueden almacenarse de forma distinta, provocando inconsistencias en datos, índices o comparaciones.

Impacto permanente en la definición de tablas e índices

Uno de los efectos menos conocidos de ANSI_PADDING es que su estado queda grabado en la definición de la columna. Si creamos una tabla con ANSI_PADDING OFF, incluso si lo activamos después, las columnas seguirán comportándose como si estuviera desactivado.

Podemos comprobarlo en cualquier tabla con la vista de sistema sys.columns:

SELECT name, is_ansi_padded
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.MiTabla');

Una vez definida, la única forma de modificar el comportamiento de una columna con ANSI_PADDING incorrecto es eliminarla y volver a crearla.

Prácticas recomendadas al trabajar con ANSI_NULLS y ANSI_PADDING

En proyectos de larga duración, donde la compatibilidad, la trazabilidad y el rendimiento son críticos, lo más recomendable es activar siempre ambas opciones antes de crear cualquier objeto persistente:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
GO

Esto garantiza que las definiciones serán compatibles con futuras versiones de SQL Server, con funcionalidades avanzadas como replicación o índices filtrados, y que se alinearán con el comportamiento ANSI estándar esperado por herramientas y desarrolladores.

Consideraciones adicionales en SQL Server Agent con ANSI_NULLS y ANSI_PADDING

Al igual que ocurre con QUOTED_IDENTIFIER, cuando se ejecutan scripts T-SQL desde un job de SQL Server Agent, las opciones ANSI_NULLS y ANSI_PADDING se encuentran desactivadas por defecto. Esto puede suponer un problema importante si el script crea objetos que dependen de esas configuraciones. Por tanto, es imprescindible establecerlas explícitamente al comienzo del script del paso del job:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
GO
– resto de código del job

De esta forma evitamos errores como: “Cannot create index on column because it does not allow NULL comparisons using IS NULL” o “Column cannot be part of index because ANSI_PADDING is OFF”

Conclusión

Tanto ANSI_NULLS como ANSI_PADDING son opciones fundamentales en el desarrollo y mantenimiento de bases de datos en SQL Server. Aunque puedan parecer detalles menores, su impacto es profundo y permanente en los objetos que se crean bajo su contexto.

Activarlas de forma sistemática es una buena práctica que garantiza compatibilidad, coherencia de datos y aprovechamiento de las funcionalidades modernas del motor. Como en otros aspectos del diseño de bases de datos, los pequeños detalles marcan la diferencia entre un sistema robusto y uno plagado de sorpresas silenciosas.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios