SQL Server

09
Abr
2025

Firmar procedimientos almacenados con certificados

Una política de permisos mínimos para usuarios (y roles) es fundamental para la seguridad de nuestros datos. Sin embargo, a menudo nos enfrentamos al dilema de cómo hacerlo sin sacrificar funcionalidad ni exponer en exceso nuestros objetos. Como decía, la necesidad de implementar un principio de privilegios mínimos es esencial en entornos seguros y controlados, especialmente en bases de datos con múltiples aplicaciones o equipos interactuando. Una técnica muy potente y poco utilizada en SQL Server es la firma de procedimientos almacenados con certificados. Hoy vamos a explorar cómo funciona y por qué puede marcar una diferencia importante en nuestras estrategias de seguridad.

Delegación segura con certificados

Cuando se habla de conceder permisos a través de procedimientos almacenados, una de las primeras aproximaciones suele ser el uso de EXECUTE AS. Aunque funcional, esta técnica presenta inconvenientes, especialmente cuando entra en juego el “chaining” de ejecución o cuando queremos evitar tener que gestionar contextos de ejecución elevados.

Ahí es donde entran los certificados. Firmar procedimientos almacenados nos permite encapsular permisos de forma precisa y segura sin cambiar el contexto de ejecución, sin depender de impersonaciones y sin debilitar el modelo de seguridad de la base de datos.

¿Cómo funciona la firma con certificados?

El procedimiento se basa en tres pasos fundamentales: creación del certificado, firma del procedimiento y asignación del permiso requerido a un login asociado al certificado. Cada uno de estos pasos es determinante para conseguir una delegación limpia y controlada.

Partimos de una situación donde un procedimiento necesita acceder a un objeto (por ejemplo, una tabla o vista), pero el usuario que lo ejecuta no tiene permiso directo sobre ese objeto. Esto normalmente no sería un problema salvo que el procedimiento y el objeto sean de un propietario distinto o que la consulta desde el procedimiento se ejecute en otro contexto de seguridad diferente, por ejemplo por usar sp_executesql. En estos casos, si firmamos el procedimiento con un certificado y asociamos el certificado a un login o usuario que sí tenga ese permiso, el procedimiento se ejecutará con esos privilegios, pero sin necesidad de cambiar el contexto de ejecución del usuario.

Caso práctico detallado

Supongamos que queremos que ciertos usuarios puedan ejecutar un procedimiento llamado usp_ObtenerVentasPrivadas, que accede a una tabla sensible como VentasPrivadas, pero sin darles acceso directo a dicha tabla. El procedimiento sería como el siguiente:

CREATE PROCEDURE dbo.usp_ObtenerVentasPrivadas
AS
BEGIN
   SELECT * FROM dbo.VentasPrivadas;
END;
GO

Tal como está, cualquier usuario que tenga permiso de ejecución sobre usp_ObtenerVentasPrivadas podrá acceder a VentasPrivadas, pero sin tener acceso directo a ella ni posibilidad de usarla fuera de este procedimiento. Esto es porque el contexto de ejecución sigue siendo el del usuario original al no tener código dinámico y al pertenecer la tabla y el procedimiento al mismo esquema (con el mismo owner del esquema).

Cuando la cosa se complica

Pero, ¿qué pasa si cambiamos el contexto de ejecución o tenemos distinto owner? Aquí es donde entran en juego los certificados. 

Yo para la demo que os pongo lo he simplificado y ejecuto una consulta simple pero, pongamos que tenemos un procedimiento que genera SQL dinámico y lo ejecuta con sp_executesql. Este sería el ejemplo que podeis reproducir vosotros mismos.

-- ============================================
-- DEMO: Firmar procedimientos con certificado
-- ============================================

-- Crear base de datos de prueba
USE master
GO
IF DB_ID('DemoCertificados') IS NOT NULL
BEGIN
    ALTER DATABASE DemoCertificados SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DemoCertificados;
END;
GO

CREATE DATABASE DemoCertificados;
GO

USE DemoCertificados;
GO

-- ========================
-- Limpieza previa (por si ya existe algo)
-- ========================
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'UsuarioLimitado')
    DROP USER UsuarioLimitado;
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'UsuarioCertSeguridad')
    DROP USER UsuarioCertSeguridad;
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'CertSeguridad')
    DROP CERTIFICATE CertSeguridad;
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_VerSeguridad')
    DROP PROCEDURE dbo.usp_VerSeguridad;
IF OBJECT_ID('dbo.Seguridad') IS NOT NULL
    DROP TABLE dbo.Seguridad;
GO

-- ========================
-- Crear tabla de ejemplo
-- ========================
CREATE TABLE dbo.Seguridad (
   Id INT PRIMARY KEY,
   InfoPrivada NVARCHAR(100)
);

-- ========================
-- Insertar datos de ejemplo
-- ========================

INSERT INTO dbo.Seguridad (Id, InfoPrivada)
VALUES (1, 'Datos internos'), (2, 'Contenido sensible');
GO

-- ========================
-- Crear usuario sin login (para simplificar la demo)
-- ========================
CREATE USER UsuarioLimitado WITHOUT LOGIN;
GO

-- ========================
-- Crear procedimiento con SQL dinámico
-- ========================
CREATE PROCEDURE dbo.usp_VerSeguridad
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM dbo.Seguridad';
    EXEC sp_executesql @sql;
END;
GO

GRANT EXECUTE ON dbo.usp_VerSeguridad TO UsuarioLimitado;
GO

-- ========================
-- Comprobación sin firma (debería fallar)
-- ========================
PRINT '--- Prueba SIN firma (esperado: error de permisos) ---';
EXECUTE AS USER = 'UsuarioLimitado';
BEGIN TRY
    EXEC dbo.usp_VerSeguridad;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH
REVERT;
GO

-- ========================
-- Crear certificado y usuario asociado
-- ========================
CREATE CERTIFICATE CertSeguridad
   ENCRYPTION BY PASSWORD = 'UnPasswordSegurísimo!'
   WITH SUBJECT = 'Certificado para lectura controlada';
GO

CREATE USER UsuarioCertSeguridad FOR CERTIFICATE CertSeguridad;
GRANT SELECT ON dbo.Seguridad TO UsuarioCertSeguridad;
GO

-- ========================
-- Firmar el procedimiento con el certificado
-- ========================
ADD SIGNATURE TO OBJECT::dbo.usp_VerSeguridad
BY CERTIFICATE CertSeguridad
WITH PASSWORD = 'UnPasswordSegurísimo!';
GO

-- ========================
-- Comprobación con firma (debería funcionar)
-- ========================
PRINT '--- Prueba CON firma (esperado: acceso concedido) ---';
EXECUTE AS USER = 'UsuarioLimitado';
BEGIN TRY
    EXEC dbo.usp_VerSeguridad;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH
REVERT;
GO

-- ========================
-- FIN DE LA DEMO
-- ========================

Como veis, si probamos a ejecutar el procedimiento almacenado con el usuario limitado va a dar error porque no tiene permisos sobre la tabla. Sin embargo, cuando creamos un certificado, lo asociamos a un usuario que tiene permisos sobre la tabla y firmamos el procedimiento con ese usuario ya podemos ejecutar sin error el procedimiento con el usuario limitado.

Ventajas clave de usar certificados

Una de las principales ventajas frente a EXECUTE AS es que el certificado no interfiere con el contexto de ejecución. Esto significa que si dentro del procedimiento hay llamadas a otros objetos que usan permisos del usuario original, todo seguirá funcionando correctamente. Además, los certificados son inmunes a problemas comunes como la pérdida de contexto entre bases de datos, lo que resulta útil en entornos distribuidos.

Otra ventaja es la auditabilidad. Como los permisos no se conceden directamente a los usuarios finales, sino que se encapsulan dentro de procedimientos firmados, es más sencillo identificar los puntos de entrada permitidos y realizar auditorías.

También se evita el problema clásico de los permisos residuales. Si un usuario necesita ejecutar varios procedimientos que requieren distintos permisos, no es necesario concederle un permiso amplio ni crear roles intermedios complejos. Firmamos cada procedimiento con los permisos justos que requiere, y el acceso queda perfectamente delimitado.

Consideraciones de los certificados a tener en cuenta

La firma de procedimientos con certificados no está exenta de ciertas limitaciones. Para empezar, no podemos firmar procedimientos encriptados ni CLR. Además, si un procedimiento se modifica, se pierde la firma y hay que volver a aplicarla.

En cuanto a la gestión de certificados, conviene centralizar su creación y almacenamiento de forma segura. El uso de contraseñas fuertes y una política clara de mantenimiento y renovación de certificados es fundamental para evitar riesgos.

Por último, es importante evitar el uso excesivo de esta técnica, especialmente si se convierte en la única forma de delegación. En bases de datos muy grandes o con cientos de procedimientos, puede ser más conveniente crear roles bien definidos y controlar el acceso de forma más clásica. La clave está en encontrar el equilibrio adecuado.

Comportamiento de DENY

Otra consideración muy importante que debemos tener en cuenta es que el uso de DENY no puede ser sobrepasado por una firma de certificado. Si el usuario que ejecuta el procedimiento tiene un DENY explícito sobre el objeto que se consulta dentro del procedimiento, nada podrá permitirle el acceso, por mucho que el certificado tenga ese GRANT.

Esto tiene dos consecuencias prácticas. La primera es que si queremos controlar el acceso mediante firmas, no debemos usar DENY. Sencillamente basta con no conceder permisos. Por otro lado, si existe un DENY, servirá como veto absoluto, incluso para accesos indirectos a través de procedimientos firmados.

Conclusión

Firmar procedimientos almacenados con certificados es una herramienta extremadamente útil y robusta para aplicar el principio de privilegios mínimos de forma segura y mantenible. Nos permite conceder permisos de forma encapsulada, sin necesidad de impersonaciones ni contextos elevados, y facilita una delegación precisa del acceso.

Es una técnica que deberíamos tener siempre en nuestro arsenal cuando diseñamos la arquitectura de seguridad de una base de datos SQL Server. En combinación con otras prácticas como el uso de roles, vistas seguras y auditoría de permisos, puede contribuir a sistemas mucho más sólidos, trazables y mantenibles.

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
04
Abr
2025

Código dinámico con seguridad en SQL Server

En muchas ocasiones nos enfrentamos a escenarios donde necesitamos construir sentencias SQL de forma dinámica. Ya sea para crear filtros condicionales, construir cláusulas ORDER BY en tiempo de ejecución o ejecutar consultas sobre distintos objetos, la generación de SQL dinámico parece una solución sencilla y flexible. Pero esta potencia viene acompañada de riesgos, especialmente desde el punto de vista de la seguridad.

A lo largo de los años, he visto cómo el uso descuidado del SQL dinámico ha sido uno de los vectores de ataques de inyección SQL (SQLi) más comunes, y aún sigue siéndolo. Por eso, en este artículo vamos a repasar cómo generar SQL dinámico en SQL Server de forma segura, analizando técnicas recomendadas y errores frecuentes, con ejemplos claros y aplicables.

¿Cuándo usamos SQL dinámico?

Los escenarios más frecuentes en los que aparece la necesidad de SQL dinámico suelen estar relacionados con filtros condicionales, búsquedas avanzadas, generación de informes personalizables, lógica multi-tenant o incluso mantenimiento automatizado.

Un ejemplo muy habitual es una búsqueda con varios filtros opcionales. Supongamos una aplicación que consulta una tabla de personas donde el usuario puede buscar por nombre, ciudad y país. Si tratamos de resolver esto con un procedimiento estándar, el número de combinaciones posibles puede crecer exponencialmente. El SQL dinámico permite construir la sentencia ajustada a los filtros que el usuario haya proporcionado.

El problema del SQL Injection

El gran riesgo del SQL dinámico mal implementado es el conocido SQL Injection, una técnica con la que un atacante puede alterar la consulta ejecutada para acceder o modificar datos sin autorización. Esto ocurre cuando concatenamos directamente valores dentro de la cadena SQL. Veamos un ejemplo inseguro:

DECLARE @city NVARCHAR(50) = 'Madrid'
DECLARE @sql NVARCHAR(MAX)

SET @sql = 'SELECT FirstName, LastName FROM Person.Person WHERE City = ''' + @city + ''''

EXEC (@sql)

Si @city proviene de un parámetro externo (una app, una web), el usuario podría inyectar algo como: “Madrid’; DROP TABLE Person.Person;” y provocar un desastre.

Este patrón, por desgracia, sigue viéndose demasiado a menudo en aplicaciones heredadas o mal diseñadas.

Uso seguro de SQL dinámico con sp_executesql

La solución más eficaz ante este problema es usar sp_executesql, que permite construir consultas dinámicas pero separando el código de los datos mediante parámetros tipados. Esto bloquea cualquier intento de inyección porque el valor del parámetro no se interpreta como código. Reescribamos el ejemplo anterior de forma segura:

DECLARE @sql NVARCHAR(MAX)
DECLARE @paramDef NVARCHAR(MAX)
DECLARE @city NVARCHAR(50) = 'Madrid'

SET @sql = N'SELECT FirstName, LastName FROM Person.Person WHERE City = @city'
SET @paramDef = N'@city NVARCHAR(50)'

EXEC sp_executesql @sql, @paramDef, @city = @city

Aquí, aunque el usuario intentase inyectar código en @city, no lo conseguiría. SQL Server lo tratará como un valor, no como una parte de la instrucción.

Además, esta técnica también permite la reutilización de planes de ejecución, lo que supone una ventaja adicional en términos de rendimiento.

Código dinámico con filtros condicionales

Un paso más allá es cuando necesitamos construir dinámicamente múltiples filtros. En estos casos, lo ideal es ir concatenando las condiciones SQL pero parametrizando todos los valores. Veamos un ejemplo más completo:

DECLARE @sql NVARCHAR(MAX) = N'SELECT FirstName, LastName FROM Person.Person WHERE 1=1'
DECLARE @paramDef NVARCHAR(MAX) = N''
DECLARE @city NVARCHAR(50) = 'Madrid'
DECLARE @country NVARCHAR(50) = NULL

IF @city IS NOT NULL
BEGIN
    SET @sql += N' AND City = @city'
    SET @paramDef += N'@city NVARCHAR(50),'
END

IF @country IS NOT NULL
BEGIN
    SET @sql += N' AND CountryRegionCode = @country'
    SET @paramDef += N'@country NVARCHAR(50),'
END

-- Elimina la coma final de los parámetros
SET @paramDef = LEFT(@paramDef, LEN(@paramDef) - 1)
EXEC sp_executesql @sql, @paramDef, @city = @city, @country = @country

De este modo, solo se añaden los filtros que tengan valor, pero todos los valores siguen protegidos mediante parámetros.

Identificadores dinámicos: el caso más delicado

sp_executesql no permite parametrizar nombres de columnas o tablas. Esto es especialmente importante si necesitamos cambiar el objeto sobre el que se ejecuta la consulta. En estos casos debemos concatenar el identificador, pero asegurándonos de que el valor es válido. La función QUOTENAME es clave para evitar inyecciones sobre identificadores ya que introduce el nombre del objeto entre corchetes [].

DECLARE @tableName SYSNAME = 'SalesOrderHeader'
DECLARE @sql NVARCHAR(MAX)

SET @sql = 'SELECT TOP 10 * FROM ' + QUOTENAME(@tableName)
EXEC (@sql)

Aquí estamos asumiendo que el nombre de la tabla ha sido validado previamente. Aún así, QUOTENAME evita que un valor como SalesOrderHeader; DROP TABLE x;– pueda hacer daño. 

En entornos multi-tenant esto es especialmente útil si cada cliente tiene su propia tabla (modelo database-per-tenant) y accedemos a ellas dinámicamente.

Consejos adicionales para SQL dinámico seguro

Cuando usamos SQL dinámico en entornos críticos o expuestos a usuarios externos, es fundamental aplicar otras prácticas complementarias como encapsular en procedimientos almacenados ya que así reducimos la exposición del motor y permitimos auditar más fácilmente. Otra buena práctica es registrar las consultas generadas, esto es especialmente útil para soporte, auditoría y detección de patrones de abuso. Otro paso obligatorio, para mi es evitar privilegios excesivos; el usuario que ejecuta el código dinámico no debe tener más permisos de los necesarios.

Por último, si queremos ir un paso más allá, podemos aplicar SET FMTONLY OFF y otras opciones de seguridad sobre todo si trabajamos con herramientas de terceros. De esta manera podremos asegurarnos de que el motor de base de datos ejecuta todo el bloque tal cual, sin modificar el flujo por culpa del modo de metadatos.

Conclusión

El SQL dinámico en SQL Server puede ser tan útil como peligroso. En nuestras manos está la diferencia entre construir una solución flexible y robusta o abrir una puerta trasera a posibles ataques.

La clave está en nunca concatenar valores directamente y utilizar sp_executesql con parámetros siempre que sea posible. Cuando se trabaja con nombres de objetos, debemos validar y proteger con QUOTENAME. Y si el contexto lo permite, encapsular toda la lógica dentro de procedimientos controlados.

Estas técnicas yo las aplico habitualmente en proyectos reales, y son parte esencial de una arquitectura segura, especialmente en entornos donde la escalabilidad o la “multi-tenencia” requieren cierta flexibilidad a nivel de metadatos. 

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
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