Mi buen amigo Aaron Bertrand me inspiró para escribir este artículo. Me recordó cómo a veces damos las cosas por sentado cuando nos parecen obvias y no siempre nos molestamos en verificar la historia completa detrás de ellas. La relevancia de T-SQL es que a veces asumimos que sabemos todo lo que hay que saber sobre ciertas características de T-SQL, y no siempre nos molestamos en verificar la documentación para ver si hay más. En este artículo, cubro una serie de características de T-SQL que a menudo se pasan por alto por completo o que admiten parámetros o capacidades que a menudo se pasan por alto. Si tiene ejemplos propios de gemas de T-SQL que a menudo se pasan por alto, compártalos en la sección de comentarios de este artículo.

Antes de comenzar a leer este artículo, pregúntese qué sabe sobre las siguientes características de T-SQL: EOMONTH, TRADUCIR, RECORTAR, CONCAT y CONCAT_WS, REGISTRAR, variables de cursor y COMBINAR con la SALIDA.

En mis ejemplos usaré una base de datos de ejemplo llamada TSQLV5. Puede encontrar el script que crea y rellena esta base de datos aquí, y su diagrama ER aquí.

EOMONTH tiene un segundo parámetro

La función EOMONTH se introdujo en SQL Server 2012. Muchas personas piensan que solo admite un parámetro que contiene una fecha de entrada, y que simplemente devuelve la fecha de fin de mes que corresponde a la fecha de entrada.

Considere una necesidad un poco más sofisticada de calcular el final del mes anterior. Por ejemplo, supongamos que necesita consultar las Ventas.La tabla de pedidos, y los pedidos de devolución que se realizaron al final del mes anterior.

Una forma de lograr esto es aplicar la EOMONTH función SYSDATETIME a llegar el fin de mes de la fecha del mes en curso, y, a continuación, aplicar la función DATEADD para restar un mes a partir del resultado, así:

USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));

tenga en cuenta que si usted realmente ejecutar esta consulta en el TSQLV5 base de datos de ejemplo que usted conseguirá un resultado vacío desde la última fecha de la orden registrada en la tabla es el 6 de Mayo, 2019. Sin embargo, si la tabla tenía pedidos con una fecha de pedido que cae en el último día del mes anterior, la consulta los habría devuelto.

Lo que muchas personas no se dan cuenta es que EOMONTH admite un segundo parámetro en el que se indica cuántos meses sumar o restar. Esta es la sintaxis de la función:

EOMONTH ( start_date )

Nuestra tarea se puede lograr de manera más fácil y natural simplemente especificando -1 como segundo parámetro de la función, de la siguiente manera:

SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);

TRADUCIR a veces es más simple que la REEMPLACE

Muchas personas están familiarizadas con la función de REEMPLAZAR y cómo funciona. Lo utiliza cuando desea reemplazar todas las ocurrencias de una subcadena con otra en una cadena de entrada. Sin embargo, a veces, cuando tiene varios reemplazos que necesita aplicar, usar REEMPLAZAR es un poco complicado y resulta en expresiones complicadas.

Como ejemplo, supongamos que se le da una cadena de entrada @s que contiene un número con formato en español. En España utilizan un punto como separador para grupos de miles y una coma como separador decimal. Debe convertir la entrada al formato US, donde se usa una coma como separador para grupos de miles y un punto como separador decimal.

Con una llamada a la función REEMPLAZAR, solo puede reemplazar todas las ocurrencias de un carácter o subcadena con otro. Para aplicar dos reemplazos (puntos a comas y comas a puntos) necesita anidar llamadas a funciones. La parte complicada es que si usa REEMPLAZAR una vez para cambiar los puntos a comas, y luego una segunda vez contra el resultado para cambiar las comas a puntos, termina con solo puntos. Inténtelo:

DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');

Se obtiene el siguiente resultado:

123.456.789.00

Si quieres seguir usando la función de REEMPLAZAR, se necesitan tres llamadas a la función. Uno para reemplazar puntos con un carácter neutro que sabes que normalmente no puede aparecer en los datos (digamos,~). Otra contra el resultado para reemplazar todas las comas con puntos. Otro contra el resultado para reemplazar todas las ocurrencias del carácter temporal (~en nuestro ejemplo) con comas. He aquí la expresión completa:

DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');

en Este momento tiene el derecho de salida:

123,456,789.00

Es factible, sino que se traduce en una larga y complicada de expresión. ¿Qué pasaría si tuviera que aplicar más reemplazos?

Muchas personas no son conscientes de que SQL Server 2017 introdujo una nueva función llamada TRANSLATE que simplifica enormemente estos reemplazos. Esta es la sintaxis de la función:

TRANSLATE ( inputString, characters, translations )

La segunda entrada (caracteres) es una cadena con la lista de los caracteres individuales que desea reemplazar, y la tercera entrada (traducciones) es una cadena con la lista de los caracteres correspondientes con los que desea reemplazar los caracteres de origen. Esto significa naturalmente que el segundo y tercer parámetros deben tener el mismo número de caracteres. Lo importante de la función es que no realiza pases separados para cada uno de los reemplazos. Si lo hiciera, podría haber resultado en el mismo error que en el primer ejemplo que mostré usando las dos llamadas a la función REEMPLAZAR. En consecuencia, el manejo de nuestra tarea se convierte en una obviedad:

DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');

Este código genera la salida deseada:

123,456,789.00

Eso es maravilloso!

TRIM es más que LTRIM (RTRIM ())

SQL Server 2017 introdujo soporte para la función TRIM. Muchas personas, incluido yo mismo, inicialmente asumen que no es más que un simple acceso directo a LTRIM (RTRIM (entrada)). Sin embargo, si revisas la documentación, te das cuenta de que en realidad es más potente que eso.

Antes de entrar en los detalles, considere la siguiente tarea: dada una cadena de entrada @s, elimine las barras al principio y al final (hacia atrás y hacia adelante). A modo de ejemplo, supongamos que @s contiene la siguiente cadena:

//\\ remove leading and trailing backward (\) and forward (/) slashes \\//

La salida deseada es:

 remove leading and trailing backward (\) and forward (/) slashes 

tenga en cuenta que la salida debe conservar los espacios iniciales y finales.

Si usted no sabe de RECORTE de capacidad total, aquí es una manera de que usted podría haber resuelto la tarea:

DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;

La solución comienza por el uso de TRADUCIR para reemplazar todos los espacios con un carácter neutro (~) y barras diagonales con espacios, a continuación, usando la herramienta RECORTAR para recortar los espacios iniciales y finales a partir del resultado. Este paso recorta esencialmente las barras inclinadas hacia adelante y hacia atrás, utilizando temporalmente ~ en lugar de espacios originales. Este es el resultado de este paso:

\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\

El segundo paso usa TRANSLATE para reemplazar todos los espacios con otro carácter neutro (^) y barras inclinadas hacia atrás con espacios, luego usa TRIM para recortar los espacios iniciales y finales del resultado. Este paso recorta esencialmente las barras inclinadas hacia atrás y hacia atrás, utilizando temporalmente ^ en lugar de espacios intermedios. Este es el resultado de este paso:

~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~

El último paso utiliza TRANSLATE para reemplazar espacios con barras inclinadas hacia atrás, ^ con barras inclinadas hacia adelante y ~ con espacios, generando la salida deseada:

 remove leading and trailing backward (\) and forward (/) slashes 

Como ejercicio, intente resolver esta tarea con una solución compatible con SQL Server 2017 en la que no pueda usar TRIM y TRANSLATE.

De vuelta a SQL Server 2017 y versiones posteriores, si se molestó en revisar la documentación, habría descubierto que TRIM es más sofisticado de lo que pensaba inicialmente. Aquí está la sintaxis de la función:

TRIM ( string )

Los caracteres opcionales DE part le permiten especificar uno o más caracteres que desee recortar desde el principio y el final de la cadena de entrada. En nuestro caso, todo lo que usted necesita hacer es especificar ‘/\’ como esta parte, así:

DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;

Eso es bastante importante mejora en comparación a la anterior solución!

CONCAT y CONCAT_WS

Si ha estado trabajando con T-SQL durante un tiempo, sabe lo incómodo que es lidiar con NULLs cuando necesita concatenar cadenas. Como un ejemplo, considere la ubicación de los datos registrados para los empleados en recursos humanos.Tabla de empleados:

SELECT empid, country, region, cityFROM HR.Employees;

Esta consulta se genera la siguiente salida:

empid country region city----------- --------------- --------------- ---------------1 USA WA Seattle2 USA WA Tacoma3 USA WA Kirkland4 USA WA Redmond5 UK NULL London6 UK NULL London7 UK NULL London8 USA WA Seattle9 UK NULL London

tenga en cuenta que para algunos empleados de la región es irrelevante y una irrelevante región está representada por un valor NULL. Supongamos que necesita concatenar las partes de ubicación (país, región y ciudad), usando una coma como separador, pero ignorando regiones NULAS. Cuando la región es relevante, desea que el resultado tiene la forma <coutry>,<region>,<city> y cuando la región es irrelevante desea que el resultado tiene la forma <country>,<city>. Normalmente, concatenar algo con un valor NULO produce un resultado NULO. Puede cambiar este comportamiento desactivando la opción de sesión CONCAT_NULL_YIELDS_NULL, pero no recomendaría habilitar el comportamiento no estándar.

Si usted no sabía de la existencia de la CONCAT y CONCAT_WS funciones, usted probablemente habría utilizado ISNULL o se UNEN para reemplazar un NULL con una cadena vacía, así:

SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;

he Aquí el resultado de esta consulta:

empid location----------- -----------------------------------------------1 USA,WA,Seattle2 USA,WA,Tacoma3 USA,WA,Kirkland4 USA,WA,Redmond5 UK,London6 UK,London7 UK,London8 USA,WA,Seattle9 UK,London

SQL Server 2012 introdujo la función CONCAT. Esta función acepta una lista de entradas de cadena de caracteres y las concatena, y al hacerlo, ignora los nulos. Por lo tanto, al usar CONCAT, puede simplificar la solución de esta manera:

SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;

Aún así, usted tiene que especificar explícitamente los separadores como parte de la función de las entradas. Para hacer nuestras vidas aún más fáciles, SQL Server 2017 introdujo una función similar llamada CONCAT_WS donde se comienza indicando el separador, seguido de los elementos que desea concatenar. Con esta función, la solución se simplifica aún más así:

SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;

El siguiente paso es, por supuesto, telepatía en absoluto. El 1 de abril de 2020, Microsoft planea lanzar CONCAT_MR. La función aceptará una entrada vacía y descubrirá automáticamente qué elementos desea concatenar leyendo su mente. La consulta se verá así:

SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;

REGISTRO tiene un segundo parámetro

Similar a la EOMONTH función, muchas personas no se dan cuenta de que ya a partir de SQL Server 2012, la función de REGISTRO admite un segundo parámetro que permite indicar el logaritmo de la base. Antes de eso, T-SQL soportaba la función LOG(entrada) que devuelve el logaritmo natural de la entrada (usando la constante e como base), y LOG10(entrada) que usa 10 como base.

Al no ser conscientes de la existencia del segundo parámetro de la función LOG, cuando la gente quería calcular Logb(x), donde b es una base distinta de e y 10, a menudo lo hacían por el camino largo. Puede confiar en la siguiente ecuación:

Como ejemplo, para calcular Log2(8), confíe en la siguiente ecuación:

Traducido a T-SQL, aplique el siguiente cálculo:

DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);

una Vez que te das cuenta de que LOG admite un segundo parámetro, donde indica la base, el cálculo simplemente se convierte en:

DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);

variable de Cursor

Si usted ha estado trabajando con T-SQL por un tiempo, usted probablemente tenía un montón de posibilidades para trabajar con los cursores. Como sabes, cuando trabajas con un cursor, normalmente utilizas los siguientes pasos:

• Declarar el cursor
• Abrir el cursor
• Recorrer los registros del cursor
• Cerrar el cursor
• Desasignar el cursor

Como ejemplo, supongamos que necesita realizar alguna tarea por base de datos en su instancia. El uso de un cursor, que normalmente se utilice código similar al siguiente:

DECLARE @dbname AS sysname; DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN C; FETCH NEXT FROM C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM C INTO @dbname;END; CLOSE C;DEALLOCATE C;

El comando CERRAR libera el conjunto de resultados actual y libera los bloqueos. El comando DESASIGNAR elimina una referencia de cursor y, cuando la última referencia se desasigne, libera las estructuras de datos que componen el cursor. Si intenta ejecutar el código anterior dos veces sin los comandos CERRAR y DESASIGNAR, obtendrá el siguiente error:

Msg 16915, Level 16, State 1, Line 4A cursor with the name 'C' already exists.Msg 16905, Level 16, State 1, Line 6The cursor is already open.

Asegúrese de ejecutar los comandos CERRAR y DESASIGNAR antes de continuar.

Muchas personas no se dan cuenta de que cuando necesitan trabajar con un cursor en un solo lote, que es el caso más común, en lugar de usar un cursor normal, puede trabajar con una variable de cursor. Al igual que cualquier variable, el ámbito de una variable de cursor es solo el lote donde se declaró. Esto significa que tan pronto como finaliza un lote, todas las variables caducan. Usando una variable de cursor, una vez que finaliza un lote, SQL Server lo cierra y lo desasigna automáticamente, ahorrándole la necesidad de ejecutar el comando CERRAR y DESASIGNAR explícitamente.

Aquí está el código revisado usando una variable de cursor esta vez:

DECLARE @dbname AS sysname, @C AS CURSOR; SET @C = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN @C; FETCH NEXT FROM @C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM @C INTO @dbname;END;

Siéntase libre de ejecutarlo varias veces y observe que esta vez no se produce ningún error. Es simplemente más limpio, y no tiene que preocuparse por mantener los recursos del cursor si olvidó cerrar y desasignar el cursor.

COMBINAR con SALIDA

Desde el inicio de la cláusula OUTPUT para instrucciones de modificación en SQL Server 2005, resultó ser una herramienta muy práctica siempre que se deseara devolver datos de filas modificadas. Las personas usan esta función regularmente para fines como archivar, auditar y muchos otros casos de uso. Una de las cosas molestas de esta característica, sin embargo, es que si la usa con instrucciones INSERT, solo puede devolver datos de las filas insertadas, prefijando las columnas de salida con insert. No tiene acceso a las columnas de la tabla de origen, aunque a veces necesita devolver columnas del origen junto a columnas del destino.

Como ejemplo, considere las tablas T1 y T2, que crea y rellena ejecutando el siguiente código:

DROP TABLE IF EXISTS dbo.T1, dbo.T2;GO CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); CREATE TABLE dbo.T2(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(datacol) VALUES('A'),('B'),('C'),('D'),('E'),('F');

Observe que una propiedad de identidad se utiliza para generar las claves en ambas tablas.

Supongamos que necesita copiar algunas filas de T1 a T2; digamos, aquellas en las que keycol % 2 = 1. Desea utilizar la cláusula OUTPUT para devolver las claves recién generadas en T2, pero también desea devolver junto a esas claves las claves de origen respectivas de T1. La expectativa intuitiva es usar la siguiente instrucción INSERT:

INSERT INTO dbo.T2(datacol) OUTPUT T1.keycol AS T1_keycol, inserted.keycol AS T2_keycol SELECT datacol FROM dbo.T1 WHERE keycol % 2 = 1;

por Desgracia, aunque, como se ha mencionado, la SALIDA de la cláusula no permite hacer referencia a columnas de la tabla de origen, por lo que obtendrá el siguiente error:

Muchas personas no se dan cuenta de que curiosamente esta limitación no se aplica a la instrucción MERGE. Así que, aunque es un poco incómodo, puede convertir su instrucción INSERT en una instrucción MERGE, pero para hacerlo, necesita que el predicado MERGE sea siempre falso. Esto activará la cláusula WHEN NOT MATCHED y aplicará la única acción de inserción admitida allí. Puede usar una condición falsa ficticia, como 1 = 2. Aquí la completa convertido código:

MERGE INTO dbo.T2 AS TGTUSING (SELECT keycol, datacol FROM dbo.T1 WHERE keycol % 2 = 1) AS SRC ON 1 = 2WHEN NOT MATCHED THEN INSERT(datacol) VALUES(SRC.datacol)OUTPUT SRC.keycol AS T1_keycol, inserted.keycol AS T2_keycol;

Esta vez el código se ejecuta correctamente, obteniéndose el siguiente resultado:

T1_keycol T2_keycol----------- -----------1 13 25 3