Mes: junio 2024

Procedimientos Almacenados Temporales en SQL Server

Script para saber el histórico de queries ejecutados SQL

Generando Script de creación de Usuarios en SQL Server

¿Qué hace DBCC CHECKDB?

UPDATE JOIN en SQL para Actualizar Tablas Relacionadas

En este tutorial, aprenderás a usar la instrucción UPDATE JOIN en SQL Server para realizar actualizaciones entre tablas relacionadas. Esta técnica es útil cuando necesitas actualizar datos en una tabla basándote en la información de otra tabla.

Sintaxis de UPDATE JOIN en SQL Server

Para consultar datos de tablas relacionadas, a menudo se utilizan las cláusulas de unión, ya sea unión interna (INNER JOIN) o unión izquierda (LEFT JOIN). En SQL Server, puedes usar estas cláusulas en la instrucción UPDATE para realizar una actualización entre tablas.

A continuación se muestra la sintaxis de la cláusula UPDATE JOIN:

UPDATE
    t1
SET
    t1.c1 = t2.c2,
    t1.c2 = expression,
    ...
FROM
    t1
[INNER | LEFT] JOIN t2 ON join_predicate
WHERE
    where_predicate;

Desglose de la Sintaxis

UPDATE t1: Especifica el nombre de la tabla (t1) que deseas actualizar.
SET t1.c1 = t2.c2, …: Define los nuevos valores para las columnas de la tabla que se actualiza.
FROM t1 [INNER | LEFT] JOIN t2 ON join_predicate: Especifica la tabla desde la cual deseas actualizar (t2) y cómo se unen las tablas (usando INNER JOIN o LEFT JOIN).
WHERE where_predicate: (Opcional) Añade condiciones para filtrar las filas que serán actualizadas.

Ejemplos de UPDATE JOIN en SQL Server

Vamos a ver algunos ejemplos prácticos sobre cómo usar UPDATE JOIN.

Configuración de Tablas de Ejemplo

Primero, crearemos una nueva tabla llamada sales.targets para almacenar los objetivos de ventas:

DROP TABLE IF EXISTS sales.targets;

CREATE TABLE sales.targets (
    target_id INT PRIMARY KEY,
    percentage DECIMAL(4, 2) NOT NULL DEFAULT 0
);

INSERT INTO sales.targets (target_id, percentage)
VALUES
    (1, 0.2),
    (2, 0.3),
    (3, 0.5),
    (4, 0.6),
    (5, 0.8);

En esta tabla, si el personal de ventas alcanza el objetivo 1, obtendrán una proporción del 20% de comisión de ventas, y así sucesivamente.

Luego, creamos otra tabla llamada sales.commissions para almacenar las comisiones de ventas:

CREATE TABLE sales.commissions (
    staff_id INT PRIMARY KEY,
    target_id INT,
    base_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    commission DECIMAL(10, 2) NOT NULL DEFAULT 0,
    FOREIGN KEY (target_id) REFERENCES sales.targets (target_id),
    FOREIGN KEY (staff_id) REFERENCES sales.staffs (staff_id)
);

INSERT INTO sales.commissions (staff_id, base_amount, target_id)
VALUES
    (1, 100000, 2),
    (2, 120000, 1),
    (3, 80000, 3),
    (4, 900000, 4),
    (5, 950000, 5);

La tabla sales.commissions almacena la identificación del personal de ventas, target_id, base_amount y commission. Esta tabla se enlaza con la tabla sales.targets a través de la columna target_id.

Nuestro objetivo es calcular las comisiones de todo el personal de ventas en función de sus objetivos de ventas.

Ejemplo de UPDATE INNER JOIN

La siguiente declaración utiliza UPDATE INNER JOIN para calcular la comisión de ventas para todo el personal de ventas:

UPDATE
    sales.commissions
SET
    sales.commissions.commission = c.base_amount * t.percentage
FROM
    sales.commissions c
INNER JOIN sales.targets t ON c.target_id = t.target_id;

Aquí está el resultado después de ejecutar la instrucción:

sqlCopiar código(5 rows affected)

Si vuelves a consultar la tabla sales.commissions, verás que los valores de la columna de comisión están actualizados:

sqlCopiar códigoSELECT *
FROM sales.commissions;

Ejemplo de UPDATE LEFT JOIN

Supongamos que tenemos dos nuevos vendedores que se acaban de incorporar y aún no tienen ningún objetivo:

INSERT INTO sales.commissions (staff_id, base_amount, target_id)
VALUES
    (6, 100000, NULL),
    (7, 120000, NULL);

Si suponemos que la comisión para el nuevo personal de ventas es del 10%, podemos actualizar la comisión de todo el personal de ventas de la siguiente manera usando UPDATE LEFT JOIN:

UPDATE
    sales.commissions
SET
    sales.commissions.commission = c.base_amount * COALESCE(t.percentage, 0.1)
FROM
    sales.commissions c
LEFT JOIN sales.targets t ON c.target_id = t.target_id;

En este ejemplo, usamos COALESCE() para devolver 0.1 si el porcentaje es NULL.

Si utilizas la cláusula UPDATE INNER JOIN, solo se actualizarán las filas de la tabla cuyos objetivos no sean NULL.

Examinemos los datos de la tabla sales.commissions:

SELECT *
FROM sales.commissions;

El conjunto de resultados es el siguiente:

staff_id | target_id | base_amount | commission
---------+-----------+-------------+------------
1        | 2         | 100000.00   | 30000.00
2        | 1         | 120000.00   | 24000.00
3        | 3         | 80000.00    | 40000.00
4        | 4         | 900000.00   | 540000.00
5        | 5         | 950000.00   | 760000.00
6        | NULL      | 100000.00   | 10000.00
7        | NULL      | 120000.00   | 12000.00

Como puedes ver, los valores de la columna commission están actualizados, incluyendo los nuevos vendedores con una comisión del 10%.

Ahora que hemos cubierto los conceptos básicos y algunos ejemplos prácticos de UPDATE JOIN en SQL Server, profundicemos un poco más en algunas consideraciones adicionales y usos avanzados de esta poderosa técnica.

Consideraciones Adicionales

Uso de Funciones Agregadas y Subconsultas

Además de simplemente asignar valores directamente en la cláusula SET, puedes utilizar funciones agregadas y subconsultas para calcular los valores que se actualizarán. Por ejemplo:

UPDATE
    sales.commissions
SET
    sales.commissions.commission = c.base_amount * (
        SELECT TOP 1 percentage
        FROM sales.targets t
        WHERE t.target_id = c.target_id
    );

En este caso, estamos calculando la comisión basada en el porcentaje de ventas objetivo específico para cada fila en sales.commissions.

Actualización Condicional

Puedes utilizar la cláusula WHERE de manera efectiva para aplicar condiciones adicionales a las actualizaciones. Por ejemplo, puedes actualizar solo las filas que cumplen ciertas condiciones:

UPDATE
    sales.commissions
SET
    sales.commissions.commission = c.base_amount * t.percentage
FROM
    sales.commissions c
INNER JOIN sales.targets t ON c.target_id = t.target_id
WHERE
    c.base_amount > 100000;

Esto actualizará las comisiones solo para los registros en sales.commissions donde base_amount es mayor que 100,000.

Usos Avanzados de UPDATE JOIN

Actualización Cruzada

A veces, necesitas actualizar una tabla basada en una relación cruzada entre múltiples tablas. Esto se puede lograr utilizando múltiples cláusulas JOIN en la misma instrucción UPDATE:

UPDATE
    table1
SET
    table1.column1 = table2.column2
FROM
    table1
INNER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table2.id = table3.id
WHERE
    table3.condition = 'some_condition';

Esto es útil cuando necesitas actualizar una tabla basada en datos de múltiples fuentes relacionadas.

Actualización de Grandes Conjuntos de Datos

Cuando trabajas con grandes volúmenes de datos, es importante considerar el impacto de las actualizaciones. Asegúrate de indexar adecuadamente las columnas involucradas en las cláusulas JOIN y WHERE para mejorar el rendimiento de las consultas de actualización.

Ejemplos Avanzados de UPDATE JOIN en SQL Server

1. Actualización con JOIN y Subconsulta

En este ejemplo, vamos a actualizar la tabla sales.commissions basándonos en una subconsulta que obtiene el porcentaje de ventas objetivo (percentage) de la tabla sales.targets para cada registro de ventas:

UPDATE
    sales.commissions
SET
    commission = base_amount * (
        SELECT TOP 1 percentage
        FROM sales.targets t
        WHERE t.target_id = sales.commissions.target_id
    )
WHERE
    EXISTS (
        SELECT 1
        FROM sales.targets t
        WHERE t.target_id = sales.commissions.target_id
    );

En este caso:

Utilizamos una subconsulta dentro de la cláusula SET para obtener el percentage específico de ventas objetivo para cada fila en sales.commissions.
La cláusula WHERE EXISTS asegura que solo actualicemos las filas en sales.commissions que tienen un target_id correspondiente en sales.targets.

2. Actualización Basada en Múltiples Condiciones

Supongamos que queremos actualizar las comisiones en sales.commissions dependiendo de múltiples condiciones, como el tipo de producto vendido y la región del vendedor:

UPDATE
    sales.commissions
SET
    commission = base_amount * t.percentage
FROM
    sales.commissions c
INNER JOIN sales.targets t ON c.target_id = t.target_id
INNER JOIN sales.products p ON c.product_id = p.product_id
INNER JOIN sales.regions r ON c.region_id = r.region_id
WHERE
    p.product_type = 'Electronics'
    AND r.region_name = 'North';

En este ejemplo:

Se utilizan múltiples cláusulas INNER JOIN para unir sales.commissions con sales.targets, sales.products, y sales.regions.
La actualización se condiciona en que el producto vendido sea del tipo ‘Electronics’ y que el vendedor esté en la región ‘North’.

3. Actualización con JOIN y COALESCE

En algunos casos, es posible que necesitemos manejar valores nulos o no existentes durante la actualización. Aquí mostramos cómo usar COALESCE para manejar esos casos:

UPDATE
    sales.commissions
SET
    commission = base_amount * COALESCE(t.percentage, 0.1)
FROM
    sales.commissions c
LEFT JOIN sales.targets t ON c.target_id = t.target_id;

COALESCE(t.percentage, 0.1) asegura que si t.percentage es NULL, se usará un valor predeterminado de 0.1 (que representa el 10% de comisión).

4. Actualización Cruzada entre Múltiples Tablas

En situaciones donde necesitamos actualizar datos basados en relaciones cruzadas entre más de dos tablas, podemos hacerlo utilizando múltiples cláusulas JOIN:

UPDATE
    table1
SET
    table1.column1 = table2.column2
FROM
    table1
INNER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table2.id = table3.id
WHERE
    table3.condition = 'some_condition';

Aquí, table1 se actualiza basándose en datos de table2 y table3 donde table3.condition cumple con una condición específica.

Consideraciones Adicionales

Índices y Rendimiento: Asegúrate de tener índices adecuados en las columnas que participan en las cláusulas JOIN y WHERE para mejorar el rendimiento de las actualizaciones, especialmente en grandes conjuntos de datos.
Transacciones: Si la actualización afecta a múltiples tablas o tiene un impacto significativo, considera envolver la operación en una transacción para garantizar la consistencia de los datos.

Conclusión

En este tutorial, has aprendido cómo utilizar la instrucción UPDATE JOIN de SQL Server para realizar actualizaciones entre tablas. Este método es extremadamente útil para mantener datos consistentes y actualizados en bases de datos relacionales, permitiendo actualizaciones basadas en relaciones entre tablas. ¡Ahora es tu turno de probar estos ejemplos y adaptarlos a tus necesidades específicas!

Script para saber el histórico de queries ejecutados SQL

Monitoreo y Mantenimiento SQL: Mantén Tu Base de Datos Saludable

Convertir una Fecha y Hora a Solo Fecha en SQL

¡Hola a todos! En este tutorial, vamos a explorar cómo convertir una fecha y hora a una fecha utilizando las funciones CONVERT(), TRY_CONVERT() y CAST() en SQL. Si estás trabajando con bases de datos y necesitas extraer solo la parte de la fecha de un valor de fecha y hora, este tutorial es para ti. Vamos a cubrir las tres funciones, cómo utilizarlas y cuándo es mejor usar cada una. ¡Vamos a empezar!

¿Por Qué Convertir Fecha y Hora a Solo Fecha?

Antes de sumergirnos en el código, es importante entender por qué querríamos hacer esta conversión. En muchas aplicaciones, la hora exacta no es necesaria y solo necesitamos la fecha. Por ejemplo, si estás analizando registros diarios, realizando reportes mensuales, o simplemente guardando la fecha de nacimiento de alguien, solo la fecha es relevante. SQL nos proporciona varias formas de hacer esta conversión fácilmente.

Usando la Función `CONVERT()`

La función CONVERT() es una de las formas más comunes y flexibles para convertir tipos de datos en SQL. Aquí está la sintaxis básica para convertir una fecha y hora a solo fecha:

CONVERT(DATE, datetime_expression)

Ejemplo de `CONVERT()`

Supongamos que quieres convertir la fecha y hora actual a solo fecha. Puedes utilizar la función GETDATE() que devuelve la fecha y hora actuales del servidor, y luego aplicar CONVERT():

SELECT 
    CONVERT(DATE, GETDATE()) AS date;

El resultado será algo como esto:

códigodate
----------
2024-06-19

En este ejemplo, GETDATE() devuelve la fecha y hora actuales, y CONVERT(DATE, GETDATE()) extrae solo la parte de la fecha.

Usando la Función `TRY_CONVERT()`

La función TRY_CONVERT() es similar a CONVERT(), pero con una diferencia clave: si la conversión falla, en lugar de generar un error, TRY_CONVERT() devuelve NULL. Esto es útil cuando no estás seguro si la conversión será exitosa y quieres evitar errores en tu consulta.

TRY_CONVERT(DATE, datetime_expression)

Ejemplo de `TRY_CONVERT()`

Aquí tienes un ejemplo utilizando TRY_CONVERT() con la fecha y hora actual:

SELECT 
    TRY_CONVERT(DATE, GETDATE()) AS date;

El resultado será el mismo que con CONVERT() si la conversión es exitosa:

códigodate
----------
2024-06-19

Si la conversión fallara (por ejemplo, si datetime_expression no fuera un valor de fecha y hora válido), TRY_CONVERT() devolvería NULL.

Usando la Función `CAST()`

Otra forma de convertir una fecha y hora a solo fecha es utilizando la función CAST(). Esta es una opción más estándar y portable entre diferentes sistemas de bases de datos.

CAST(datetime_expression AS DATE)

Ejemplo de `CAST()`

Aquí hay un ejemplo de cómo utilizar CAST():

SELECT 
    CAST(GETDATE() AS DATE) AS date;

El resultado es el siguiente:

códigodate
----------
2024-06-19

Como puedes ver, CAST() es bastante directo y fácil de usar.

¿Cuál Función Deberías Usar?

La elección entre CONVERT(), TRY_CONVERT() y CAST() depende de tu situación específica:

CONVERT(): Usa esta función si estás trabajando en un entorno de SQL Server y necesitas convertir tipos de datos con flexibilidad. CONVERT() también permite especificar estilos de formato adicionales que pueden ser útiles.
TRY_CONVERT(): Opta por esta función si necesitas manejar conversiones que pueden fallar y quieres evitar errores en tu consulta devolviendo NULL en su lugar.
CAST(): Esta es la opción más estándar y portable. Si estás trabajando con diferentes sistemas de bases de datos y necesitas una solución que funcione en la mayoría de ellos, CAST() es tu mejor opción.

Ejemplos Prácticos y Casos de Uso

Ejemplo 1: Filtrar Registros por Fecha

Imagina que tienes una tabla de orders con una columna order_date que incluye tanto la fecha como la hora. Quieres obtener todos los pedidos realizados en un día específico, digamos el 1 de junio de 2024. Para ello, puedes utilizar cualquiera de las funciones que hemos aprendido para convertir order_date a solo fecha y luego compararla.

Usando `CONVERT()`

SELECT *
FROM orders
WHERE CONVERT(DATE, order_date) = '2024-06-01';

Usando `TRY_CONVERT()`

SELECT *
FROM orders
WHERE TRY_CONVERT(DATE, order_date) = '2024-06-01';

Usando `CAST()`

SELECT *
FROM orders
WHERE CAST(order_date AS DATE) = '2024-06-01';

En estos ejemplos, cada función convierte la columna order_date a solo fecha antes de hacer la comparación. Este enfoque es útil para ignorar la parte de la hora y centrarse solo en la fecha.

Ejemplo 2: Agrupar Datos por Fecha

Supongamos que deseas agrupar tus pedidos diarios y contar cuántos pedidos se realizan cada día. Aquí es donde la conversión de fecha y hora a solo fecha es realmente útil.

Usando `CONVERT()`

SELECT 
    CONVERT(DATE, order_date) AS order_date,
    COUNT(*) AS order_count
FROM orders
GROUP BY CONVERT(DATE, order_date);

Usando `TRY_CONVERT()`

SELECT 
    TRY_CONVERT(DATE, order_date) AS order_date,
    COUNT(*) AS order_count
FROM orders
GROUP BY TRY_CONVERT(DATE, order_date);

Usando `CAST()`

SELECT 
    CAST(order_date AS DATE) AS order_date,
    COUNT(*) AS order_count
FROM orders
GROUP BY CAST(order_date AS DATE);

En estos ejemplos, cada función convierte order_date a solo fecha antes de agrupar, lo que permite contar los pedidos por día de manera efectiva.

Ejemplo 3: Validar Fechas en Datos

Imagina que tienes una tabla de datos importados con una columna de fecha que puede contener valores inválidos. Quieres seleccionar solo los registros donde la conversión de fecha y hora a solo fecha sea exitosa, utilizando TRY_CONVERT() para manejar posibles errores.

SELECT *
FROM imported_data
WHERE TRY_CONVERT(DATE, imported_date) IS NOT NULL;

En este caso, TRY_CONVERT() devuelve NULL para los valores que no pueden ser convertidos a fecha, permitiéndote filtrar los registros válidos fácilmente.

Consideraciones de Rendimiento

Al utilizar funciones de conversión en tus consultas SQL, es importante considerar el impacto en el rendimiento, especialmente si estás trabajando con grandes volúmenes de datos. Aquí hay algunos consejos:

Índices: Cuando conviertes una columna dentro de una cláusula WHERE o GROUP BY, el índice en esa columna puede no ser utilizado de manera eficiente. Si el rendimiento es crítico, considera almacenar las fechas convertidas en una columna separada y mantener índices adecuados en esa columna.
Evaluaciones en Funciones: Las funciones como CONVERT(), TRY_CONVERT() y CAST() se evalúan en tiempo de ejecución, lo que puede agregar una sobrecarga a tus consultas. Opta por conversiones fuera de las consultas críticas de rendimiento cuando sea posible.

Recapitulación

Hemos cubierto cómo y por qué convertir una fecha y hora a solo fecha en SQL utilizando CONVERT(), TRY_CONVERT() y CAST(). Aquí hay un resumen rápido:

CONVERT(): Flexible y permite especificar estilos de formato adicionales.
TRY_CONVERT(): Maneja conversiones fallidas devolviendo NULL.
CAST(): Estandarizada y portable entre diferentes sistemas de bases de datos.

Conclusión:

En este tutorial, has aprendido a utilizar las funciones CONVERT(), TRY_CONVERT() y CAST() para convertir una fecha y hora en una fecha en SQL. Cada una de estas funciones tiene sus propias ventajas y desventajas, y la elección de cuál usar dependerá de tus necesidades específicas.

Espero que este tutorial te haya proporcionado una comprensión clara y práctica sobre cómo convertir fechas y horas a solo fecha en SQL. Estas técnicas son esenciales para el manejo efectivo de datos en aplicaciones de bases de datos y te ayudarán a simplificar y optimizar tus consultas.

Cambiar el collation en un servidor sql server 2019

Generando Script de creación de Usuarios en SQL Server

Cambiar el collation en un servidor sql server 2019

Qué es la temp-db en sql

La-Importancia-del-Transaction-Log-en-SQL-Server-Mas-Alla-del-Mito

¿Qué es el Transaction Log? La Importancia en SQL Server

Cuando comencé a trabajar con bases de datos en SQL Server, una pregunta recurrente surgía en mis conversaciones con expertos: «¿Para qué sirve el Transaction Log?». Sorprendentemente, la respuesta que recibía era casi siempre la misma: «No sé, creo que para nada». Esta respuesta no me parecía correcta. Si este archivo existía, debía tener una función importante. Peor aún, en muchos sistemas en producción, observaba un manejo incorrecto del Transaction Log, como crear trabajos específicos para truncarlo sin entender por qué crecía desproporcionadamente. Estos trabajos simplemente reducían el tamaño del archivo sin más consideración.

El Verdadero Rol del Transaction Log

A lo largo de los años, al adquirir más conocimiento, aprendí que el Transaction Log es vital para mantener un sistema de base de datos saludable. Este archivo registra todas y cada una de las transacciones que se realizan en la base de datos, asegurando las propiedades ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad) de las transacciones.

Cuando se modifica un dato en la base de datos, el primer paso para confirmar la transacción es registrar los cambios en el Transaction Log. Solo después de esto se puede decir que la transacción está completada. Este proceso garantiza la durabilidad de la transacción, ya que si ocurre un problema justo después de hacer «commit», el Transaction Log es el único medio que la base de datos tiene para reproducir los cambios.

Los Peligros de Truncar el Transaction Log

Muchos desarrolladores y analistas solían truncar el Transaction Log para evitar su crecimiento excesivo. Afortunadamente, desde SQL Server 2008, estas prácticas han sido descontinuadas, obligando a un manejo más adecuado del Transaction Log. Truncar el Transaction Log y reducir su tamaño puede degradar el rendimiento del sistema, ya que ninguna transacción puede ser confirmada hasta que el log esté listo para recibir nuevas transacciones. Además, cada vez que el log crece, puede fragmentarse en el disco, afectando aún más la performance.

Manejo del Transaction Log Según el Modelo de Recuperación

El Transaction Log se gestiona según el modelo de recuperación configurado en la base de datos. En futuros posts, exploraremos en detalle estos modelos. Por ahora, es crucial entender que los registros del Transaction Log pueden estar en estado activo o inactivo, permitiendo identificar los registros que ya no son útiles y pueden ser sobrescritos. Si no hay espacio libre o registros inactivos, el log crecerá, lo cual es costoso en términos de rendimiento.

El comportamiento cíclico del Transaction Log permite que, una vez que llega al final del archivo, busque espacio libre al inicio para seguir escribiendo transacciones.

Configuración Óptima del Transaction Log

Para saber el tamaño y las propiedades de crecimiento del Transaction Log, podemos ejecutar el siguiente comando:

sqlCopiar códigoEXECUTE sp_helpfile

El resultado muestra configuraciones como «maxsize» (tamaño máximo) y «growth» (crecimiento automático). Por ejemplo, si el log está configurado para crecer un 10% cada vez que necesita más espacio, esto puede no ser óptimo. Es mejor establecer un valor fijo para el crecimiento del log, evitando un crecimiento frecuente que impacte negativamente en el rendimiento.

Escritura Secuencial del Transaction Log

Una característica clave del Transaction Log es que su método de escritura es secuencial, a diferencia de los archivos de datos que escriben de forma aleatoria según la ubicación de las páginas de datos. Esta secuencialidad permite una escritura más rápida, ya que la aguja del disco duro no necesita moverse aleatoriamente, sino que se desplaza en una dirección hasta el final del archivo antes de aplicar el comportamiento cíclico y regresar al inicio.

Prácticas Recomendadas para Manejar el Transaction Log

Para garantizar un rendimiento óptimo y una administración eficiente del Transaction Log, es crucial seguir algunas prácticas recomendadas:

1. Configurar el Tamaño Inicial Adecuado

Establezca un tamaño inicial adecuado para el Transaction Log, anticipando las necesidades de su base de datos. Esto evitará crecimientos frecuentes y mejorará la performance general del sistema.

2. Configurar el Crecimiento Automático

En lugar de permitir un crecimiento porcentual, configure el Transaction Log para que crezca en incrementos fijos. Esto proporciona un control más preciso y minimiza el impacto en el rendimiento.

ALTER DATABASE [YourDatabaseName] 
MODIFY FILE (NAME = 'YourLogFileName', FILEGROWTH = 512MB);

3. Monitorizar el Transaction Log Regularmente

Use herramientas y scripts para monitorizar el tamaño y el uso del Transaction Log. Esto le ayudará a identificar patrones de uso y a ajustar configuraciones según sea necesario.

DBCC SQLPERF(LOGSPACE);

4. Realizar Copias de Seguridad del Log de Transacciones

Regularmente, haga copias de seguridad del Transaction Log. Esto no solo ayuda a mantener un tamaño manejable del log, sino que también es crucial para la recuperación de la base de datos.

BACKUP LOG [YourDatabaseName] TO DISK = 'path_to_backup_location';

5. Entender y Configurar el Modelo de Recuperación Apropiado

Elija el modelo de recuperación adecuado (Simple, Completo o Bulk-Logged) para su base de datos. Cada modelo tiene implicaciones diferentes para el Transaction Log y el proceso de recuperación.

6. Evitar el Truncamiento Manual del Transaction Log

En lugar de truncar manualmente el Transaction Log, confíe en las estrategias de copia de seguridad y en la gestión adecuada del log. El truncamiento manual puede llevar a problemas de rendimiento y pérdida de datos.

Monitoreo del Transaction Log

Monitorear el Transaction Log de SQL Server es crucial para mantener el rendimiento y la integridad de la base de datos. A continuación, se presentan varios scripts útiles para diferentes aspectos del monitoreo del Transaction Log.

1. Verificar el Uso del Transaction Log

Este script muestra el uso actual del Transaction Log de cada base de datos:

DBCC SQLPERF(LOGSPACE);

2. Consultar Información Detallada del Transaction Log

Obtenga detalles sobre el tamaño del Transaction Log, el espacio usado y otros parámetros importantes:

SELECT 
    db.name AS DatabaseName,
    mf.name AS LogFileName,
    mf.physical_name AS LogFilePath,
    mf.size / 128 AS TotalSizeMB,
    mf.max_size / 128 AS MaxSizeMB,
    mf.growth / 128 AS GrowthMB,
    ls.cntr_value / 1024 AS LogSpaceUsedMB,
    (ls.cntr_value / (mf.size * 8)) * 100 AS LogSpaceUsedPercent
FROM 
    sys.master_files mf
JOIN 
    sys.databases db ON db.database_id = mf.database_id
JOIN 
    sys.dm_os_performance_counters ls ON ls.instance_name = db.name
WHERE 
    mf.type = 1 -- Log files
    AND ls.counter_name = 'Log File(s) Used Size (KB)';

3. Consultar Transacciones Activas

Este script muestra información sobre las transacciones activas en el Transaction Log:

SELECT 
    t.database_id,
    db.name AS DatabaseName,
    t.transaction_id,
    t.transaction_begin_time,
    t.transaction_state,
    t.transaction_status,
    t.transaction_type,
    t.transaction_uow
FROM 
    sys.dm_tran_database_transactions t
JOIN 
    sys.databases db ON t.database_id = db.database_id
WHERE 
    t.database_id = DB_ID('YourDatabaseName');

4. Monitorizar el Crecimiento del Transaction Log

Este script muestra eventos de crecimiento del Transaction Log:

SELECT 
    d.name AS DatabaseName,
    mf.name AS LogFileName,
    mf.physical_name AS LogFilePath,
    mf.size / 128 AS CurrentSizeMB,
    mf.growth / 128 AS GrowthMB,
    mf.max_size / 128 AS MaxSizeMB,
    mf.growth AS GrowthSetting
FROM 
    sys.master_files mf
JOIN 
    sys.databases d ON mf.database_id = d.database_id
WHERE 
    mf.type = 1 -- Log files
ORDER BY 
    d.name;

5. Consultar Fragmentación del Transaction Log

Monitorear la fragmentación del Transaction Log puede ayudar a identificar problemas de rendimiento:

SELECT 
    db.name AS DatabaseName,
    fg.name AS FileGroupName,
    df.name AS LogFileName,
    df.physical_name AS LogFilePath,
    df.size / 128 AS TotalSizeMB,
    df.max_size / 128 AS MaxSizeMB,
    df.growth / 128 AS GrowthMB,
    fg.data_space_id,
    fg.is_default
FROM 
    sys.database_files df
JOIN 
    sys.filegroups fg ON df.data_space_id = fg.data_space_id
JOIN 
    sys.databases db ON df.database_id = db.database_id
WHERE 
    df.type = 1 -- Log files
ORDER BY 
    db.name;

6. Alertas de Uso del Transaction Log

Configure alertas para cuando el uso del Transaction Log exceda un umbral especificado. Primero, cree un operador para recibir alertas:

EXEC msdb.dbo.sp_add_operator 
    @name=N'YourOperatorName', 
    @enabled=1, 
    @email_address=N'your-email@example.com';

Luego, cree una alerta para el uso del Transaction Log:

EXEC msdb.dbo.sp_add_alert 
    @name=N'Transaction Log Usage Alert', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=0, 
    @database_name=N'YourDatabaseName', 
    @notification_message=N'The transaction log usage has exceeded the threshold.', 
    @event_description_keyword=N'', 
    @performance_condition=N'SQLServer:Databases|Percent Log Used|YourDatabaseName|>|80', 
    @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification 
    @alert_name=N'Transaction Log Usage Alert', 
    @operator_name=N'YourOperatorName', 
    @notification_method = 1;

7. Limpieza Automática del Transaction Log

Realice una copia de seguridad del Transaction Log para mantener su tamaño controlado:

sqlCopiar códigoBACKUP LOG [YourDatabaseName] TO DISK = 'path_to_backup_location';

Automatice esta tarea mediante un trabajo de SQL Server Agent:

USE msdb;
GO

EXEC dbo.sp_add_job 
    @job_name = N'Backup Transaction Log for YourDatabaseName'; 
GO

EXEC sp_add_jobstep 
    @job_name = N'Backup Transaction Log for YourDatabaseName', 
    @step_name = N'Backup Log Step', 
    @subsystem = N'TSQL', 
    @command = N'BACKUP LOG [YourDatabaseName] TO DISK = ''path_to_backup_location'';', 
    @retry_attempts = 5, 
    @retry_interval = 5; 
GO

EXEC dbo.sp_add_schedule 
    @job_name = N'Backup Transaction Log for YourDatabaseName', 
    @name = N'Daily Transaction Log Backup', 
    @freq_type = 4, 
    @freq_interval = 1, 
    @active_start_time = 233000; 
GO

EXEC sp_attach_schedule 
    @job_name = N'Backup Transaction Log for YourDatabaseName', 
    @schedule_name = N'Daily Transaction Log Backup'; 
GO

EXEC dbo.sp_add_jobserver 
    @job_name = N'Backup Transaction Log for YourDatabaseName'; 
GO

Estos scripts y prácticas pueden ayudar a asegurar que su Transaction Log esté monitoreado y manejado adecuadamente, contribuyendo al rendimiento y la integridad de su sistema de bases de datos SQL Server.

Conclusión

El Transaction Log es una parte fundamental de SQL Server que garantiza la integridad y durabilidad de las transacciones. Su manejo adecuado es esencial para el rendimiento y la salud de la base de datos. Al seguir prácticas recomendadas y evitar métodos obsoletos, puede asegurar que su base de datos funcione de manera eficiente y confiable.

Generando Script de creación de Usuarios en SQL Server

Generando Script de creación de Usuarios en SQL Server

que son Procedimientos Almacenados Temporales sql

Procedimientos Almacenados Temporales en SQL Server

En el mundo de SQL Server, es común hablar de tablas temporales, pero también existen procedimientos almacenados temporales que son menos conocidos pero igualmente útiles. Estos procedimientos almacenados temporales tienen una vida limitada a la sesión que los crea, lo cual ofrece diversas ventajas. En este artículo, exploraremos cómo crear estos procedimientos y las razones para usarlos en tus proyectos.

Creación de Procedimientos Almacenados Temporales SQL

Procedimientos Almacenados Temporales Locales

Para crear un procedimiento almacenado temporal local, se antepone un numeral # al nombre del procedimiento. Este procedimiento estará disponible solo en la sesión donde se creó. Aquí hay un ejemplo práctico que muestra cómo obtener una lista de tablas y su número de columnas:

CREATE PROCEDURE #spu_ObtenerTablasColumnas
AS
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, COUNT(c.COLUMN_NAME) AS NUMERO_COLUMNAS
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

Procedimientos Almacenados Temporales Globales

Si necesitas que el procedimiento almacenado temporal sea accesible desde múltiples sesiones, puedes crear un procedimiento almacenado temporal global utilizando dos numerales ## antes del nombre. Esto permite que el procedimiento esté disponible para todas las sesiones mientras la sesión que lo creó siga activa.

CREATE PROCEDURE ##spu_ObtenerTablasColumnasGlobal
AS
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, COUNT(c.COLUMN_NAME) AS NUMERO_COLUMNAS
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

Ventajas de Usar Procedimientos Almacenados Temporales

Facilidad de Pruebas

Una de las principales ventajas de los procedimientos almacenados temporales es la facilidad para realizar pruebas. Puedes crear una copia temporal de un procedimiento almacenado existente y modificarlo sin afectar el procedimiento original. Esto es especialmente útil cuando necesitas probar cambios en el código o evaluar el impacto de nuevas optimizaciones.

Sin Necesidad de Permisos Especiales

La creación de procedimientos almacenados temporales no requiere permisos especiales en la base de datos. Esto simplifica el proceso de prueba y desarrollo, permitiendo a los desarrolladores realizar ajustes y pruebas sin necesidad de esperar la aprobación de permisos adicionales.

Optimización del Plan de Ejecución

SQL Server optimiza los planes de ejecución de los procedimientos almacenados basándose en los valores conocidos de los parámetros al momento de la compilación. Al utilizar procedimientos almacenados temporales, puedes aprovechar esta optimización, obteniendo planes de ejecución más eficientes en comparación con la ejecución de consultas con variables cuyos valores no son conocidos hasta el tiempo de ejecución.

Aislamiento de Sesión

Los procedimientos almacenados temporales locales aseguran que las modificaciones y pruebas realizadas no afecten a otros usuarios o sesiones en la base de datos. Esto proporciona un entorno seguro y aislado para realizar experimentos y ajustes sin riesgo de interferir con el trabajo de otros.

Casos de Uso Comunes

Desarrollo y Pruebas

Durante el desarrollo de nuevas funcionalidades o la optimización de procedimientos existentes, los procedimientos almacenados temporales son una herramienta valiosa. Permiten a los desarrolladores iterar rápidamente sobre diferentes versiones de código y realizar pruebas en un entorno controlado.

Solución de Problemas

En situaciones de emergencia donde se necesita aplicar una solución rápida, los procedimientos almacenados temporales permiten implementar cambios temporales sin necesidad de modificar el código fuente de los procedimientos almacenados principales. Esto es útil para aplicar parches rápidos mientras se trabaja en una solución permanente.

Migraciones y Actualizaciones

Durante las migraciones de bases de datos o actualizaciones de sistemas, los procedimientos almacenados temporales pueden ser utilizados para probar nuevas estructuras y datos sin comprometer la integridad de la base de datos actual. Esto facilita la validación de cambios y asegura una transición suave.

Conclusión

Los procedimientos almacenados temporales en SQL Server son una herramienta poderosa y versátil para desarrolladores y administradores de bases de datos. Ofrecen una manera eficiente de realizar pruebas, optimizar código y asegurar que los cambios se implementen de manera controlada y segura. Considera utilizar procedimientos almacenados temporales en tus próximos proyectos para aprovechar sus múltiples beneficios.

Script para saber el histórico de queries ejecutados SQL

DESCARGAR SCRIPT INFORMACION GENERAL

Qué es la tempb en sql y queries para monitoreo

Qué es la temp-db en sql

Tempdb, en el mundo de la gestión de bases de datos relacionales, SQL Server de Microsoft es una de las plataformas más utilizadas. Entre sus componentes fundamentales se encuentra tempdb, una base de datos especial que desempeña un papel crucial en el rendimiento y la gestión de SQL Server. En este artículo, exploraremos en profundidad qué es tempdb, su propósito, cómo se utiliza y algunos consejos prácticos para optimizar su rendimiento.

¿Qué es tempdb?

En SQL Server que se utiliza para almacenar datos temporales, tablas temporales, variables de tabla y otros objetos temporales. A diferencia de otras bases de datos que almacenan datos permanentes, tempdb se crea cada vez que se inicia SQL Server y se elimina cuando se apaga el servidor. Esto significa que su contenido no es persistente y se utiliza principalmente para operaciones temporales durante la ejecución de consultas, procedimientos almacenados, índices y operaciones de clasificación.

Propósito de tempdb

El propósito principal de tempdb es proporcionar un espacio de trabajo temporal para almacenar datos y objetos temporales mientras se ejecutan operaciones dentro de SQL Server. Algunas de las funciones clave de tempdb incluyen:

Tablas temporales: Utilizadas para almacenar datos temporales que solo son necesarios durante la sesión de conexión.
Variables de tabla: Actúan como variables que almacenan conjuntos de datos temporales que pueden ser utilizados en varias operaciones dentro de una conexión.
Almacenamiento de resultados intermedios: Durante la ejecución de consultas complejas, tempdb puede almacenar resultados intermedios y operaciones de clasificación que requieren espacio adicional.

Uso de tempdb en operaciones cotidianas

1. Creación de tablas temporales: Las tablas temporales se crean dentro de tempdb utilizando la sintaxis especial CREATE TABLE #NombreTabla. Estas tablas son visibles solo para la sesión de conexión actual y se eliminan automáticamente cuando la conexión se cierra o se elimina explícitamente.

2. Variables de tabla: Las variables de tabla permiten almacenar conjuntos de datos temporales que pueden ser utilizados en múltiples consultas o procedimientos almacenados dentro de la misma conexión. Se declaran utilizando DECLARE @NombreVariable TABLE.

3. Resultados intermedios y almacenamiento temporal: Durante la ejecución de consultas complejas que requieren operaciones de clasificación o agrupación, tempdb actúa como un espacio de almacenamiento temporal para almacenar resultados parciales antes de devolver el conjunto de resultados final al usuario.

Gestión y optimización de tempdb

Para garantizar un rendimiento óptimo de SQL Server, es crucial gestionar adecuadamente tempdb y optimizar su configuración. A continuación se presentan algunas estrategias clave para optimizar tempdb:

1. Tamaño y crecimiento automático: Configurar el tamaño inicial de tempdb según las necesidades del entorno y habilitar el crecimiento automático para garantizar que tempdb tenga suficiente espacio para manejar operaciones temporales sin interrupciones.

2. Colocación de archivos: Distribuir los archivos de tempdb en múltiples discos físicos para distribuir la carga de E/S y mejorar el rendimiento. Esto puede lograrse mediante la creación de múltiples archivos de datos para tempdb y asignándolos a diferentes unidades de disco.

3. Monitoreo de la actividad: Utilizar herramientas de monitoreo de SQL Server para supervisar la actividad de tempdb y identificar patrones de uso, cuellos de botella o problemas de rendimiento que puedan surgir debido a operaciones temporales intensivas.

4. Uso de instantáneas de base de datos (Database Snapshots): Al implementar instantáneas de base de datos, se puede reducir la carga de tempdb durante operaciones de copia de seguridad y restauración, ya que estas operaciones pueden ser intensivas en términos de recursos temporales.

5. Mantenimiento regular: Realizar mantenimientos regulares en tempdb, como la eliminación de objetos temporales no utilizados, la limpieza de tablas temporales y la revisión de configuraciones de almacenamiento, para mantener un rendimiento óptimo del servidor SQL.

Script 1: Obtener información general sobre tempdb

Este script te ayudará a obtener información básica sobre tempdb, incluyendo el tamaño de los archivos y el espacio utilizado.

USE tempdb;
GO

-- Obtener el tamaño actual de tempdb y espacio utilizado
SELECT
    DB_NAME(database_id) AS [Database Name],
    type_desc AS [File Type],
    name AS [Logical Name],
    size * 8 / 1024 AS [Size MB],
    used_space_pages * 8 / 1024 AS [Used Space MB],
    (size - used_space_pages) * 8 / 1024 AS [Free Space MB]
FROM
    sys.dm_db_file_space_usage;
GO

Script 2: Monitorear actividad actual en tempdb

Este script te permite ver las sesiones y las tareas que están utilizando tempdb en el momento actual.

USE tempdb;
GO

-- Monitorear la actividad actual en tempdb
SELECT
    session_id,
    request_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
FROM
    sys.dm_db_task_space_usage
WHERE
    session_id > 50; -- Filtrar sesiones de usuario (generalmente las sesiones de sistema tienen IDs menores a 50)
GO

DESCARGAR SCRIPT MONITOREO DE ACTIVIDAD

Script 3: Identificar objetos temporales y su uso

Este script te muestra los objetos temporales actuales en tempdb y su tamaño estimado.

USE tempdb;
GO

-- Identificar objetos temporales en tempdb
SELECT
    OBJECT_NAME(object_id) AS [Object Name],
    user_object_id,
    type_desc AS [Type],
    total_pages * 8 / 1024 AS [Size MB]
FROM
    sys.dm_db_session_space_usage
WHERE
    database_id = DB_ID('tempdb');
GO

DESCARGAR SCRIPT OBJETOS TEMPORALES

Script 4: Monitorear el crecimiento de archivos de tempdb

Este script te permite verificar el crecimiento de los archivos de tempdb durante un intervalo de tiempo específico.

USE tempdb;
GO

-- Monitorear el crecimiento de archivos de tempdb
SELECT
    DB_NAME(database_id) AS [Database Name],
    type_desc AS [File Type],
    name AS [Logical Name],
    size_change_kb / 1024 AS [Size Change MB],
    duration_seconds
FROM
    sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL) AS divfs
CROSS APPLY
    sys.dm_io_virtual_file_stats(DB_ID('tempdb'), file_id) AS divfs2
WHERE
    file_id IN (1, 2); -- Archivos de datos y de registro de tempdb respectivamente
GO

DESCARGAR SCRIPT MONITOREO DE TEMAÑO

Consideraciones adicionales:

Programación de tareas: Puedes programar estos scripts para ejecutarse periódicamente (por ejemplo, cada hora) utilizando un trabajo de SQL Server Agent para mantener un monitoreo constante.
Análisis de tendencias: Utiliza los resultados de estos scripts para analizar las tendencias de uso de tempdb y tomar decisiones informadas sobre la capacidad y la optimización.
Ajustes según el entorno: Asegúrate de ajustar los scripts según las configuraciones específicas de tu entorno de SQL Server.

Conclusiones

En resumen, tempdb es un componente esencial en SQL Server que proporciona un espacio de trabajo temporal para almacenar datos y objetos temporales durante la ejecución de consultas y procedimientos almacenados. Su correcta gestión y optimización son fundamentales para garantizar el rendimiento y la estabilidad de SQL Server en entornos de producción. Al comprender su propósito y aplicaciones, así como al implementar prácticas recomendadas para su configuración y mantenimiento, los administradores de bases de datos pueden maximizar la eficiencia operativa de sus sistemas SQL Server.

Cambiar el collation en un servidor sql server 2019

Generando Script de creación de Usuarios en SQL Server

script para ver el historico de queries hechos en sql server

Script para saber el histórico de queries ejecutados SQL

En el mundo del desarrollo de bases de datos, la gestión y optimización de consultas SQL es una tarea fundamental para garantizar el rendimiento y la integridad de los datos. Una práctica recomendada es mantener un script histórico de las queries ejecutadas en SQL. En este artículo, exploraremos las razones por las cuales es necesario conservar este historial y cómo puede beneficiar a tu organización.

1. Auditoría y Cumplimiento

Uno de los motivos más importantes para mantener un historial de consultas SQL es la auditoría. En muchas industrias, existen regulaciones estrictas que requieren un seguimiento detallado de quién accede a la base de datos y qué acciones realizan. Un script histórico proporciona una pista de auditoría completa que puede ser invaluable durante auditorías internas y externas.

2. Resolución de Problemas

Cuando se presenta un problema en la base de datos, ya sea un error o un rendimiento lento, tener un historial de consultas ejecutadas puede ayudar a identificar rápidamente la causa. Los administradores de bases de datos pueden revisar las consultas previas para ver si hubo algún cambio significativo o una consulta ineficiente que esté afectando el rendimiento.

3. Optimización del Rendimiento

El análisis de las consultas históricas permite a los administradores identificar patrones y tendencias en el uso de la base de datos. Con esta información, pueden optimizar las consultas y mejorar el rendimiento general del sistema. Por ejemplo, si una consulta específica se ejecuta frecuentemente y tarda mucho tiempo, se pueden tomar medidas para optimizarla o ajustar los índices de la base de datos.

4. Recuperación de Datos

En situaciones donde se ha producido una pérdida de datos accidental, el historial de consultas puede ser crucial para la recuperación. Al revisar las consultas ejecutadas, es posible identificar qué datos se vieron afectados y restaurarlos a su estado original.

5. Capacitación y Mejora Continua

Para equipos de desarrollo y administración de bases de datos, el historial de consultas es una herramienta de aprendizaje. Los nuevos miembros del equipo pueden estudiar las consultas previas para entender las mejores prácticas y los estándares de la organización. Además, permite una revisión continua y mejoras en las prácticas de escritura de consultas SQL.

6. Documentación

Mantener un historial de consultas ejecutadas también sirve como documentación viva del uso de la base de datos. Esto puede ser útil no solo para los administradores, sino también para otros stakeholders que necesiten entender cómo se está utilizando la base de datos.

Script para ver el histórico de queries lanzados en sql (Insert)

select  top(100)
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time/1000 as CPU,
        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
        qs.total_elapsed_time/1000 as TotDuration,
        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
        total_logical_reads as [Reads],
        total_logical_writes as [Writes],
        total_logical_reads+total_logical_writes as [AggIO],
        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
        [sql_handle],
        plan_handle,
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        total_physical_reads,
        convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
        convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
        convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
        query_hash,
        query_plan_hash,
        total_rows,
        convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
        total_dop,
        convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
        total_grant_kb,
        convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
        total_used_grant_kb,
        convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
        total_ideal_grant_kb,
        convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
        total_reserved_threads,
        convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
        total_used_threads,
        convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid) as database_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
        sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%insert%'
order by creation_time desc

DESCARGAR SCRIPT PARA VER HISTORICO DE QUERIES LANZADOS EN SQL

Explicación de la Consulta anterior que muestra el histórico de queries en SQL

Consulta SQL que sirve para monitorizar y obtener estadísticas detalladas sobre consultas que contienen la palabra «INSERT«. Este tipo de análisis es fundamental para identificar y optimizar el rendimiento de las operaciones de inserción en una base de datos.

Desglose de la Consulta

Selección de los Primeros 100 Registros

TOP(100)

La consulta selecciona los primeros 100 registros de los datos que cumple con las condiciones especificadas.

Campos y Cálculos

A continuación, se seleccionan varios campos y se realizan ciertos cálculos para proporcionar métricas detalladas sobre el rendimiento de las consultas:

creation_time,
last_execution_time,
execution_count,
total_worker_time / 1000 AS CPU,
CONVERT(money, (total_worker_time)) / (execution_count * 1000) AS [AvgCPUTime],
qs.total_elapsed_time / 1000 AS TotDuration,
CONVERT(money, (qs.total_elapsed_time)) / (execution_count * 1000) AS [AvgDur],
total_logical_reads AS [Reads],
total_logical_writes AS [Writes],
total_logical_reads + total_logical_writes AS [AggIO],
CONVERT(money, (total_logical_reads + total_logical_writes) / (execution_count + 0.0)) AS [AvgIO],
[sql_handle],
plan_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
total_physical_reads,
CONVERT(money, total_physical_reads / (execution_count + 0.0)) AS [AvgIOPhysicalReads],
CONVERT(money, total_logical_reads / (execution_count + 0.0)) AS [AvgIOLogicalReads],
CONVERT(money, total_logical_writes / (execution_count + 0.0)) AS [AvgIOLogicalWrites],
query_hash,
query_plan_hash,
total_rows,
CONVERT(money, total_rows / (execution_count + 0.0)) AS [AvgRows],
total_dop,
CONVERT(money, total_dop / (execution_count + 0.0)) AS [AvgDop],
total_grant_kb,
CONVERT(money, total_grant_kb / (execution_count + 0.0)) AS [AvgGrantKb],
total_used_grant_kb,
CONVERT(money, total_used_grant_kb / (execution_count + 0.0)) AS [AvgUsedGrantKb],
total_ideal_grant_kb,
CONVERT(money, total_ideal_grant_kb / (execution_count + 0.0)) AS [AvgIdealGrantKb],
total_reserved_threads,
CONVERT(money, total_reserved_threads / (execution_count + 0.0)) AS [AvgReservedThreads],
total_used_threads,
CONVERT(money, total_used_threads / (execution_count + 0.0)) AS [AvgUsedThreads],

Explicación de Campos Seleccionados

creation_time: Tiempo de creación de la consulta.
last_execution_time: Última vez que se ejecutó la consulta.
execution_count: Número de veces que se ha ejecutado la consulta.
total_worker_time / 1000 AS CPU: Tiempo total de CPU utilizado, convertido a segundos.
AvgCPUTime: Tiempo promedio de CPU por ejecución.
TotDuration: Duración total de la consulta en milisegundos.
AvgDur: Duración promedio por ejecución.
Reads: Lecturas lógicas totales.
Writes: Escrituras lógicas totales.
AggIO: I/O agregado (lecturas + escrituras).
AvgIO: I/O promedio por ejecución.
sql_handle, plan_handle: Identificadores de la consulta y del plan de ejecución.
statement_start_offset, statement_end_offset: Posiciones de inicio y fin de la consulta dentro del batch.
plan_generation_num: Número de veces que se ha generado el plan de ejecución.
total_physical_reads: Lecturas físicas totales.
AvgIOPhysicalReads: Lecturas físicas promedio por ejecución.
AvgIOLogicalReads: Lecturas lógicas promedio por ejecución.
AvgIOLogicalWrites: Escrituras lógicas promedio por ejecución.
query_hash, query_plan_hash: Hash de la consulta y del plan de ejecución.
total_rows: Número total de filas afectadas.
AvgRows: Número promedio de filas por ejecución.
total_dop: Grado total de paralelismo.
AvgDop: Grado promedio de paralelismo.
total_grant_kb, total_used_grant_kb, total_ideal_grant_kb: Memoria total concedida, usada e ideal en KB.
AvgGrantKb, AvgUsedGrantKb, AvgIdealGrantKb: Promedio de memoria concedida, usada e ideal en KB por ejecución.
total_reserved_threads, total_used_threads: Hilos totales reservados y usados.
AvgReservedThreads, AvgUsedThreads: Hilos promedio reservados y usados por ejecución.

Texto de la Consulta y Plan de Ejecución

CASE 
    WHEN sql_handle IS NULL THEN ' '
    ELSE SUBSTRING(st.text, (qs.statement_start_offset + 2) / 2, 
        (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), st.text)) * 2      
            ELSE qs.statement_end_offset    
        END - qs.statement_start_offset) / 2)
END AS query_text,
DB_NAME(st.dbid) AS database_name,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) + '.' + OBJECT_NAME(st.objectid, st.dbid) AS [object_name],
sp.[query_plan]

query_text: Extrae el texto de la consulta SQL.
database_name: Nombre de la base de datos donde se ejecutó la consulta.
object_name: Nombre del objeto (tabla, vista, etc.) relacionado con la consulta.
query_plan: Plan de ejecución de la consulta.

Tablas y Funciones del Sistema

CASE 
    WHEN sql_handle IS NULL THEN ' '
    ELSE SUBSTRING(st.text, (qs.statement_start_offset + 2) / 2, 
        (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), st.text)) * 2      
            ELSE qs.statement_end_offset    
        END - qs.statement_start_offset) / 2)
END AS query_text,
DB_NAME(st.dbid) AS database_name,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) + '.' + OBJECT_NAME(st.objectid, st.dbid) AS [object_name],
sp.[query_plan]

sys.dm_exec_query_stats: Vista de estadísticas de ejecución de consultas.
sys.dm_exec_sql_text: Función que devuelve el texto completo de la consulta SQL.
sys.dm_exec_query_plan: Función que devuelve el plan de ejecución de la consulta.

Filtrado y Orden

WHERE st.[text] LIKE '%insert%'
ORDER BY creation_time DESC;

Filtro: Se filtran las consultas cuyo texto contiene la palabra «insert».
Orden: Los resultados se ordenan por el tiempo de creación en orden descendente.

Esta consulta es una herramienta útil para analizar el rendimiento y el uso de recursos de las consultas SQL que contienen la palabra «insert» en su texto. Proporciona una visión detallada de cómo se comportan estas consultas en términos de CPU, duración, lecturas/escrituras y otros recursos, ayudando a los administradores de bases de datos a optimizar y solucionar problemas de rendimiento.

¿Qué hace DBCC CHECKDB?

Generando Script de creación de Usuarios en SQL Server

DESCARGAR SCRIPT PARA BORRAR USUARIOS HUERFÁNOS EN SQL SERVER

Eliminar usuarios huérfanos en SQL Server, puedes seguir estos pasos detallados y hacer uso de un script SQL que te ayudará a identificar y eliminar dichos usuarios de una manera ordenada. Aquí te dejo una guía detallada, que también podría ser útil como entrada de blog:

¿Por qué borrar usuarios huérfanos en SQL después de una migración?

Las migraciones de bases de datos son procesos complejos que, a menudo, pueden generar datos residuales no deseados, como usuarios huérfanos. Estos usuarios son cuentas que ya no están asociadas a ningún inicio de sesión válido y pueden suponer diversos riesgos para la seguridad y el rendimiento de la base de datos.

En este artículo, exploraremos las razones principales por las que es crucial eliminar los usuarios huérfanos en SQL después de una migración:

1. Mejorar la seguridad:

Los usuarios huérfanos representan una brecha de seguridad potencial, ya que podrían ser utilizados por actores maliciosos para acceder a la base de datos sin autorización. Al eliminarlos, se elimina este punto de entrada vulnerable y se protege la integridad de los datos.

2. Optimizar el rendimiento:

Las bases de datos con un gran número de usuarios huérfanos pueden experimentar un rendimiento más lento, especialmente durante las consultas que involucran permisos y roles. Eliminar estos usuarios innecesarios libera recursos del sistema y mejora la eficiencia general de la base de datos.

3. Simplificar la administración:

La presencia de usuarios huérfanos dificulta la administración de la base de datos, ya que aumenta la complejidad de tareas como la concesión de permisos y la auditoría de acceso. Eliminar estos usuarios simplifica el entorno y facilita la gestión de la base de datos.

4. Mejorar la calidad de los datos:

Los usuarios huérfanos pueden generar entradas de registro y datos de auditoría incorrectos, lo que dificulta la identificación de patrones y la comprensión del uso real de la base de datos. Eliminar estos usuarios garantiza que los datos de la base de datos sean precisos y confiables.

5. Cumplimiento normativo:

En algunos sectores, las regulaciones de cumplimiento de datos, como GDPR y HIPAA, exigen la eliminación de datos personales no utilizados. Eliminar usuarios huérfanos ayuda a garantizar el cumplimiento de estas regulaciones y protege la privacidad de la información sensible.

Script para borrar usuarios huérfanos y sus esquemas

---------------------------------------------------------------------------------------
-- ELIMINAR usuarios huérfanos y sus eschemas  
---------------------------------------------------------------------------------------

-- www.sql-ninja.com

set nocount on

declare @cadena varchar(4000)
declare @usuario sysname
declare @nombre_usuario sysname
declare @principal_id int
declare @usuario2 sysname

---- coge los usuarios huérfanos (que no tiene un login con mismo nombre ni mismo codigo (SID)
select name, principal_id 
into #usuarios    --- tabla temporal con los usuarios
from sys.database_principals
where type in ('S','U','G')
and (sid is not null and sid <> 0x0)
and name not in (select name from sys.server_principals)  -- no existe con mismo nombre
and sid not in (select sid from sys.server_principals)    -- no existe con mismo sid
and principal_id > 1
and db_id() > 4 
order by name

declare cur_usuarios cursor 
  for  select name,principal_id from #usuarios
  order by name

  Open cur_usuarios

  FETCH NEXT FROM cur_usuarios 
  INTO @nombre_usuario, @principal_id

  WHILE @@FETCH_STATUS = 0
  BEGIN
    select @usuario= @nombre_usuario
	select @usuario2='[' + @nombre_usuario +']'
	
    declare @schema_name sysname , @cant_schemas int
	set @schema_name = null
	set @cant_schemas = 0
	--- verifica la cantidad de schemas que tiene
	select @cant_schemas = count(*) from sys.schemas where  principal_id = @principal_id

	print '*********   USUARIO ---> ' + @nombre_usuario
	if @cant_schemas > 0
	begin
	    While @cant_schemas > 0
		begin
			select top(@cant_schemas) @schema_name = '[' + name +']' from sys.schemas where  principal_id = @principal_id
			print ' --- Se procedera a borrar el SCHEMA: ' + @schema_name
			set @cadena='   drop schema ' + @schema_name + ''
			print @cadena
			exec ( @cadena )   ----- ELIMINA schema
			set @cant_schemas = @cant_schemas - 1
		end
	end
	---- select schema_name(s.schema_id),user_name(s.principal_id),*
	print ' --- Se procedera a borrar el USUARIO: ' + @usuario
	set @cadena='   drop user ' + @usuario2 + ''
	print @cadena
	exec ( @cadena )     ----- ELIMINA usuario
	  
  FETCH NEXT FROM cur_usuarios 
  INTO @nombre_usuario,@principal_id
END
CLOSE cur_usuarios
DEALLOCATE cur_usuarios

set nocount off

drop table #usuarios
go

Explicación del código para borrar usuarios huerfános en SQL

Este script de SQL está diseñado para eliminar usuarios huérfanos y sus esquemas asociados en una base de datos. Los usuarios huérfanos son aquellos que no tienen un login correspondiente con el mismo nombre o el mismo SID (Security Identifier). Aquí está el desglose del script:

Parte Superior: Comentarios y Configuración Inicial

sqlCopiar código---------------------------------------------------------------------------------------
-- ELIMINAR usuarios huérfanos y sus esquemas  --- Usuario que NO EXISTE LOGIN CON MISMO NOMBRE NI CON MISMO SID
---------------------------------------------------------------------------------------

-- www.sql-ninja.com

set nocount on

Comentarios: Explica el propósito del script.
set nocount on: Previene que SQL Server devuelva mensajes de cuenta de filas afectadas, lo que hace que el script sea más limpio en términos de salida.

Declaración de Variables

declare @cadena varchar(4000)
declare @usuario sysname
declare @nombre_usuario sysname
declare @principal_id int
declare @usuario2 sysname

Declaración de variables: Estas variables se utilizarán para almacenar información temporal durante la ejecución del script.

Selección de Usuarios Huérfanos

select name, principal_id 
into #usuarios    --- tabla temporal con los usuarios
from sys.database_principals
where type in ('S','U','G')
and (sid is not null and sid <> 0x0)
and name not in (select name from sys.server_principals)  -- no existe con mismo nombre
and sid not in (select sid from sys.server_principals)    -- no existe con mismo sid
and principal_id > 1
and db_id() > 4 
order by name

select: Selecciona los usuarios que no tienen un login correspondiente con el mismo nombre o SID y los almacena en una tabla temporal #usuarios.

Cursor para Iterar sobre los Usuarios Huérfanos

declare cur_usuarios cursor 
  for  select name,principal_id from #usuarios
  order by name

Open cur_usuarios

FETCH NEXT FROM cur_usuarios 
INTO @nombre_usuario, @principal_id

Cursor: Define un cursor cur_usuarios para iterar sobre los usuarios huérfanos seleccionados anteriormente.

Bucle para Procesar Cada Usuario Huérfano

WHILE @@FETCH_STATUS = 0
BEGIN
    select @usuario= @nombre_usuario
	select @usuario2='[' + @nombre_usuario +']'
	
    declare @schema_name sysname , @cant_schemas int
	set @schema_name = null
	set @cant_schemas = 0
	--- verifica la cantidad de schemas que tiene
	select @cant_schemas = count(*) from sys.schemas where  principal_id = @principal_id

	print '*********   USUARIO ---> ' + @nombre_usuario
	if @cant_schemas > 0
	begin
	    While @cant_schemas > 0
		begin
			select top(@cant_schemas) @schema_name = '[' + name +']' from sys.schemas where  principal_id = @principal_id
			print ' --- Se procedera a borrar el SCHEMA: ' + @schema_name
			set @cadena='   drop schema ' + @schema_name + ''
			print @cadena
			exec ( @cadena )   ----- ELIMINA schema
			set @cant_schemas = @cant_schemas - 1
		end
	end
	---- select schema_name(s.schema_id),user_name(s.principal_id),*
	print ' --- Se procedera a borrar el USUARIO: ' + @usuario
	set @cadena='   drop user ' + @usuario2 + ''
	print @cadena
	exec ( @cadena )     ----- ELIMINA usuario
	  
  FETCH NEXT FROM cur_usuarios 
  INTO @nombre_usuario,@principal_id
END

Bucle WHILE: Itera sobre cada usuario huérfano.
- Variables y Preparación: Asigna el nombre del usuario a las variables @usuario y @usuario2 (con corchetes para manejar nombres con espacios o caracteres especiales).
- Verificación de Esquemas: Cuenta la cantidad de esquemas asociados con el usuario.
- Eliminación de Esquemas: Si hay esquemas asociados, los elimina uno por uno.
- Eliminación de Usuario: Después de eliminar los esquemas, elimina el usuario.

Cierre y Liberación de Recursos

CLOSE cur_usuarios
DEALLOCATE cur_usuarios

set nocount off

drop table #usuarios
go

Cierre del Cursor: Cierra y libera el cursor.
Restauración de nocount: Vuelve a permitir que SQL Server devuelva mensajes de cuenta de filas.
Eliminación de Tabla Temporal: Elimina la tabla temporal #usuarios.

Este script es útil para mantener la base de datos limpia al eliminar usuarios huérfanos y sus esquemas asociados, evitando así posibles problemas de seguridad y administración.

La importancia eliminar usuarios huérfanos en SQL después de una migración.

Más allá de las razones generales mencionadas en la entrada anterior, eliminar usuarios huérfanos en SQL después de una migración reviste especial importancia por las siguientes razones:

1. Complejidad de la migración:

Las migraciones de bases de datos, por su naturaleza compleja, pueden generar un mayor número de usuarios huérfanos debido a diversos factores, como:

Eliminación incompleta de usuarios: Si la migración no elimina correctamente las cuentas de usuario asociadas a objetos de la base de datos origen, pueden quedar usuarios huérfanos en el destino.
Cambios en la estructura de la base de datos: Si la estructura de la base de datos destino difiere de la origen, es posible que algunos usuarios ya no tengan permisos para acceder a los objetos relevantes, quedando huérfanos.
Migración de datos incompleta: Si la migración no transfiere completamente los datos de permisos y roles, puede haber usuarios huérfanos en el destino que no estén asociados a ningún objeto o rol.

2. Mayor impacto en el rendimiento:

En el contexto de una migración, el impacto en el rendimiento causado por usuarios huérfanos puede ser más significativo, ya que:

La base de datos destino es un nuevo entorno: Es posible que la base de datos destino no esté optimizada para manejar un gran número de usuarios huérfanos, lo que puede amplificar la degradación del rendimiento.
Los recursos del sistema se encuentran en fase de adaptación: Tras la migración, la base de datos destino se encuentra en un proceso de adaptación a la nueva carga de trabajo. La presencia de usuarios huérfanos puede sobrecargar aún más el sistema durante esta fase crucial.

3. Riesgos de seguridad amplificados:

En el contexto posterior a una migración, los riesgos de seguridad asociados a usuarios huérfanos se intensifican:

Entorno menos familiar: Los administradores del sistema pueden estar menos familiarizados con la nueva base de datos destino, lo que aumenta la probabilidad de pasar por alto usuarios huérfanos durante las revisiones de seguridad.
Mayor superficie de ataque: Una migración puede ampliar la superficie de ataque de la base de datos, ya que los actores maliciosos pueden explorar los nuevos usuarios huérfanos como puntos de entrada vulnerables.

4. Mayor dificultad para la resolución de problemas:

La presencia de usuarios huérfanos puede dificultar la resolución de problemas después de una migración:

Diagnóstico confuso: Los errores o fallos de funcionamiento pueden tener su origen en usuarios huérfanos, lo que dificulta la identificación precisa de la causa raíz del problema.
Soluciones más complejas: La resolución de problemas relacionados con usuarios huérfanos puede requerir soluciones más complejas y que consumen más tiempo, desviando recursos de otras tareas importantes.

Cambiar el collation en un servidor sql server 2019

Creación de Roles en SQL Server

Generando Script de creación de Usuarios en SQL Server

Scripts de Creación de Usuarios en SQL Server

Generando Script de creación de Usuarios en SQL Server

En el entorno de administración de bases de datos, es común encontrarnos con la necesidad de generar scripts automatizados para tareas repetitivas como la creación de usuarios. En SQL Server, este proceso se simplifica utilizando consultas SQL que extraen información de metadatos del sistema para construir declaraciones CREATE USER personalizadas. A continuación, exploraremos cómo puedes generar estos scripts de manera eficiente.

Consulta SQL

La consulta SQL proporcionada tiene como objetivo generar comandos CREATE USER para todos los usuarios relevantes en una base de datos específica. Vamos a desglosar cada parte de la consulta para entender cómo funciona:

sqlCopiar 
SELECT 
	'CREATE USER ['+ dbU.[name] + '] FOR LOGIN [' + dbU.[name] + '] WITH DEFAULT_SCHEMA = [' +dbU.default_schema_name +'];'
FROM	
	sys.database_principals AS dbU 	
WHERE (TYPE='S' or TYPE='U' or TYPE='G') aND dbU.default_schema_name is not null
GO

DESCARGAR SCRIPT PARA SACAR USUARIOS DE UNA BASE DE DATOS SQL

Este script recorre los usuarios de la base de datos que tienen un esquema predeterminado asignado y crea usuarios de SQL Server correspondientes con inicios de sesión asociados. Si se proporciona un esquema predeterminado, también se asigna al usuario recién creado.

Desglose del script para sacar todos los usuarios de una base de datos

Consulta de la base de datos:
- sys.database_principals AS dbU: sys.database_principals es una vista del sistema que contiene información sobre los principios de seguridad en la base de datos actual (dbU es un alias para esta tabla).
- WHERE (TYPE = 'S' OR TYPE = 'U' OR TYPE = 'G'): Filtra los resultados para incluir solo usuarios ('S'), usuarios de Windows ('U') y grupos ('G').
- AND dbU.default_schema_name IS NOT NULL: Asegura que solo se seleccionen aquellos principios de base de datos que tienen un esquema predeterminado definido.
Resultado de la consulta: La consulta devuelve una lista de comandos CREATE USER para cada usuario encontrado en sys.database_principals que cumpla con los criterios especificados. El comando CREATE USER crea un usuario en la base de datos para un login específico, asignando el esquema predeterminado indicado por dbU.default_schema_name.
Ejecución del script:
- Una vez obtenidos los comandos CREATE USER, generalmente se ejecutan en el contexto de la base de datos para crear los usuarios con los esquemas predeterminados correspondientes.

En resumen, este script automatiza la creación de usuarios en una base de datos SQL Server para los logins relevantes, asegurándose de asignarles un esquema predeterminado válido si está definido en la base de datos.

Beneficios y Aplicaciones Prácticas

Automatización: Esta consulta puede ser integrada en scripts más grandes de administración de base de datos para automatizar la creación de usuarios durante el despliegue de una aplicación o la configuración inicial de un entorno.
Seguridad y Gestión: Al automatizar la creación de usuarios, se asegura la consistencia y la seguridad, evitando errores humanos y garantizando que todos los usuarios tengan configuraciones adecuadas.
Escalabilidad: A medida que crece el número de usuarios y bases de datos, el uso de scripts SQL como este facilita la gestión y reduce la carga administrativa.

Conclución

El uso de consultas SQL como la presentada no solo simplifica tareas administrativas complejas, sino que también mejora la eficiencia operativa y reduce el riesgo de errores. Al integrar estas prácticas en tu flujo de trabajo de administración de bases de datos, puedes optimizar significativamente la gestión y la seguridad de tus entornos SQL Server.