Aprendiendo SQL Server

Desbordando AVG y SUM: Cuando SQL Server se queda sin dedos para sumar

2017-08-17T10:42:00.000+02:00

Entre las múltiples funciones de agregado que nos ofrece SQL Server, nos encontramos con dos que, probablemente junto con COUNT(), son las más usadas: SUM() y AVG().

El resultado que producen ambas parece bastante obvio: SUM() devuelve la suma de valores de la columna que le indiquemos a la función, mientras que AVG() nos devolverá el valor promedio de los datos de la columna deseada.

Así pues, si tenemos una tabla de pedidos con la columna Importe, que indica el importe final de cada pedido sin decimales (tipo de datos int), si queremos saber la suma de los importes de todos los pedidos, bastará con realizar la siguiente consulta:

SELECT SUM(Importe)
FROM dbo.Pedidos;

Análogamente, si queremos conocer el valor promedio de los pedidos:

SELECT AVG(Importe)
FROM dbo.Pedidos;

El resultado devuelto por ambas será un entero (int) con la suma y el promedio respectivamente.

"Estos números son muy grandes, no sé contar tanto"

Estas funciones probablemente no nos fallarán nunca, hasta que de repente un día veamos un mensaje en rojo que nos diga:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

¡Oh, oh! ¿Qué ha ocurrido aquí? Muy sencillo: Si el resultado de la suma es más grande que el entero (int) más grande, tenemos un desbordamiento aritmético. El número es demasiado grande como para que SQL Server lo pueda devolver.

Es decir, es como si nos piden sumar 5 y 6 con los dedos de la mano: una vez pasemos de 10, nos hemos quedado sin dedos. Se nos han desbordado las manos.

¿Cuántos son 6 + 5? ¡No tengo suficientes dedos!

Podemos hacer la prueba rápidamente con el siguiente script, en el que introducimos dos pedidos con importe de 1.000.000.000 y 2.000.000.000, y tratamos de obtener la suma de los importes:

CREATE TABLE dbo.Pedidos
(
 NumeroPedido int PRIMARY KEY,
 Importe int
);

INSERT INTO dbo.Pedidos (NumeroPedido, Importe)
VALUES (1, 1000000000), (2, 2000000000);

SELECT SUM(Importe)
FROM dbo.Pedidos;

Ejecutamos el script y se genera el error anteriormente mencionado. La explicación es sencilla: la suma de los importes es 3.000.000.000, pero el valor máximo que puede representar el tipo de datos int es algo más de 2.100.000.000, así que hemos desbordado la suma.

Si ejecutamos el promedio (AVG) ocurrirá exactamente lo mismo:

SELECT AVG(Importe)
FROM dbo.Pedidos;

¿Por qué? Porque primero hay que realizar la suma de todos los importes para después dividir entre el número de elementos. Como la suma desborda el tipo de datos, SQL Server no puede seguir adelante con la operación:

(1.000.000.000 + 2.000.000.000) / 2

Todo tiene solución

En este caso, como muchas otras veces, podemos solucionar este escenario simplemente consultando la ayuda online.

Vemos en la siguiente tabla que SUM devuelve un entero (int) si el tipo de datos de entrada es tinyint (1 byte), smallint (2 bytes) o int (4 bytes), por lo que si intentamos sumar columnas de estos tipos de datos tendremos el límite citado anteriormente (exactamente 2.147.483.647)

Resultado de la expresión	Tipo de valor devuelto
tinyint	int
smallint	int
int	int
bigint	bigint

Pero si usamos un bigint como tipo de datos de la columna a sumar, el resultado será también un bigint, ampliándonos el límite hasta un valor de 2 elevado a 63 (9.223.372.036.854.775.807). Un número lo suficientemente grande como para que sea complicado de desbordar, aunque no imposible.

Así pues, sin necesidad de cambiar el tipo de datos de nuestras columnas, podemos usar un CAST para convertir los valores de la columna Importe a bigint y después realizar la operación deseada. Del siguiente modo:

SELECT SUM(CAST(Importe AS bigint))
FROM dbo.Pedidos;

SELECT AVG(CAST(Importe AS bigint))
FROM dbo.Pedidos;

¡Ahora SQL Server ya nos devuelve los valores esperados!

Usa el esquema en tus consultas o rompe tu código sin darte cuenta

2016-06-21T21:24:00.005+02:00

Hace ya algún tiempo contábamos en otra entrada de este blog la importancia que tiene nombrar correctamente a los objetos de una base de datos a la hora de realizar las consultas: siempre debemos usar el formato esquema.nombre.

Los motivos quedaban claros entonces: evitar problemas de rendimiento en la compilación de las consultas debidos al proceso de resolución de nombres y evitar errores si el esquema por defecto de un usuario es distinto a "dbo" o "NULL".

Si bien estos errores pueden ocasionar algún quebradero de cabeza, su resolución es bastante sencilla, tal y como se explica en el artículo anterior. Sin embargo, añadimos ahora un nuevo caso que podría ser más problemático, ya que podemos generar un error que pasase inadvertido, devolviendo registros incorrectos en nuestras consultas.

Cuando trabajamos con más de un esquema -cosa que deberíamos hacer no sólo por motivos de organización de nuestros objetos, sino también para aplicar permisos sobre ellos más ágilmente- es especialmente importante prestar atención en la definición de vistas y procedimientos almacenados.

En ellos, deberemos poner siempre el esquema y el nombre de los objetos a los que hagamos referencia, si no queremos exponer nuestro código a futuros problemas.

Abocados al cataclismo

Supongamos que en nuestra base de datos usamos esquemas, en concreto "dbo" y "web". En el esquema dbo tenemos dos tablas "Cliente" y "Pedido", en el esquema web añadimos ahora la vista "PedidosCliente" e insertamos algunos registros en las tablas a modo de prueba. El script completo para crear los objetos y rellenar las tablas es el siguiente:

Una vez tenemos los objetos creados, ejecutamos una simple consulta sobre la vista:

Que nos devolverá tres resultados: Dos pedidos para Juan y uno para María.

Ahora bien, imaginemos que solamente para los clientes web necesitamos guardar más información, por ejemplo su password. Como tenemos clientes tradicionales y web, decidimos añadir una segunda tabla Cliente, esta vez en el esquema web, que hará referencia a la tabla dbo.Cliente por el Codigo y extenderá la información de sus registros con la columna Password, para aquellos que vengan a través de la web. Así:

Empieza la batalla de los esquemas

A estas alturas ya podemos empezar a tener dudas sobre a qué tabla referencia nuestra vista web.PedidosCliente. Recordemos que habíamos referenciado a las tablas Pedido y Cliente sin usar el esquema en la definición de la vista. Así pues, ¿la vista seguirá usando dbo.Clientes como hasta ahora, o bien pasará a referenciar la nueva tabla web.Clientes?

Una primera comprobación que podemos hacer es, en el SQL Server Management Studio, hacer clic derecho sobre la vista y en el menú contextual seleccionar "Ver Dependencias".

SQL Server nos dice que la vista sigue dependiendo de dbo.Cliente

Parece que sigue usando la tabla dbo.Cliente. Para estar doblemente seguros, podemos consultar también la definición de la vista -igualmente con clic derecho sobre ella- y ver cómo está definida. Efectivamente, SQL Server parece estar seguro, es sobre dbo.Cliente:

SELECT P.Numero, P.FechaPedido, C.Nombre
FROM dbo.Pedido AS P INNER JOIN
dbo.Cliente AS C ON P.CodigoCliente = C.Codigo

SQL Server Management Studio no dice la verdad

A estas alturas podemos asumir que nuestra vista seguirá funcionando como si nada hubiera pasado. Para comprobarlo, ejecutemos la misma consulta del principio de esta entrada sobre la vista. Debería devolver los mismos tres resultados, ¿verdad? Veámoslo:

Msg 207, Level 16, State 1, Procedure PedidosCliente, Line 3 [Batch Start Line 44]El nombre de columna 'Nombre' no es válido.Msg 4413, Level 16, State 1, Line 46No se pudo usar la vista o función 'web.PedidosCliente' debido a errores de enlace.

¡Oh! ¡Oh! Hemos roto nuestra vista y no nos hemos dado cuenta hasta que hemos ejecutado una consulta sobre ella.

Sin embargo, esta es la buena noticia dentro de la gravedad del problema. Ha ocurrido un error y SQL Server nos lo ha notificado. Este error se produce porque la vista está definida dentro del esquema web. Al estar dentro de este esquema, pese a que el Management Studio nos insista en que la vista hace referencia a la tabla dbo.Cliente, no es así: al crear la tabla web.Cliente, la vista hace referencia a ella, porque está en su mismo esquema. Como esta tabla no tiene la columna Nombre, se produce el error.

El mismo error lo podríamos obtener si recreásemos la vista o la intentásemos modificar sin alterar la definición.

Aún puede ser peor

Sí, podría serlo. Si nuestra nueva tabla web.Cliente también tuviese un campo Nombre -imaginemos que queremos poner nombre de usuario web, pero llamamos a la columna Nombre- no se produciría ningún error. Simplemente cambiaría el conjunto de resultados devuelto, circunstancia que podría pasar inadvertida hasta que, por ejemplo, un usuario detectase la anomalía. Por ejemplo:

¡Oooooh! ¡Ooooooh! ¿Ningún resultado? ¿Qué ha pasado con nuestros tres pedidos de Juan y María? Fácil: la consulta sobre la vista ha funcionado, pero haciendo referencia a la tabla web.Cliente, donde no tenemos ningún registro. Así que la vista nos devuelve eso: ningún registro.

La solución

Es sencilla: Nombrar a los objetos por su nombre completo, que incluye esquema.nombre. Si en la definición de la vista original simplemente hubiésemos puesto dbo.Cliente y dbo.Pedido, no habría forma de haberla roto, ni con error ni inadvertidamente. Así:

Recuérdalo, llama a las cosas por su nombre, o podrás tener problemas que, por muy poco probables que parezcan, ocurrirán en el momento más inoportuno. Siempre es mejor no dejarlo en manos del azar.

Con SCHEMABINDING esto no pasaría

Efectivamente, hay otra solución. SQL Server nos permite definir nuestras vistas (y funciones) con la opción SCHEMABINDING. Esta opción hace que no se pueda cambiar la definición de los objetos a los que hace referencia la vista (las tablas Pedido y Cliente en nuestro caso). Además, tal y como explica la MSDN, se nos obliga a usar el esquema siempre cuando definimos una vista con WITH SCHEMABINDING.

Cuando se utiliza SCHEMABINDING, select_statement debe incluir los nombres de dos partes (schema.object) de las tablas, vistas o funciones definidas por el usuario a las que se hace referencia.

Por ejemplo, si intentamos definir nuestra vista web.PedidosCliente así:
Recibiremos el siguiente error:

Msg 4512, Level 16, State 3, Procedure PedidosCliente, Line 4 [Batch Start Line 27]No se puede enlazar a esquema vista 'web.PedidosCliente' porque el nombre 'Pedido' no es válido para enlazar a esquema. Los nombres deben constar de dos partes y los objetos no pueden hacer referencia a sí mismos.

UNION vs UNION ALL: Entiende las diferencias

2015-11-09T14:13:00.001+01:00

SQL Server ofrece el operador UNION para, según la MSDN, "combinar los resultados de dos o más consultas en un solo conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la unión."

Para que el motor de base de datos pueda combinar el resultado de distintas consultas, éstas deben respetar el número y orden de las columnas implicadas. Asimismo, las columnas equivalentes en posición deben serlo también compatibles en tipos de datos.

El operador UNION presenta un modificador o argumento: ALL. Éste se escribe a continuación de UNION, así: UNION ALL. Cuando se especifica, SQL Server devuelve todas las filas de todas las consultas combinadas, incluyendo posibles duplicados.

Ahí radica justamente la diferencia entre UNION y UNION ALL: El operador de unión elimina las filas duplicadas cuando combina los resultados de las consultas implicadas. Sin embargo, con el argumento ALL, los duplicados se mantienen.

¿Cuándo uso UNION y cuándo UNION ALL?

El primer criterio de selección está claro: si no nos importa que haya registro repetidos o los necesitamos, entonces debemos usar UNION ALL. Si queremos asegurarnos de que no haya registros repetidos, usaremos UNION.

¿Y qué pasa si estoy seguro de que no hay registros repetidos?

En ese caso, por normal general, será mucho más eficiente usar UNION ALL, ya que le ahorramos a SQL Server la necesidad de comprobar y descartar posibles duplicados. Así que, salvo excepciones, salvo que explícitamente queramos eliminar duplicados, será más eficiente para SQL Server el uso de UNION ALL.

Cómo averiguar el propietario (Owner) de una base de datos en SQL Server

2015-01-28T10:46:00.001+01:00

En ocasiones necesitamos conocer quién es el propietario de una base de datos. Normalmente podemos acceder a esta información a través del SQL Server Management Studio, consultando las propiedades de la base de datos.

Para ello, buscamos la base de datos que queramos consultar en el árbol de bases de datos del Object Explorer y hacemos clic derecho sobre ella. A continuación, elegimos la última opción del menú contextual que nos aparece: Propiedades. Veremos esta ventana:

Propiedades de una base de datos, que incluyen el propietario (en amarillo)

Sin embargo, en ocasiones no tenemos acceso a esta ventana de propiedades o, simplemente, queremos consultar el propietario de todas las bases de datos de un servidor. Para ello disponemos del siguiente script T-SQL, que nos proporcionará la información deseada:

La primera columna nos dirá el propietario de la base de datos (en concreto, su login o nombre de usuario). A partir de este script, podemos filtrar por base de datos, mediante el campo "name":

O podemos conocer todas las bases de datos cuyo propietario sea un login concreto, aplicando de nuevo al campo "owner_sid" la función suser_sname:

Notas del SQL Saturday Barcelona (#338): Conocimiento compartido

2014-10-27T08:58:00.000+01:00

Volvemos de Barcelona con la sensación de haber sido partícipes de una experiencia enriquecedora: el primer SQL Saturday celebrado en España ha sido todo un éxito.

La encomiable labor organizativa del grupo PASS España -encabezados por Rubén Pertusa-, la generosidad a la hora de compartir su conocimiento de los expertos venidos de diversos países para realizar sus excelentes ponencias y el entusiasmo de todos los profesionales registrados, allí reunidos durante más de 10 horas de sesiones, con un gran afán por compartir y aprender han hecho del SQL Saturday Barcelona un evento del que salimos con un bagaje positivo y muchas ganas de repetir.

Como bien expuso Fernando G. Guerrero, CEO de SolidQ -uno de los sponsors del evento-, "la curiosidad es lo que nos mueve". El apetito por aprender, las ganas de compartir experiencias y conocer a quienes también las viven son el motor de los SQL Saturday. Nada más acertado que el lema de "Share what you know, learn what you don't" (Comparte lo que sabes, aprende lo que no).

Nuestro sábado dio comienzo poco después de las ocho y media de la mañana. Tras el pertinente registro, llegaba la presentación del evento.

La primera sensación fue de sorpresa, pues el número de profesionales inscrito, dispuestos a sacrificar su tiempo libre para no perderse la cita -a centenares de kilómetros de su hogar, en muchos casos-, era elevado.

Tras la bienvenida en uno de los salones de actos del IQS (lugar donde se desarrollaría la jornada), comenzaban las sesiones. Distribuidas entre tres salas, las rutas a elegir eran claras: Business Intelligence, Big Data o DBA (y developers, añadiríamos).

Escogimos, por supuesto, la que más se ajustaba a nuestro perfil, la tercera de ellas. Comenzamos la mañana aprendiendo muchas cosas nuevas sobre SQL Server 2014, de la mano de Enrique Catalá.

No entraremos en detalles técnicos, puesto que no es el propósito de esta entrada en la bitácora, pero las Transacciones de Durabilidad Diferida enseguida captaron nuestra atención. Ya había merecido la pena el esfuerzo realizado por estar presente.

La segunda sesión, también en manos de Enrique, nos dejó con ganas de más. El tiempo destinado a explicar los planes de ejecución -que nos explican qué hace el motor de bases de datos para devolvernos lo que le pedimos- fue a todas luces escaso. Es un tema que da para muchas horas, y que conviene conocer a la perfección. Por cierto, Enrique sólo lo mencionó de pasada porque se quedó sin tiempo para más: si has de verte las caras con planes de ejecución, no dudes en usar SQL Sentry Plan Explorer.

Llegó el tiempo para el primer descanso. Un café y la primera oportunidad para conversar con compañeros de fatigas. Allí conocimos a Eduardo Burillo, alicantino él, con el que fue un placer compartir inquietudes.

Dos sesiones más antes de comer: En la primera, Sergey Olontsev nos habló de una utopía en "Cómo sacar provecho de In-Memory OLTP (Hekaton) en nuestras aplicaciones". Imposible. No se puede. Parece que el afán por competir con otras soluciones para Big Data ha provocado el parto prematuro de Hekaton. SQL Server 2014 salió mucho antes de que esta tecnología estuviese madura. Si es que algún día lo está. Habrá que estar pendientes de su evolución en próximas ediciones. Por ahora solamente alguna aplicación de nuevo cuño y con finalidad muy específica podría sacarle partido sin sufrir alguna se sus casi infinitas restricciones. Eso, y el rayo de luz final que supuso conocer la existencia de una alternativa en memoria a las tablas temporales y variables tipo tabla.

Llegaba el turno después para Davide Mauro, hablándonos de un tema en el que somos más bien profanos: "Cómo elegir el hardware adecuado para SQL Server". Apuntamos algunas cosas interesantes, como las herramientas TPC (Transaction Processing Performance Council) para banco de pruebas. Las usaremos.

Tras la hora de la comida, aún restaban tres sesiones más. Descubrimos el potencial de Power BI, de la mano Rodney Landrum como antesala a otra de las sesiones más interesantes del día, de la mano del divertido alemán Uwe Ricken. Su charla sobre lo que ocurre en las entrañas de SQL Server -esto es, a nivel de páginas de datos- cada vez que modificamos datos mediante instrucciones INSERT, UPDATE y DELETE fue realmente reveladora. No hubo tiempo para completarla, pero nos sumergió de lleno en lo que ocurre cuando ejecutamos dichas sentencias.

Finalizamos el día con la sesión más relajada del sábado. Alberto López Grande nos propuso una reflexión, desarrollada en un agradable debate colectivo, sobre el día a día del DBA, centrada en el desafío que supone mantener los servidores en las versiones más recientes para entornos productivos.

Las interesantes intervenciones de los allí presentes reflejó a la perfección el propósito y espíritu de los SQL Saturday: participar, compartir, debatir. En resumen, aprender. SQL Saturdays que, esperamos, se instauren y se conviertan en costumbre, no sólo en Barcelona, sino en cualquier ciudad donde los profesionales que trabajamos con SQL Server queramos seguir compartiendo conocimiento.

Humor y SQL Server: inyección SQL y cómo evitarla (no llames a tu hijo DROP TABLE)

2014-07-04T12:04:00.003+02:00

El portal xkcd publica esta viñeta sobre inyección de código en SQL Server:

Los peligros de llamar a tu hijo DROP TABLE

Tratado con gran sentido del humor, esta cómica situación nos pone en alerta frente a la inyección de código SQL Server, que puede suponer una vulnerabilidad seria para nuestras bases de datos.

Una aplicación que no compruebe los datos que en ella se introducen a través de aplicaciones externas está expuesta al desastre. En el cómic se introduce una instrucción DROP TABLE en medio del supuesto nombre de un niño inscrito en un colegio. Al introducir el falso nombre en el sistema informático del centro, el resultado es catastrófico: la tabla de estudiantes es eliminada por completo.

La Wikipedia presenta un ejemplo similar para demostrar en qué consiste la inyección SQL Server, así como diversas soluciones a adoptar, dependiendo del lenguaje de programación de la aplicación:

"Por ejemplo, asumiendo que el siguiente código reside en una aplicación web y que existe un parámetro "nombreUsuario" que contiene el nombre de usuario a consultar, una inyección SQL se podría provocar de la siguiente forma:

El código SQL original y vulnerable es:
consulta := "SELECT * FROM usuarios WHERE nombre = '" + nombreUsuario + "';"

Si el operador escribe un nombre, por ejemplo "Alicia", nada anormal sucederá, la aplicación generaría una sentencia SQL similar a la siguiente, que es perfectamente correcta, en donde se seleccionarían todos los registros con el nombre "Alicia" en la base de datos:

SELECT * FROM usuarios WHERE nombre = 'Alicia';

Pero si un operador malintencionado escribe como nombre de usuario a consultar:
"Alicia'; DROP TABLE usuarios; SELECT * FROM datos WHERE nombre LIKE '%"
(sin las comillas externas), se generaría la siguiente consulta SQL, (el color verde es lo que pretende el programador, el azul es el dato, y el rojo, el código SQL inyectado):

SELECT * FROM usuarios WHERE nombre = 'Alicia'; DROP TABLE usuarios; SELECT * FROM datos WHERE nombre LIKE '%DROP TABLE usuarios;SELECT * FROM datos WHERE nombre LIKE '%';

En la base de datos se ejecutaría la consulta en el orden dado, se seleccionarían todos los registros con el nombre 'Alicia', se borraría la tabla 'usuarios' y finalmente se seleccionaría toda la tabla "datos", que no debería estar disponible para los usuarios web comunes."

¡Nos vemos en el SQLSaturday Barcelona!

2014-07-03T15:55:00.003+02:00

¡Por primera vez llegan a España los famosos eventos SQLSaturday! Será en Barcelona, el próximo sábado 25 de octubre de 2014.

Ya me he registrado en el evento. Será una buena oportunidad para conocer a otros profesionales de SQL Server y compartir experiencias y conocimiento, todo ello mientras aprendemos de los mejores expertos, incluidos algunos MVP y MCM de Microsoft, en las diferentes sesiones programadas.

@jaimeml is attending SQLSaturday #338 - Barcelona 2014 #sqlsatBarcelona
— PASS SQLSaturday (@sqlsat) junio 28, 2014

SQLSaturday es un evento gratuito de un día de duración para profesionales y futuros profesionales relacionados con SQL Server, Big Data y Business Intelligence; organizado por la Asociación de Profesionales de SQL Server (PASS). Se han celebrado más de 300 eventos SQLSaturday alrededor de todo el mundo (el de Barcelona será el 338) y ésta va a ser la primera edición en España. El plazo de registro lleva abierto algunos días, en los que ya se han inscrito más de 70 asistentes.

Ya tenemos más de 70 asistentes registrados y sesiones de 13 MVPs, 3 MCMs...¡Qué acogida! #sqlsatBarcelona Regístrate http://t.co/v5ghJGvHwp
— SQL Sat Barcelona (@sqlsatbarcelona) junio 30, 2014

Tal y como afirma la web del evento, dentro de la multitud de temas que se van a tratar en las sesiones, podremos encontrar:

Motor relacional (optimización, bloqueos, índices...)
Alta disponibilidad
SQL Server 2014
Big Data (HDinsight, Pig, Hive...)
Power BI
Integration Services, Analysis Services, Reporting
Powershell
...

Habrá sesiones en inglés y en castellano. Los ponentes han ido enviando sus propuestas de sesiones, que aparecen listadas en la web del evento. En próximas fechas se publicará el calendario final del evento, para que cada asistente se programe aquéllas a las que tiene más interés en asistir.

Además, el evento favorece el networking entre profesionales de SQL Server, facilitando un lugar común en el que conocer al resto de asistentes mediante sus perfiles de LinkedId o sus cuentas de Twitter.

Haz bien tus consultas: Llama a las cosas por su nombre (y esquema)

2014-05-23T16:06:00.002+02:00

La MSDN nos explica cual es la forma correcta de referenciar a los objetos en tus consultas, y es la siguiente:

Esquema.Nombre

¿Por qué? Hay diversos motivos y, aunque parezca algo trivial, pueden evitarnos acceder a objetos incorrectos, errores en consultas para usuarios de la base de datos distintos al que creó el objeto e incluso problemas de rendimiento.

Resolución de nombres

Cuando ejecutamos una consulta, el servidor SQL realiza un proceso llamado resolución de nombres (name resolution, en inglés) para identificar los objetos referidos en ella.

Si identificamos cada objeto por su nombre completo (fully qualified name), SQL sabrá determinar inmediatamente a qué objeto nos referimos y si existe o no. Por el contrario, si solamente especificamos su nombre, el motor de bases de datos tendrá que determinar a qué objeto nos referimos. Para ello usará el default schema del usuario conectado.

Esquema por defecto o Default schema

Cada usuario de una base de datos puede tener un esquema por defecto (desde SQL Server 2008 R2, también los grupos de Windows. Esto es importante, como veremos a continuación). Si para un usuario no está definido el esquema por defecto, se asume que éste es "dbo".

Para comprobar el esquema por defecto que tienen los usuarios de nuestra base de datos, podemos ejecutar la siguiente consulta:

Cuando SQL tiene que resolver un nombre de objeto sin esquema, primero busca en el esquema por defecto del usuario actual. Si no lo encuentra ahí, entonces lo buscará en el esquema dbo. Si tampoco estuviera ahí, la consulta fallaría.

Es conveniente que el esquema por defecto de todos los usuarios y grupos sea dbo o no esté definido (NULL), salvo que haya razones muy concretas para que no sea así.

Cuando el esquema por defecto de un usuario es otro, se dan los problemas descritos a continuación.

Problemas relacionados con el esquema por defecto

Si un usuario tiene un esquema por defecto que no es dbo, cuando cree nuevos objetos en la base de datos, estos se llamarán esquema_usuario.nombre_objeto. Si cualquier otro usuario con un esquema por defecto distinto hace una consulta sobre dicho objeto sin especificar su esquema, la consulta fallará.
Si se crea un objeto con el esquema dbo, pero un usuario con el esquema por defecto distinto de dbo hace una consulta se hace sin especificar el esquema, SQL buscará el objeto primero en su esquema por defecto y después en dbo (name resolution). Esto puede producir problemas de rendimiento. Si bien estos problemas no serían dramáticos, se han medido que entre el 10% y el 15% del proceso de ejecución de una consulta puede ser debido a la resolución de nombres.

Los esquemas existen desde SQL Server 2005 y ofrecen multitud de posibilidades de organización y control de seguridad sobre nuestros objetos. Sin embargo, aunque no los uses y todos tus objetos estén en el esquema dbo, que es el por defecto, es posible que el esquema por defecto de alguno de tus usuarios no sea dbo.

Para comprobar qué usuarios no tienen por defecto el esquema "dbo", puedes ejecutar el siguiente script:

La MSDN explica muy bien todo este proceso en su sección "How to Refer to Objects" (como referirse a objetos) del artículo -en inglés- "SQL Server Best Practices - Implementation of Database Object" (Prácticas recomendadas en SQL Server - Implementación de Objetos de Base de Datos):

Es siempre una buena práctica referirse a los objetos de la base de datos por el nombre de esquema y el nombre de objeto separados por un punto.
...
El proceso [de resolución de nombres] puede mejorarse usando el nombre completo o configurando correctamente el esquema por defecto del usuario, eliminando un proceso innecesario de comprobación de propiedad. Esto puede mejorar considerablemente el rendimiento en sistemas utilizados intensivamente.

Problema adicional con grupos de usuarios de Windows en versiones anteriores a SQL Server 2012

Si asignas permisos a tus bases de datos a grupos de usuarios de Windows en versiones de SQL Server anteriores a 2012, cuando los usuarios de dichos grupos accedan a la base de datos, SQL Server les creará un usuario cuyo esquema por defecto será su login de Windows.

Esto hace que el problema descrito de resolución de nombres se agrave especialmente en este tipo de entornos con versiones de SQL Server 2005 o 2008, en los que el administrador de bases de datos gestione los permisos de esta manera.

Por ejemplo: Si el usuario MIDOMINIO\Usuario1 pertenece al grupo MIDOMINIO\Grupo1 y el Usuario1 se conecte por primera vez a una base de datos para la que tenga permisos asignados a través del Grupo1, SQL Server le asignará como esquema por defecto el valor "MIDOMINIO\Usuario1".

A partir de este momento, cada objeto que cree el Usuario1 sin definir el esquema correctamente se llamará de la forma:

MIDOMINIO\Usuario1.NombreObjeto.

Y todas las consultas que haga el usuario sin especificar el esquema en los nombres de objeto penalizarán el rendimiento de SQL Server. Por ejemplo:

Además, si otros usuarios realizan esta misma consulta, fallará, ya que SQL Server no será capaz de encontrar el objeto si no se especifica el nombre de esquema.

Conclusiones

Si eres DBA: Asegúrate que los usuarios de tus bases de datos tengan asignado como esquema por defecto dbo o NULL.
Si eres programador: Escribe todas tus consultas haciendo referencia a los objetos por su nombre completo, de la forma nombre_de_esquema.nombre_de_objeto

Hacer trazas en SQL Server Express

2014-04-14T13:00:00.000+02:00

Si necesitas diagnosticar problemas de rendimiento, encontrar consultas conflictivas o simplemente quieras trazar las consultas que hace una aplicación contra la base de datos, seguramente estés acostumbrado a usar el SQL Server Profiler, la herramienta que principalmente hemos utilizado para realizar este tipo de diagnósticos sobre nuestras bases de datos y servidores. También es posible que te hayas decidido a dar el paso a Extended Events, pero esa es otra cuestión.

Si el Profiler es tu herramienta, muy seguramente la eches de menos en el caso de que el diagnóstico tengas que hacerlo sobre un SQL Server Express. La edición gratuita del motor de bases de datos relacionales de Microsoft no incluye esta aplicación. Es un contratiempo, pero hay alternativas. A continuación, os las presentamos:

ExpressProfiler

ExpressProfiler es un ejecutable, gratuito, de código abierto, anteriormente conocido como SqlExpress Profiler. Presenta una interfaz muy sencilla en un ejecutable que solamente tendremos que descargar y ejecutar, sin instalaciones. Simplemente tendremos que darle al botón de play y monitorizar la traza para ver qué consultas se están ejecutando.

El ExpressProfiler v2.0, en acción

Trace Flags o Marcas de Seguimiento

Existen en SQL Server una serie de características que pueden ser configuradas de manera temporal y dinámica, activando y desactivando lo que se conoce como trace flags o marcas de seguimiento, en castellano. Tal y como explica la MSDN, las marcas de seguimiento se suelen utilizar para diagnosticar problemas de rendimiento o para depurar procedimientos almacenados o sistemas complejos.

Pues bien, dos de ellas nos sirven para nuestro propósito, tal y como se explica en este artículo de CodeProject, escrito por BillyBoatGruff en febrero de 2012.

Activando la marca 4032, todas las consultas ejecutadas sobre SQL Server se trazan. El mecanismo habitual para activar una traza es mediante la instrucción DBCC TRACEON:

Sin embargo, si intentamos activar esta traza, obtendremos el siguiente mensaje:

"Se omitirá la marca de seguimiento 4032. Se trata de una marca de seguimiento no válida o que solo se puede especificar durante el inicio del servidor."

Tal y como explica el mensaje, esta traza ha de especificarse durante el inicio del servidor. Para ello ejecutaremos la siguiente instrucción desde una línea de comandos que hayamos ejecutado como administrador:

Para poder ejecutar la instrucción, el servidor deberá estar detenido. Si no lo está, podemos pararlo desde la misma línea de comandos con la instrucción:

Una vez hecho esto, volcaremos las consultas al log de errores de SQL Server para que podamos consultarla, activando la traza 3605:

Con estas dos trazas activas, podremos revisar en el log de nuestro SQL Server todas las consultas realizadas sobre el servidor. El log permite realizar filtrados y exportar su listado a un fichero de texto, desde donde podremos copiar las consultas.

Hay que tener en cuenta que para poder utilizar este mecanismo, es necesario ser administrador tanto del servidor de bases de datos como del equipo en el que esté instalado.

Para deshabilitar las trazas, una vez hayamos acabado con las tareas de diagnóstico, ejecuta:

Heaps en SQL Server: tablas sin índice clustered y sus consecuencias

2014-03-14T12:12:00.004+01:00

Definición

En SQL Server las tablas pueden tener o no tener un índice clustered. El índice clustered es por el que SQL ordena físicamente los datos (las filas de las tablas) en disco.

El índice clustered le sirve a SQL para buscar, ordenar y agrupar registros de manera eficiente. Así, no tener un índice clustered en una tabla puede llevarnos a problemas de rendimiento. Cuando una tabla no tiene índice clustered, se llama heap.

El problema

Los heaps mantienen los registros en las páginas de datos en el mismo orden en el que se han insertado. Esto hace que los heaps resulten más rápidos a la hora de realizar un INSERT, ya que no han de insertarse en una posición en concreto, sino directamente a continuación del último registro existente para la tabla en cuestión.

Sin embargo, cualquier otra operación que requiera un orden en los datos será más lenta. Esto se aplica a SELECT, DELETE y UPDATE, salvo que se quieran efectuar estas operaciones sobre la tabla completa.

Lo que la MSDN nos dice de los heaps es que:

A veces, hay buenos motivos para dejar una tabla como heap en lugar de crear un índice clustered, pero para usar los heaps de forma eficaz se requieren conocimientos avanzados. La mayoría de las tablas deben tener un índice clustered cuidadosamente elegido.
Si una tabla es un heap y no tiene ningún índice no clustered, debe examinarse la tabla completa (table scan) cuando se busca una fila.

Es decir, en general los heaps no son aceptables. Solamente bajo ciertos escenarios muy estudiados para los que una ordenación de los datos no es necesaria y un mantenimiento de índices por parte de SQL implicaría un sobrecoste innecesario (por ejemplo, una tabla con una lista de provincias de España, para la que siempre se devuelve la lista completa y nunca se busca información para un registro concreto).

En cualquier otro caso, cualquier consulta sobre una tabla sin índice clustered obligará a SQL Server a recorrer la tabla entera en busca del registro o registros solicitados. Evidentemente, cuanto mayor sea la tabla, mayor será el problema de rendimiento que esta situación puede generar.

Cómo detectarlo

El script a continuación ayuda a detectar heaps en nuestras bases de datos:

SELECT OBJECT_NAME(i.object_id) AS TableName, p.rows

FROM sys.indexes i

INNER JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id

WHERE i.index_id = 0

ORDER BY p.rows DESC

La solución

En general, con cada heap hay que pararse y determinar cuál debería ser su índice clustered. El candidato más idóneo para ser índice clustered suele ser siempre la clave primaria, aunque existen excepciones. Si la tabla sólo tiene clave primaria, ésta debe ser clustered siempre. El resto de los casos podemos estudiarlo, aunque existen algunas directrices:

Suelen ser los campos que usamos para hacer búsquedas y joins sobre la tabla.
Suelen ser los campos por los que se referencia a la tabla desde otras tablas.
Hay que intentar que el índice clustered de una tabla tenga el mínimo tamaño posible.

Todo esto señala, como decíamos, a la clave primaria de la tabla. Pero, en ocasiones, creamos claves alternativas a la primaria que se ajustan mejor a esta definición.

Referencias:

Tables without clustered indexes, por Basit Farooq. Basit nos ofrece scripts alternativos para detectar heaps basados en su utilización
Blitz Results: Tables without clustered indexes, por Brent Ozar.

Tipos de datos por defecto en SQL Server

2014-03-12T16:26:00.001+01:00

En ocasiones necesitamos que nuestras consultas devuelvan valores constantes. Por ejemplo, de manera muy sencilla, podemos tener una consulta como la siguiente:

Si necesitamos recuperar los valores devueltos por la consulta en un recordset de ADO.NET, por ejemplo, ¿qué tipo de datos debemos esperar de columnas de este tipo? La respuesta: depende.

Depende del tipo de constante que hayamos introducido. Si se trata de un número que no desborde los 4 bytes de capacidad de un entero (int), como en el caso del 1 de nuestra consulta anterior, éste será el tipo de datos de dicha constante.

Si escribimos la constante entre comillas simples, SQL lo tratará como un varchar del tamaño del literal que hayamos escrito. Por ejemplo:

Creará un campo varchar(1). Éste sería nvarchar(2) si hubiésemos escrito lo siguiente, indicando un literal Unicode mediante la N delante del literal:

Para un número con parte decimal, el tipo de datos será numeric; lo mismo que para un número de tamaño mayor que el máximo entero positivo. Si escribimos un símbolo de moneda, como €, SQL lo interpretará con el tipo de datos money. Y así, sucesivamente.

Para comprobar todos los tipos de datos que queramos, podemos ejecutar el siguiente script:

Y comprobar en sus resultados qué tipo de datos podemos esperar para cada tipo de constante que se nos ocurra:

Llama especialmente la atención el tratamiento que recibe el NULL de la columna 7: su tipo de datos por defecto también es int.

Éste es el comportamiento que cabe esperar de SQL Server. Sin embargo, lo recomendable sería controlar exactamente qué tipo de datos queremos que nos sea devuelto ejecutando los CAST pertinentes sobre cada una de las constantes. En el ejemplo anterior, lo lógico es que queramos tratar el literal '20140312' como una fecha, por lo que la consulta sobre ese valor debería realizarse así:

Formato de fechas en SQL Server

2014-02-20T17:15:00.003+01:00

Resumen: En cada país, en cada idioma, en cada región... usamos un formato diferente para representar las fechas. SQL Server permite comparar columnas que almacenan fechas contra cadenas de textos que convierte internamente al tipo de datos necesario. Sin embargo, según la configuración regional del servidor, la misma cadena puede ser interpretada de diferentes formas, generando errores que incluso pueden pasar inadvertidos. Para solucionarlo existe un formato de fecha estándar e independiente de cualquier configuración que hay que usar siempre: 'yyyymmdd'.

El problema

Todos sabemos que las fechas no se representan igual en todas las partes del mundo. Mientras que en España, por ejemplo, es típico usar el formato dd/mm/yyyy, donde d es día, m es año y la y representa al año (la fecha de publicación de este artículo en dicho formato sería 20/02/2014), en Alemania se usaría dd.mm.yyyy, pero en Estados Unidos sería mm/dd/yyyy.

Así pues, como se puede observar en este extenso artículo de la Wikipedia, el mismo dato se puede representar de múltiples formas; una misma fecha puede representarse con distintas ordenaciones de sus componentes y con distintos separadores entre ellos.

Por ello, es inevitable que cuando queramos poner una fecha en una consulta para compararla contra un campo de tipo date, datetime o datetime2 nos preguntemos: "¿en qué formato la pongo?"

La importancia del formato

El mismo formato puede funcionar, no funcionar o devolver resultados incorrectos, según lo que espere el servidor contra el que trabajemos. No es una cuestión trivial, ya que equivocarse en el formato puede producir resultados inesperados en el peor momento.

Supongamos que tenemos una base de datos de compras en nuestro entorno de desarrollo con la que trabajamos en español y queremos obtener todos los pedidos hechos el 12 de febrero de 2014.

La consulta que realizaríamos sería la siguiente:
Esta consulta funcionaría correctamente siempre y cuando la ejecutásemos solamente en servidores configurados en español. Sin embargo, si la ejecutásemos contra un servidor configurado en inglés, ¡estaríamos pidiendo de repente los pedidos generados el 2 de diciembre de 2014!

Tendríamos, pues, un error que pasaría inadvertido hasta que un usuario se diese cuenta de que no está obteniendo los resultados que esperaba. Esto puede tener consecuencias bastante desastrosas para consultas que afecten a fechas de caducidad, contabilidad, programas de fidelización de clientes... ¡cualquier cosa!

Puedes hacer la siguiente prueba para comprender mejor lo que está ocurriendo (además de SET LANGUAGE, existe el comando SET DATEFORMAT, que también puede cambiar la forma de SQL Server de interpretar una fecha):

Verás que el resultado cambia. En inglés se nos devuelve el mes December (diciembre), mientras que en español el mes resultante es mayo. Si la fecha consultada hubiese sido del día 13 en adelante de cualquier mes, se habría producido directamente un error ejecutando la consulta contra cualquier servidor configurado en inglés o cualquier otro idioma que espere un formato del tipo mm/dd/yyyy.

El formato independiente, al rescate

Para solucionar estos problemas existe un formato universal, que SQL Server entiende siempre, y que es totalmente independiente de cualquier configuración del servidor.

Cualquier fecha representada de la siguiente manera es interpretada siempre correctamente:

'yyyymmdd'

Así pues, en nuestro ejemplo anterior, para pedir los pedidos del día 12 de febrero de 2014, utilizaríamos la siguiente consulta:

Se puede comprobar, imitando el script de prueba anterior, que a SQL Server no le afecta en absoluto la configuración actual de idioma:

Esta vez hemos obtenido el mismo resultado tanto para inglés como para español: Febrero.

Otros formatos posibles son:

Para fechas cortas: 'yymmdd'
Para fechas con hora incluida: 'yyyymmdd hh:mm:ss.mmm'

Estos tres formatos se resumen en el patrón '[yy]yymmdd [hh:mm[:ss][.mmm]]' donde todo lo que está entre corchetes es opcional.

También existe un formato universal con separador de fechas, que siempre ha de llevar hora (solamente los milisegundos son opcionales), y que es el siguiente: 'yyyy-mm-ddThh:mm:ss.[mmm]'.

Referencias:

Inside Microsoft SQL Server 2005: T-SQL Programming, by Itzik Ben-Gan
Aaron Bertrand's "Bad habits to kick: mis-handling date / range queries"

Columnas Identity: Insertar valores en columnas con la propiedad Identity

2013-10-22T12:23:00.001+02:00

La propiedad Identity sobre una columna de una tabla de la base de datos, hace que en ésta sus valores se generen secuencialmente, a partir de una raíz, para cada registro que insertemos en ella. Las columnas identity son de tipo numérico, generalmente entero, quedando los tipos de datos que soportan dicha propiedad los siguientes: tinyint, smallint, int, bigint, decimal(p,0), o numeric(p,0).

Identity supone una gran ayuda para generar claves sobre tablas. Sus ventajas son, principalmente, que es SQL Server quien se encarga de rellenar su valor y que jamás se va a repetir el valor de la misma, ya que SQL Server genera el siguiente valor disponible para cada registro insertado. Esto evita problemas de concurrencia. Es muy típico en las aplicaciones obtener el máximo valor de la columna clave, sumarle 1 e insertar el siguiente registro. Este mecanismo no presenta ningún problema si somos el único usuario conectado a la base de datos. Sin embargo, en cuanto haya dos usuarios conectados a la misma base de datos, puede darse el escenario en el que ambos leen el mismo valor y, por consiguiente, intentan insertar el mismo valor, produciendo así una violación de la clave primaria.

Por supuesto, este escenario es controlable, mediante gestión de concurrencia, transacciones, etc. Pero todo ello tiene un coste, tanto de programación como de rendimiento. Identity aparece aquí al rescate.

Identity también tiene sus potenciales problemas:

Si se borran registros, se quedarán saltos en la numeración que jamás se usarán.
Inicialmente, no podemos elegir el valor que se inserta en cada registro
Es más complicado obtener el número recién insertado para seguir operando con el objeto en nuestra aplicación (supongamos que creamos un cliente nuevo en nuestra aplicación de ventas, y queremos saber su código para mantener una referencia a él en caso de que queramos hacer posteriores actualizaciones).

El primero de los problemas no debería suponer inconveniente alguno, aunque puede ser un factor de decisión para acabar usando Identity o no.

Para el último de los puntos, existe una solución desde SQL Server 2005, mediante el uso de la cláusula OUTPUT.

Centrémonos ahora en el punto intermedio, que es el que da título a esta entrada:

Cómo insertar un valor en una columna Identity

Es sencillo. En un principio, si intentamos insertar un valor en una columna de este tipo, obtendremos el siguiente mensaje de error:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TestIdentity' when IDENTITY_INSERT is set to OFF.

En ese mensaje tenemos una pista de lo que podemos hacer. Activando la opción IDENTITY_INSERT sobre la tabla, podremos insertar el valor que queramos. Por ejemplo, de la siguiente manera:

Es sencillo, y nos da cierto control sobre columnas con la propiedad Identity. La única restricción que debemos tener en cuenta, es que no podemos ejecutar simultáneamente SET IDENTITY_INSERT ON para más de una tabla en una sesión/conexión.

Actualización:

Como decíamos al principio, el propósito de las columnas de tipo Identity es el de servir de claves únicas, ya sean clustered o no, ya sean la clave primaria o no. Podemos pensar que, como delegamos en SQL Server la responsabilidad de insertar nuevos valores en estas columnas, de manera sucesiva y única, nunca vamos a tener valores repetidos en la misma.

Pero ¡ojo! si hacemos uso de SET IDENTITY_INSERT ON, nada ni nadie nos asegura que no vayamos a tener valores repetidos en las columnas Identity. Así pues, si no estamos usando estas columnas como clave primaria de la tabla, es conveniente crear una restricción de unicidad sobre ellas mediante la creación de un índice único. La decisión de si ha de ser clustered o no ya depende de otros factores que debemos valorar (si normalmente hacemos consultas en las que en el WHERE se va a usar esta columna u otra, etc).

Script para encontrar código SQL entre los objetos de tu base de datos

2013-09-23T09:24:00.001+02:00

En algunas ocasiones, buscando problemas de rendimiento en bases de datos, nos enfrentamos a una lista de consultas que son las causantes de dichos problemas.

Una vez obtenida esa lista de consultas, el problema a resolver es: ¿Dónde están codificadas esas consultas? ¿Forman parte del código de nuestra aplicación o, por el contrario, su código T-SQL está en alguno de nuestros objetos de la base de datos; véase un trigger, un procedimiento almacenado, una función o una vista?

Si se da el segundo caso, y no somos capaces de reconocer el objeto que está ejecutando la consulta que buscamos, disponemos de una vista de sistema (entre las vistas de objetos del catálogo) que nos ayudará a encontrar dónde se esconde: sys.sql_modules.

Así, una consulta como la siguiente es la que necesitamos para encontrar el código:

* Si tu versión de SQL Server es anterior a SQL Server 2005, hay que usar la tabla de sistema sys.syscomments.

Programar triggers correctamente: qué son y cómo se usan inserted y deleted

2013-09-02T17:21:00.000+02:00

Revisando recientemente la programación de algunos triggers, se han detectado algunos errores que se suelen cometer cuando se desconoce la idiosincrasia de estos objetos y sus tablas especiales inserted y deleted.

En uno de los triggers revisados, se quería copiar el valor de un campo datetime de la tabla para la que se había programado el trigger a otra. El código usado en el trigger (simplificado) era algo como lo siguiente:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  UPDATE Table2  
  SET Fecha = Table1.Fecha  
  FROM Table2  
  INNER JOIN Table1 ON Table1.PrimaryKeyField = Table2.PrimaryKeyField  
 END

Como comprenderéis inmediatamente, un trigger así hace muchas más cosas que las pretendidas inicialmente. Sí, copiará la fecha de Table1 a Table2, pero, cada vez que actualicemos un solo registro en Table1, se actualizarán ¡todas las fechas! en Table2 a partir de Table1.

Observad el código detenidamente y veréis que en el trigger no hay ninguna condición que le diga a SQL Server "haz esto solamente para los registros afectados por la instrucción desencadenante". El resultado puede ser desastroso, ya que si se trata de tablas muy grandes estaremos actualizando multitud de registros innecesariamente, perjudicando el rendimiento de nuestro servidor gravemente.

Como sabréis, un trigger se ejecuta cada vez que hay una sentencia que modifica la tabla que contiene el trigger. Es decir, podemos definir triggers que se ejecuten tras un DELETE, un INSERT o un UPDATE. Asimismo, también podemos definir triggers que se ejecuten para dos de estos tipos de instrucciones o para las tres. Para ello, debemos especificar tras la palabra clave AFTER a qué tipo de instrucciones queremos que afecte el trigger.

Bien, una vez hemos definido cuando ha de actuar el trigger, ¿cómo sabemos qué registros han sido afectados por la ejecución de la sentencia desencadenante?

¡inserted y deleted, al rescate!

Los triggers definen dos tablas especiales que contienen toda la información que necesitamos: inserted y deleted. Ambas son subconjuntos de la tabla que contiene el trigger, justamente con los registros que nos interesan: los afectados por la sentencia desencadenante.

inserted contiene los registros con los nuevos valores para triggers que se desencadenan con sentencias INSERT (nuevos registros) y UPDATE (nuevo valor para registros actualizados).

deleted, por su parte, contiene los registros con los viejos valores para triggers que se desencadenan con sentencias DELETE (registros borrados) y UPDATE (valor anterior para los registros actualizados).

Así pues, en un trigger definido como AFTER INSERT sólo dispondremos de la tabla inserted, en uno definido como AFTER DELETE solamente tendremos la tabla deleted, mientras que, finalmente, ambas tablas estarán disponibles en triggers definidos para ejecutarse tras un UPDATE con AFTER UPDATE, pudiendo consultar así de los valores antes y después de actualizarse los registros correspondientes.

Así pues, podemos pensar ahora en cómo reescribir el trigger anterior para que solamente los registros afectados actualicen el valor del campo Fecha en Tabla1.

El resultado sería el siguiente:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  UPDATE Table2  
  SET Fecha = inserted.Fecha  
  FROM Table2  
  INNER JOIN inserted ON inserted.PrimaryKeyField = Table2.PrimaryKeyField  
 END

Así, tendremos todos nuestros registros actualizados cada vez que se ejecute un UPDATE sobre Table1, sin alterar otros registros no implicados en la operación (cada registro de Table2 se actualizará únicamente en el momento en que se modifique el valor del campo Fecha para su registro correspondiente en Table1).

Otros errores típicos al programar un trigger

Finalmente, hay otro par de detalles que podemos comentar para mejorar nuestra programación de triggers. Como alguno ya os habréis preguntado: ¿y qué pasa si el campo Fecha no ha sido actualizado? ¿No estamos haciendo UPDATES innecesarios sobre Table2?

Efectivamente, pero también existe una solución. En un trigger del tipo AFTER UPDATE podemos comprobar si un campo determinado ha sido afectado por la operación desencadenante mediante la función UPDATE(campo).

Así, nuestro trigger podría (y debería) mejorarse de la siguiente manera:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  SET NOCOUNT ON;  
  IF UPDATE(Fecha)  
  BEGIN  
   UPDATE Table2  
   SET Fecha = inserted.Fecha  
   FROM Table2  
   INNER JOIN inserted ON inserted.PrimaryKeyField = Table2.PrimaryKeyField  
  END  
 END

*Sobre el uso de SET NOCOUNT ya hablamos en otra entrada anterior.

Finalmente, los triggers deben programarse para que funcionen independientemente de si el numero de registros afectados es uno, muchos o ninguno (¡Ojo! También se desencadenan los triggers si no hay registros afectados, y esto hay que tenerlo muy en cuenta).

Con inserted y con deleted hay que trabajar siempre como si de una tabla más se tratase y, ¡muy importante!, pensar que puede contener más de un registro. Siempre hay que programar los triggers pensando en que no solamente se verá afectada una fila. Muchas veces se programan así porque conocemos la aplicación cliente y sabemos que solamente opera con los registros de uno en uno. Pero, creedme, llegará el día en que haréis un UPDATE para más de un registro y vuestro trigger dejará de funcionar correctamente en ese momento.

El siguiente código es muy habitual en muchos triggers, y sólo funcionaría cuando hay un registro afectado:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  SET NOCOUNT ON;  
  IF UPDATE(Fecha)  
  BEGIN  
   DECLARE @fecha datetime  
   DECLARE @primaryKeyField int  
   SELECT @primaryKeyField = PrimaryKeyField, @fecha = Fecha FROM inserted  
   UPDATE Table2  
   SET Fecha = @fecha  
   WHERE Table2.PrimaryKeyField = @primaryKeyField  
  END  
 END

Y, por supuesto, ¡no vale abrir un cursor sobre inserted para iterar por los registros afectados y hacer lo que se muestra en el código anterior fila a fila! En el 99% de las veces que vayáis a usar un cursor, existe una alternativa basada en conjuntos de registros, mucho más eficiente, que podréis usar. Los cursores tienen su aplicación en SQL Server, pero casi nunca es la de iterar por conjuntos de resultados para operar con ellos uno a uno. Normalmente, un JOIN con inserted o deleted y la otra tabla o tablas con las que queremos operar en el trigger suele ser el mecanismo apropiado trabajar con todos los registros a la vez.

Parámetros OUTPUT en procedimientos almacenados: recuperar su valor desde la llamada

2013-04-09T12:24:00.001+02:00

Los procedimientos almacenados de SQL Server pueden devolver valores bien sea a través de su valor de retorno, bien sea mediante los denominados parámetros OUTPUT.

Ahora bien, para que un procedimiento almacenado nos devuelva el valor de un parámetro OUPUT deberemos tener en cuenta dos cosas: que sea declarado como OUTPUT en el propio procedimiento y que en la ejecución del mismo se especifique la palabra clave OUPUT junto al parámetro en el que queremos que se nos devuelva el valor.

1. La primera premisa es, obviamente, declarar el parámetro como OUPUT en la declaración del procedimiento almacenado. Por ejemplo, imaginemos que queremos crear un procedimiento almacenado que sume dos números y nos devuelva el resultado. Reservamos el valor de retorno del procedimiento almacenado para gestionar posibles errores (por ejemplo, parámetros incorrectos).

CREATE PROCEDURE Suma
@sumando1 int,
@sumando2 int,
@resultado int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @sumando1 < 0 OR @sumando2 < 0
RETURN 1
-- Insert statements for procedure here
SET @resultado = @sumando1 + @sumando2
RETURN 0
END

En este caso, queremos que nuestro procedimiento almacenado "Suma" sólo admita números enteros positivos o el cero, por lo que en caso de recibir algún valor negativo en alguno de los dos sumandos, se devolvería un 1, indicando así un error en la ejecución del procedimiento almacenado (Nota: la gestión de errores mediante el valor de retorno de los procedimientos almacenados es sencilla pero limitada. Para una gestión de errores más completa, existen mecanismos como RAISERROR o THROW en combinación con bloques TRY...CATCH)

2. La segunda, imprescindible también, es responsabilidad de la llamada al procedimiento almacenado. Para que la variable en la que queremos obtener el valor de retorno sea rellenada por el procedimiento almacenado debemos especificar la palabra clave OUTPUT junto al parámetro que hace las veces de parámetro de salida (en el orden correspondiente de la llamada, por supuesto). De lo contrario, nuestra variable no se rellenará con el valor devuelto por el procedimiento almacenado. Es decir, un código como el siguiente, producirá un resultado quizás inesperado de NULL:

DECLARE @resultado int
EXEC Suma 1, 2, @resultado
SELECT @resultado

Para obtener el valor esperado, en este caso un 3, debemos escribir lo siguiente:

DECLARE @resultado int
EXEC Suma 1, 2, @resultado OUTPUT
SELECT @resultado

Hay que observar que ambas ejecuciones son correctas, ninguna de ellas produce error. En ambas, la declaración del procedimiento almacenado es correcta. Sin embargo, solamente especificando que queremos obtener el valor del parámetro de salida mediante la palabra clave OUTPUT, la variable @resultado ha sido rellenada correctamente en el código cliente que llama al procedimiento almacenado.

ExecuteNonQuery devuelve -1 si se indica SET NOCOUNT ON

2013-01-17T09:52:00.002+01:00

Ejecutando un procedimiento almacenado que, a su vez llamaba a un método en un assembly instalado en el CLR de SQL Server, obtenía un error inesperado. El error me decía que una de las instrucciones SQL ejecutadas dentro del assembly estaba fallando. Sin embargo, si omitía el procesamiento del error, me daba cuenta de que la sentencia se había ejecutado correctamente.

¿Qué estaba ocurriendo?

La sentencia en el assembly se estaba ejecutando mediante el método ExecuteNonQuery de la clase SQLCommand de ADO.NET. Según la MSDN, este método devuelve siempre el número de filas afectadas para instrucciones del tipo UPDATE, INSERT o DELETE. En mi caso se trataba de un UPDATE. Sin embargo, el valor devuelto siempre era -1, lo que el código del assembly procesaba como un error, ya que se esperaba afectar siempre a un registro de la base de datos.

Intentando averiguar las causas del porqué de dicho -1, encontré este artículo, en el que se explica muy claramente el motivo del valor negativo devuelto:

SET NOCOUNT ON

Esa instrucción, habitual en los procedimientos almacenados, normalmente escrita al principio de los mismos, sirve para evitar que SQL Server nos devuelva la cuenta de filas afectadas por cada operación ejecutada en el procedimiento. Se evitan así dos cosas:

Interferir en los resultados devueltos
Mejorar el rendimiento del procedimiento almacenado, sobre todo si el número de sentencias ejecutadas es elevado.

Asegurándonos de poner SET NOCOUNT OFF delante de la llamada al método del assembly, ¡problema resuelto!

Microsoft quiere punto y coma al final de cada sentencia T-SQL

2013-01-09T16:39:00.000+01:00

Microsoft publica en la MSDN la "Lista de características desusadas del motor de base de datos de SQL Server 2012" o, lo que es lo mismo, las deprecated features.

Esta lista es útil para mantener nuestra aplicación actualizada, de tal forma que podamos seguir usando las nuevas funcionalidades de las sucesivas versiones SQL Server. Mantener en nuestro código alguna característica marcada como obsoleta o en desuso implicaría que no podríamos ejecutarlo en la versión de SQL que ya no la soportase.

Pues bien, en dicha lista aparecen tanto las características que no serán soportadas en la próxima versión de SQL Server como las que estarán en desuso en futuras versiones. Esta segunda lista es muy amplia, pero si leemos detenidamente en ella nos encontraremos con, al menos, una característica que nos llamará la atención:

"Not ending Transact-SQL statements with a semicolon."

Es decir, Microsoft pretende que deje de ser opcional -como lo es hasta ahora- finalizar las sentencias SQL con o sin punto y coma. Hasta ahora, su opcionalidad ha provocado que millones de líneas de código Transact SQL se haya escrito sin punto y coma al finalizar cada sentencia. Incluso el código que Microsoft implementa en las bases de datos de sistema no incluye este símbolo final.

Así pues, parece descabellado que se pretenda que a partir de una incierta futura versión de SQL Server sea obligatorio, como así pretende Microsoft, finalizar cada sentencia con punto y coma. En tal circunstancia, todo el código anterior debería ser revisado... ¡Todo, sin excepción! No se trataría de buscar una determinada característica en él y reemplazarla, sino de cambiarlo entero. Incluso aquel programador acostumbrado a finalizar sus sentencias con dicho signo de puntuación puede haber olvidado ponerlo en alguna ocasión, ya que su T-SQL era igualmente válido.

A través del grupo SQL Server Pofessionals de LinkedIn descubrimos que Microsoft ya mostró su intención de obligarnos a finalizar las sentencias con punto y coma con la versión SQL Server 2005. A día de hoy, sigue pareciendo imposible que eso suceda. En cualquier caso, ¿empezaríais a programar usando punto y coma para terminar vuestras instrucciones? Parece una buena opción, salvo que se os olvide alguno en el camino y luego no seáis capaces de encontrar el punto y coma desaparecido entre las líneas de código que ya consideráis "correctas".

¿Qué opináis?

Script para obtener el tamaño de todas las tablas de la base de datos

2012-11-13T11:01:00.003+01:00

En algunas ocasiones podemos vernos con la necesidad de conocer qué tablas de nuestra base de datos están ocupando más espacio en disco. Por ejemplo, si disponemos de SQL Server Express, cuyas bases de datos están limitadas a 4GB o 10GB, según la versión que estemos usando -4, hasta 2005; 10, a partir de 2008-, aparte de usar las opciones de comprimir la base de datos, poner el log en el modo simple de recuperación o ajustar las políticas de crecimiento automático de nuestros ficheros, podemos necesitar averiguar qué tablas crecen más para tomar las decisiones oportunas.

Para ello, podemos mirar tabla por tabla sus propiedades o usar el siguiente script que nos devolverá una fila por cada tabla, con su espacio usado y reservado:

SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 

-- Table row counts and sizes.

DECLARE @sizes TABLE

(

    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT @sizes EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   @sizes
ORDER BY convert(int, substring(data, 1, len(data)-3)) desc

Nuevo modo de autocompletado para IntelliSense en SQL Server Management Studio 2012. ¿Cómo cambiarlo?

2012-10-19T08:27:00.000+02:00

Con SQL Server 2008 llegó IntelliSense para T-SQL. IntelliSense es una herramienta que nos ofrece el editor de SQL Server Management Studio para facilitarnos la escritura de código T-SQL. Básicamente, entre otras cosas, carga el catálogo de nuestra base de datos para facilitarnos la escritura de consultas.

Así, si tenemos una tabla que se llama MiTabla y empezamos a escribir en el editor de consultas algo como

SELECT *
FROM Mi

Intellisense nos mostrará rápidamente las opciones del catálogo que se pueden corresponder con lo que estamos escribiendo, para facilitarnos así la escritura de textos y minimizar también la posibilidad de erratas en nuestro tecleo de código.

Hasta SQL Server 2012, IntelliSense mostraba esta lista de miembros, seleccionando progresivamente el valor más parecido a lo que estábamos escribiendo. Si pulsábamos la tecla espacio, una coma, retorno de carror, un paréntesis, o cualquier otra tecla que indicase al editor que habíamos acabado de escribir el término actual, IntelliSense lo completaba automáticamente con la selección actual.

Esto conllevaba que, en algunas ocasiones, IntelliSense nos auto-completase palabras sin que nuestra intención real fuese escribirlas.

Así pues, el auto-completado ha cambiado ligeramente en la nueva versión de SQL Server. Como podemos apreciar en la imagen siguiente, en la versión actual SQL Server deja una línea encima de todos los posibles valores que encajan con lo que estamos escribiendo. En esa línea aparece simplemente lo que hemos escrito hasta el momento. Si lo que queremos es conservar lo tecleado y no elegir ninguno de los valores que IntelliSense nos ofrece, basta con pulsar espacio, coma o cerrar paréntesis y se mantendrá el texto tal cual.

El nuevo IntelliSense nos permite mantener lo que estamos escribiendo o seleccionar un valor propuesto.

¿Y si quiero mantener el comportamiento anterior?

Es posible. SQL Server nos ofrece la posibilidad de mantener el mencionado comportamiento anterior total o parcialmente.

Si nos gusta el nuevo modo de funcionamiento de IntelliSense, pero queremos poder elegir la palabra que queremos escribir cuando IntelliSense ya la ha encontrado, podemos usar el tabulador, así: Vemos en la imagen que la selección más aproximada al texto que estamos escribiendo aparece rodeada de un recuadro amarillo. Si pulsamos la tecla TAB se escribirá lo que aparece dentro de ese recuadro, como cuando en versiones anteriores se elegía automáticamente la opción más parecida a lo escrito.

Aun así, si eres de los que prefiere el modo de funcionamiento anterior de IntelliSense, SQL Server te ofrece la posibilidad de seguir usándolo, mediante la combinación de teclas Ctrl + Alt + Espacio. También podemos acceder a esta opción en el menú Edición / IntelliSense. Eso sí, con el inconveniente de que habrá que cambiar el modo cada vez que abramos una ventana nueva del editor.

Si queremos cambiar el modo del IntelliSense podemos pulsar Ctrl + Alt + Espacio.

Una vez cambiado al modo anterior, escribiendo lo que aparece en la imagen, SQL Server nos completará automáticamente la palabra MiTabla si pulsamos cualquiera de las teclas comentadas anteriormente.

Modo de funcionamiento anterior a SQL Server 2012 de IntelliSense.

Recordad que en ambos modos podremos movernos con los cursores por la lista mostrada o usar el ratón para elegir el texto que realmente queremos escribir. En este caso, podríamos elegir MiTabla2 simplemente pulsando la tecla cursor con la flecha hacia abajo para seleccionarla.

Listar todos los ficheros de un servidor SQL Server

2012-10-10T19:43:00.003+02:00

Existen multitud de vistas de sistema en SQL Server que nos ayudan a hacer las tareas de administración y mantenimiento mucho más sencillas. Una de ellas nos permite listar todos los ficheros, tanto de datos como de log, relativos a las bases de datos que tenemos en nuestro servidor.

Para ello, usaremos la vista sys.master_files, que nos proporciona información de cada uno de los ficheros de nuestras bases de datos, con todas sus propiedades. Algunas de ellas, incluidas en la consulta de ejemplo, son: nombre del fichero, ruta completa del fichero en disco, tamaño, estado y configuración sobre su crecimiento, del que ya hablamos en posts anteriores.

Actualizado el 19 de septiembre de 2013: Ahora el script muestra el tamaño de los ficheros en Megabytes, mientras que, anteriormente, lo hacía en número de páginas de 8KB.

TABLESAMPLE, o cómo obtener una muestra aleatoria de registros de una tabla

2012-10-10T12:46:00.001+02:00

¿Alguna vez has necesitado obtener una muestra de datos totalmente aleatoria de tu base de datos? En SQL Server es posible hacerlo, mediante la cláusula TABLESAMPLE, que se aplica a la definición del origen de datos que vamos a recuperar con la cláusula FROM.

TABLESAMPLE nos permite indicar un número de filas o un porcentaje de filas a devolver, de la siguiente manera:

--devuelve el 10% de las filas totales
--(dependiendo del tamaño de los registros en la base de datos, no del número de filas).
SELECT *
FROM YourTable
TABLESAMPLE (10 PERCENT)

--devuelve una muestra de 15 filas del conjunto de resultados
SELECT *
FROM YourTable
TABLESAMPLE (15 ROWS)

Algunas consideraciones a la hora de usar TABLESAMPLE incluyen:

No se puede usar en la definición de vistas
No se puede usar en la definición de funciones de tabla inline
No se puede aplicar a tablas devueltas por funciones tipo tabla

¡Ganador del premio por resolver los puzzles T-SQL!

2012-10-03T09:48:00.001+02:00

Pinal Dave ha anunciado a través de twitter el ganador del premio por resolver los dos puzzles SQL que planteaba ahora hace unos días, y de los que nos hicimos eco en este mismo blog. Pues bien, parece que nuestras respuestas eran correctas :)

The winner of 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card bit.ly/Odx9Wyis @jaimeml - Congratulations!
— Pinal Dave (@pinaldave) octubre 3, 2012

Great! :) RT @pinaldave: winner of 2 T-SQL Puzzles and Win USD 50 worth Amazon Card bit.ly/Odx9Wyis @jaimeml - Congratulations!
— Jaime Mtnz Lafargue (@jaimeml) octubre 3, 2012

Puzzles en T-SQL ¿te atreves a encontrar la respuesta?

2012-09-27T13:03:00.000+02:00

Recientemente, Pinal Dave publicó en su blog SQL Authority un post con dos puzzles para Transact SQL. La curiosidad nos hizo intentar averiguar las respuestas, y aquí va lo que descubrimos (podéis intentar adivinarlas antes de leer la solución):

Puzzle 1. ¿Por qué la siguiente query produce como resultado un asterisco (*)?

SELECT CAST (100 AS varchar(2))

Podríamos esperar un error de desbordamiento, que el 100 se truncara por alguna de sus extramos y quedase reducido a un 10 o a un doble cero (00). Pero no, SQL Server nos retorna un asterisco.

Solución: La MSDN nos explica cómo actúa el motor de la base de datos a la hora de convertir un número (smallint, int...) a una cadena. Si la cadena soporta caracteres Unicode (nchar o nvarchar), se producirá un error. Sin embargo, si estamos intentando convertir el número en un char o varchar con menor longitud de la necesaria, obtendremos un asterisco. Dicho asterisco -nos explica el artículo- quiere decir que la longitud resultante es demasiado corta para mostrar el valor.

Conversiones de números en cadenas de tamaño insuficiente, según la MSDN

Puzzle 2. ¿Cuál es la sentencia más corta que produce un 1 como resultado, sin usar números?

Para esta pregunta encontramos una discusión abierta en el grupo SQL Server Developers de LinkedIn. Allí, el ingenio de algunos de los participantes nos llevó a respuestas cada vez más cortas. Os intentamos explicar cada una de ellas.

Solución: Inicialmente nos encontramos con la siguiente query:

SELECT COUNT(*)

COUNT(*) nos devuelve el recuento del número de filas afectadas por la sentencia SELECT. Como no estamos devolviendo filas de una tabla, SQL asume que el número de filas devueltas es uno (como si ejecutásemos una SELECT de una lista de valores constantes - SELECT constante1, N'constante2', dia-mes-año...)

Con la misma longitud existe otra solución: Pedirle a SQL Server la longitud de una cadena de texto de un solo carácter. Así:

SELECT LEN('a')

Buenos intentos, pero se pueden mejorar. SQL Server acepta los tipos de datos money y smallmoney. Para representar estos tipos de datos, se pueden usar símbolos de moneda. Así, 100€ se pueden representar como €100. Si dejamos la parte numérica vacía, SQL Server interpreta que la cantidad es cero. Así, SELECT $ nos devolvería 0.00.

A partir de un cero, ¿qué funciones SQL nos pueden devolver un uno? Son las siguientes:

SELECT EXP($) -- El número e elevado a cero es uno.
SELECT DAY($) -- El día cero para SQL Server es el 1 de enero de 1900, así que cogemos el día
SELECT COS($) -- El coseno de 0 es 1.

¿Podéis mejorarlo? :)

Cómo determinar la clave primaria en una entidad con Entity Framework y Data Annotations

2012-09-06T16:47:00.002+02:00

Con Entity Framework 4.1, a las opciones del modelo primero (Model First) y base de datos primero (Database First), se añadió Code First. Con esta aproximación, el programador escribe clases (objetos POCO) para cada una de las entidades que quiere manejar en su aplicación. Estas entidades pueden generar tablas en una nueva base de datos o ser mapeadas a una base de datos ya existente.

En general, EF usa una serie de convenciones para definir el modelo y mapearlo a la base de datos de forma automática. Por ejemplo, Entity Framework puede reconocer automáticamente el campo que es clave primaria de una tabla si éste se llama de la forma NombreTablaId

El problema.

Supongamos que estamos desarrollando una nueva aplicación para una colección de discos. Se trata de una aplicación web, que estamos programando con la arquitectura MVC. Si en ella tenemos una tabla que se llama Disco, EF espera que el campo que sea clave primaria de la tabla se llame DiscoId. De no ser así, cuando intentemos crear, por ejemplo, el controlador para el modelo Disco, obtendremos el siguiente error:

Visual Studio nos dice que hay un error de validación en el modelo. Discography no tiene clave definida.

Si estamos creando una base de datos nueva, puede que lo más sencillo sea cambiar el nombre de nuestro campo clave primaria para adaptarlo a las necesidades del Entity Framework. Sin embargo, si nuestra clave es una clave compuesta o estamos trabajando sobre una base de datos ya existente sobre la que no tenemos la posibilidad de cambiar el nombre de los campos, necesitamos una alternativa.

La solución.

Aquí es donde las Data Annotations vienen al rescate: Podemos indicarle a las propiedades de nuestra
entidad (nuestro modelo) ciertas características que lo definen mejor, para que Entity Framework comprenda correctamente cómo está definida. Una de ellas es la propiedad KeyAttribute, representada como [Key]. Así le podemos decir al EF cuál de las columnas de nuestro objeto POCO es la que ejerce de clave primaria:

[Key]
public int TableKey { get; set; }

Si, además la clave primaria es compuesta, tenemos también solución: debemos indicar el atributo Key a todas las columnas que forman parte de la clave primara, indicando el orden de éstas en la misma, de la siguiente manera:

[Key]
[Column(" TableKey1", Order = 0)]
public int TableKey1 { get; set; }
[Key]
[Column(" TableKey2", Order = 1)]
public int TableKey2 { get; set; }

Si hemos instalado las Entity Framework Power Tools (recomendable, aunque todavía en fase Beta), podremos ver cómo queda nuestro modelo tras realizar estas anotaciones (mediante clic derecho sobre el fichero contexto y eligiendo en el menú la opción Entity Framework / View Entity Data Model):

Diagrama de nuestra entidad Disco, con los dos campos formando parte de la clave primaria.

Data Annotations vs Fluent API.

Existe una alternativa a las Data Annotations, llamada Fluent API, que veremos más adelante. Data Annotations presenta una manera más sencilla de configurar el modelo, mientras que Fluent API ofrece más posibilidades. Además, las Data Annotations son usadas al mismo tiempo por Entity Framework y por MVC, para validar los datos que el usuario introduce en las vistas (páginas web). Sin embargo, es posible que si nuestro modelo es ligeramente complejo, necesitemos de la potencia de Fluent API.

Puedes leer más acerca de Data Annotations y Fluent API aquí.

Puedes encontrar más información acerca de las anotaciones en:

http://sankarsan.wordpress.com/2011/06/05/entity-framework-4-1-annotationspart-1/