SQL Server archivos - El Rincón de Cesar

Funcion JSON_MODIFY() en SQL Server

15 de enero de 202314 de enero de 2023 Cesar Perez

Hola compañeros,

La función JSON_MODIFY() en SQL Server se utiliza para actualizar valores específicos dentro de un valor JSON. Esta función toma tres argumentos: el valor JSON al que se desea hacer la modificación, el camino al fragmento que se desea actualizar y el nuevo valor.

Por ejemplo, si tenemos una tabla clientes con una columna datos de tipo JSON que contiene información sobre los clientes, como su nombre, dirección y teléfono, y deseamos actualizar la dirección de un cliente específico, podemos utilizar JSON_MODIFY() de la siguiente manera:

UPDATE clientes
SET datos = JSON_MODIFY(datos, '$.direccion', 'Nueva dirección')
WHERE id = 1;

UPDATE clientes

SET datos = JSON_MODIFY(datos, '$.direccion', 'Nueva dirección')

WHERE id = 1;

En este caso se esta utilizando JSON_MODIFY para actualizar la dirección del cliente con id 1.

Además de actualizar valores existentes, también podemos utilizar JSON_MODIFY() para agregar nuevas propiedades a un valor JSON. Por ejemplo, si queremos agregar un correo electrónico a la información de un cliente específico, podemos utilizar la siguiente consulta:

UPDATE clientes
SET datos = JSON_MODIFY(datos, '$.correo', 'cliente@ejemplo.com')
WHERE id = 1;

UPDATE clientes

SET datos = JSON_MODIFY(datos, '$.correo', 'cliente@ejemplo.com')

WHERE id = 1;

Es importante tener en cuenta que, al utilizar JSON_MODIFY() se sobreescriben los valores existentes, si deseas agregar una propiedad y no sobreescribir alguna existente, es recomendable hacer uso de la función JSON_MERGEPATCH()

En resumen, la función JSON_MODIFY() es una herramienta útil para actualizar valores específicos dentro de un valor JSON en SQL Server. Con su uso se pueden realizar actualizaciones precisas y específicas en los datos almacenados en formato JSON en SQL Server.

Funcion JSON_QUERY() en SQL Server

14 de enero de 202314 de enero de 2023 Cesar Perez

Hola a tod@s,

La función JSON_QUERY() en SQL Server se utiliza para extraer un fragmento específico de un valor JSON en forma de texto. A diferencia de JSON_VALUE(), que solo devuelve el valor de una propiedad específica, JSON_QUERY() puede extraer un sub-árbol completo de un valor JSON.

Para utilizar JSON_QUERY(), se le proporciona el valor JSON como primer argumento y el camino al fragmento deseado como segundo argumento. El camino al fragmento se especifica utilizando el formato de notación de punto (.) para acceder a las propiedades del objeto JSON.

Por ejemplo, si tenemos una tabla clientes con una columna datos de tipo JSON que contiene información sobre los clientes, como su nombre, dirección y teléfono, podríamos utilizar JSON_QUERY() para extraer solo la información de contacto de cada cliente:

SELECT id, JSON_QUERY(datos, '$.telefono') as telefono, JSON_QUERY(datos, '$.direccion') as direccion
FROM clientes;

1 2	SELECT id, JSON_QUERY(datos, '$.telefono') as telefono, JSON_QUERY(datos, '$.direccion') as direccion FROM clientes;

n este caso se esta utilizando JSON_QUERY para extraer solo la dirección y el teléfono de cada cliente y seleccionarlo en la tabla final.

Además de JSON_QUERY(), también podemos utilizar la función JSON_EXISTS() para verificar si un fragmento específico existe en un valor JSON. Esta función toma dos argumentos: el valor JSON y el camino al fragmento que se desea verificar. Por ejemplo, si queremos verificar si un cliente tiene un número de teléfono específico, podemos utilizar la siguiente consulta:

SELECT id, nombre
FROM clientes
WHERE JSON_EXISTS(datos, '$.telefono' AND datos.telefono='+34657687908');

SELECT id, nombre

FROM clientes

WHERE JSON_EXISTS(datos, '$.telefono' AND datos.telefono='+34657687908');

En resumen, la función JSON_QUERY() es una herramienta útil para extraer fragmentos específicos de un valor JSON en SQL Server, mientras que JSON_EXISTS() se utiliza para verificar si un fragmento específico existe en un valor JSON. Con el uso de ambas funciones, se pueden crear consultas más específicas y precisas sobre los datos almacenados en formato JSON en SQL Server.

Espero que os valga, saludos..

Campo JSON en SQL Server

14 de enero de 202314 de enero de 2023 Cesar Perez

Hola compañeros !!

SQL Server 2016 y versiones posteriores incluyen un nuevo tipo de datos JSON para almacenar y manejar datos en formato JSON. Para crear una columna de tipo JSON, se utiliza el tipo de datos nvarchar(max) con la especificación FOR JSON en la cláusula AS. Por ejemplo, para crear una tabla con una columna de tipo JSON se utilizaría el siguiente comando SQL:

CREATE TABLE tabla_json (id INT, datos_json NVARCHAR(MAX) AS JSON);

1	CREATE TABLE tabla_json (id INT, datos_json NVARCHAR(MAX) AS JSON);

Una vez creada la tabla, se pueden insertar datos en formato JSON utilizando la cláusula VALUES y encerrando el valor JSON en comillas dobles. Por ejemplo:

INSERT INTO tabla_json (id, datos_json) VALUES (1, '{"nombre": "Juan", "edad": 30}');

1	INSERT INTO tabla_json (id, datos_json) VALUES (1, '{"nombre": "Juan", "edad": 30}');

Para acceder a los valores contenidos en un campo JSON se utiliza la función JSON_VALUE(). Esta función toma dos argumentos: el primer argumento es el valor JSON y el segundo argumento es el camino a la propiedad específica que se desea obtener. Por ejemplo, para obtener el valor de la propiedad “nombre” en el ejemplo anterior se utilizaría la siguiente consulta SQL:

SELECT JSON_VALUE(datos_json, '$.nombre') FROM tabla_json WHERE id = 1;

1	SELECT JSON_VALUE(datos_json, '$.nombre') FROM tabla_json WHERE id = 1;

Además de JSON_VALUE(), existen otras funciones para trabajar con datos JSON en SQL Server, como JSON_QUERY(), JSON_MODIFY() y ISJSON(). Es importante tener en cuenta que el uso de estas funciones requiere un conocimiento previo del formato y estructura de los datos JSON que se están manejando.

Diferencias entre @@identity y Scope_identity

28 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a todos,

SQL Server ofrece al menos 3 formas de obtener el último ID insertado. Aunque puedan parecer iguales, no son equivalentes y conocer sus diferencias es muy importante:

@@Identity: devuelve el último ID en la misma conexión.
Scope_identity(): la que recomendamos, devuelve el último ID creado en la misma conexión y el mismo contexto. En este caso el contexto se refiere a la consulta o procedimiento almacenado actual.
ident_current(nombre): devuelve el último ID de la tabla que le indiquemos (el que haya en ese momento)

En la mayor parte de los casos @@identity y scope_identity() se comportan igual, pero no siempre es así.

Por ejemplo, si tenemos un disparador (trigger) en nuestra tabla y lanzamos una consulta de inserción que genera un nuevo registro y por lo tanto un nuevo ID, si el trigger a su vez genera otro registro en otra tabla (y probablemente otro ID en ésta), en esta situación @@identity nos devolverá el ID del registro que ha insertado el disparador, no el ID de la tabla que hemos actualizado (lo cual no es lo que queremos casi nunca), ya que te devuelve el último que se haya generado en esa conexión, tal y comentábamos en la lista anterior. Sin embargo, en este ejemplo, scope_identity() nos devuelve el identificador que esperábamos (el de la inserción en nuestra tabla) ya que siempre devuelve el del contexto actual, que en este caso es nuestra consulta de inserción.

Yo os recomiendo el uso de scope_identity().

Saludos

Paginado de Consulta en SQL Server

7 de abril de 20211 de noviembre de 2021 Cesar Perez

Buenos dias,

Turno de otro script de SQL Server, con el que podemos paginar los resultados de una sentencia select, es muy sencillo de usar, para ello necesitamos dos variables:

DECLARE @Pagina AS INT = 1;
DECLARE @SizePagina AS INT = 100;

1 2	DECLARE @Pagina AS INT = 1; DECLARE @SizePagina AS INT = 100;

Con la variable @Pagina, estamos indicando el numero de pagina que queremos consultar. Y la variable @SizePagina nos indica el tamaño de la pagina, es decir, el numero de registros por pagina.

Con esta sentencia tendriamos el resultado:

SELECT *
FROM tbTabla
ORDER BY Campos
OFFSET (@Pagina- 1) * @SizePagina ROWS FETCH NEXT @SizePagina ROWS ONLY;

SELECT *

FROM tbTabla

ORDER BY Campos

OFFSET (@Pagina- 1) * @SizePagina ROWS FETCH NEXT @SizePagina ROWS ONLY;

Sencillo pero muy practico, espero que os valga.

Objetos modificados en SQL Server

7 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@s,

Os dejo por aqui otro script muy bueno para saber que objetos de la base de datos se han modificado en 10 dias atras.

SELECT 
   name AS object_name,
   SCHEMA_NAME(schema_id) AS schema_name,
   type_desc,
   create_date,
   modify_date
FROM 
   sys.objects
WHERE 
   modify_date > GETDATE() - 10
ORDER BY 
   modify_date;

SELECT

name AS object_name,

SCHEMA_NAME(schema_id) AS schema_name,

type_desc,

create_date,

modify_date

FROM

sys.objects

WHERE

modify_date > GETDATE() - 10

ORDER BY

modify_date;

Si quieres tener otro rango de dias, simplemente cambia del script el valor de 10 por el numero de dias que quieras mostrar.

Informacion Registros y Tamaño de Tablas en SQL Server

6 de abril de 20211 de noviembre de 2021 Cesar Perez

Buenos dias a tod@os,

Hoy os traigo otro pequeño script para SQL Server, con el que podreis sacar bastante informacion de tablas, tales como tamaños, numero de registros, etc…

SELECT
   t.NAME AS NombreTabla,
   s.Name AS Esquema,
   p.rows AS NumFilas,
   SUM(a.total_pages) * 8 AS EspacioTotal_KB,
   SUM(a.used_pages) * 8 AS EspacioUsado_KB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacioNoUsado_KB
FROM
   sys.tables t
     INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
     INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
   t.is_ms_shipped = 0
     AND i.OBJECT_ID > 255
GROUP BY
   t.Name, s.Name, p.Rows
ORDER BY
   p.rows desc, t.NAME, s.Name desc

SELECT

t.NAME AS NombreTabla,

s.Name AS Esquema,

p.rows AS NumFilas,

SUM(a.total_pages) * 8 AS EspacioTotal_KB,

SUM(a.used_pages) * 8 AS EspacioUsado_KB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacioNoUsado_KB

FROM

sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

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

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

p.rows desc, t.NAME, s.Name desc

El resultado de esta consulta seria algo como esto:

Como veis se puede obtener dicha informacion de forma rapida, os recomiendo que mires las tablas de las que se esta estrayendo la informacion, ya que podeis obtener mucha mas.

SPLIT en SQL Server

6 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@os,

Momento de traeros otro pequeño script, que os puede venir muy bien a la hora de devolver en modo registros los valores de un campo.

Para poder realizar esta tarea, lo primero que hay que revisar, es la version del servidor SQL y preparar la base de datos, ya que para que la funcion que os traigo “STRING_SPLIT” funcione, el servidor de SQL debe de ser una version SQL Server 2016 o superior.

En el caso de que sea la version que tengas, la compatibilidad de la base de datos, debe de ser la 130, te dejo aqui un pequeño script para poder revisarla y en el caso de tener que cambiarla tambien te pongo el script de actualización.

Averiguar que compatibilidad tiene la base de datos, simplemente cambia “NombreDeBaseDatos”, por el nombre de tu base de datos:

SELECT compatibility_level
FROM sys.databases WHERE name = 'NombreDeBaseDatos';

1 2	SELECT compatibility_level FROM sys.databases WHERE name = 'NombreDeBaseDatos';

Actualizar la compatibilidad de la base de datos, simplemente cambia “NombreDeBaseDatos”, por el nombre de tu base de datos:

ALTER DATABASE NombreDeBaseDatos
SET COMPATIBILITY_LEVEL = 130;
GO

ALTER DATABASE NombreDeBaseDatos

SET COMPATIBILITY_LEVEL = 130;

Y por ultimo, os dejo la sentencia que transformara los valores de un campo en registros, para ello tenemos que establecer el valor por el que va a separar la informacion, en este caso “;”

SELECT value
FROM tbTabla
CROSS APPLY STRING_SPLIT(CampoTabla, ';')

SELECT value

FROM tbTabla

CROSS APPLY STRING_SPLIT(CampoTabla, ';')

Espero que te valga, a mi me salvo en alguna…

Encriptar cadena en MD5 en SQL Server

5 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola de nuevo,

Otra pequeña aportación, en este caso, para poder generar un MD5 directamente en SQL Server, para ello con esta simple linea podremos tener el resultado, solo tienes que sustituir tu cadena donde pone “elrincondecesar.com”. Como ves es muy sencilla, la funcion de sql fn_varbintohexstr, nos permite obtenerla con esa simple linea.

SELECT SUBSTRING(
            master.dbo.fn_varbintohexstr(HashBytes('MD5','elrincondecesar.com')),3,32)

--Cadena obtenida: 694a6f3e6ec1e316422b496f995a10dc

SELECT SUBSTRING(

master.dbo.fn_varbintohexstr(HashBytes('MD5','elrincondecesar.com')),3,32)

--Cadena obtenida: 694a6f3e6ec1e316422b496f995a10dc

Si quieres ver como se hace en .NET, aqui te pongo el ejemplo que pusimos hace unos dias.

Saludos,

Convertir Filas en Columnas

5 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@s,

En esta ocasión os voy a traer un pequeño script el cual es muy util para poder tener en una columna, los valores de los diferentes registros.

La tabla de origen tiene estos registros.

Ejecutamos el siguiente script:

SELECT CAST(';' AS NVARCHAR(10)) + STUFF
((SELECT CAST(';' AS NVARCHAR(10)) + CAST(tb.ColumnaValor AS NVARCHAR(25))
	FROM            tbValoresFila tb
	ORDER BY tb.ColumnaValor FOR XML PATH('')), 1, 1, '') + CAST(';' AS NVARCHAR(10))

SELECT CAST(';' AS NVARCHAR(10)) + STUFF

((SELECT CAST(';' AS NVARCHAR(10)) + CAST(tb.ColumnaValor AS NVARCHAR(25))

FROM tbValoresFila tb

ORDER BY tb.ColumnaValor FOR XML PATH('')), 1, 1, '') + CAST(';' AS NVARCHAR(10))

El resultado seria el siguiente:

Como veis puede ser muy util, cuando necesitemos tener los valores de varios registros en una sola columna.