En varias oportunidades podemos tener la necesidad de depurar alguna tabla grande, esta operación puede ser compleja y hasta impactar la performance de tu base de datos.
En el siguiente tutorial te muestro paso a paso cómo podes hacer un proceso para mover registros viejos a una tabla de historia afectando lo menos posible la performance de tu SQL.
Comparto también el código utilizado en dicho video tutorial
USE master
GO
DROP DATABASE DATOS
DROP DATABASE DATOS_HISTORY
CREATE DATABASE DATOS
CREATE DATABASE DATOS_HISTORY
USE DATOS
GO
-- CREAMOS UNA TABLA Y LA LLENAMOS CON 4M DE REGISTROS
SELECT TOP 4000000
H.AccountNumber,
h.BillToAddressID,
h.CustomerID,
h.OrderDate,
h.SalesOrderNumber,
h.TotalDue
INTO DBO.VENTAS FROM AdventureWorks2019.Sales.SalesOrderHeader H
CROSS JOIN AdventureWorks2019.sys.columns C
-- CREAMOS A LA BASE HISTORY LA TABLA VACIA
USE DATOS_HISTORY
GO
SELECT TOP 0 * INTO DBO.VENTAS FROM DATOS.DBO.VENTAS
USE DATOS
GO
-- CALCULAMOS LAS ORDENES MENORES A 2007
SELECT COUNT(1)
FROM DBO.VENTAS
WHERE OrderDate >= '20130101' and OrderDate < '20140101'
SELECT COUNT(1)
FROM DBO.VENTAS
use DATOS
DBCC TRACEON (1224) -- NO ESCALAN LOS LOKEOS PARA ESTA SESION
WHILE 1 = 1
BEGIN
DELETE TOP (50000) FROM DBO.VENTAS
OUTPUT
Deleted.AccountNumber,
Deleted.BillToAddressID,
Deleted.CustomerID,
Deleted.OrderDate,
Deleted.SalesOrderNumber,
Deleted.TotalDue
INTO DATOS_HISTORY.dbo.VENTAS
WHERE OrderDate >= '20130101'
and OrderDate < '20140101'
IF @@ROWCOUNT = 0 BREAK;
END;
SELECT COUNT(1) FROM DBO.VENTAS
SELECT COUNT(1) FROM DATOS_HISTORY.DBO.VENTAS
En algunas oportunidades observamos que SQL no utiliza nuestros índices creados.
En el siguiente tutorial te explico cuales son las principales causas.
También te comparto los ejemplos utilizados:
USE AdventureWorks2019
GO
SP_HELPINDEX 'Sales.SalesOrderDetail'
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID +1 = 898;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE upper(ProductID) = '897';
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 870;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WITH (INDEX=ix_salesorderdetail_productid)
WHERE ProductID = 870;
En este tutorial te voy a mostrar cómo podes usar la función de TSQL string_split() para poder pasar una lista de valores a tus procedimientos almacenados.
También comparto el código utilizado en la demostración
USE ADVENTUREWORKS2019
GO
SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
USE [MASTER]
GO
ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 120
GO
USE ADVENTUREWORKS2019
GO
SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 150
GO
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_STRING
@VALORES VARCHAR(8000)
AS
SET NOCOUNT ON
SELECT * FROM SALES.SALESORDERDETAIL D
WHERE D.SALESORDERID
IN
(
SELECT VALUE FROM
STRING_SPLIT(@VALORES,',')
)
GO
--- USANDO FUNCIONES
CREATE OR ALTER FUNCTION [dbo].[SplitInts]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT Item = Item FROM
( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
FROM ( SELECT [XML] = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
WHERE Item IS NOT NULL
);
GO
CREATE OR ALTER FUNCTION [DBO].[SPLITSTRING]
(
@INPUT NVARCHAR(MAX),
@CHARACTER CHAR(1)
)
RETURNS @OUTPUT TABLE (
ITEM NVARCHAR(1000)
)
AS
BEGIN
DECLARE @STARTINDEX INT, @ENDINDEX INT
SET @STARTINDEX = 1
IF SUBSTRING(@INPUT, LEN(@INPUT) - 1, LEN(@INPUT)) <> @CHARACTER
BEGIN
SET @INPUT = @INPUT + @CHARACTER
END
WHILE CHARINDEX(@CHARACTER, @INPUT) > 0
BEGIN
SET @ENDINDEX = CHARINDEX(@CHARACTER, @INPUT)
INSERT INTO @OUTPUT(ITEM)
SELECT SUBSTRING(@INPUT, @STARTINDEX, @ENDINDEX - 1)
SET @INPUT = SUBSTRING(@INPUT, @ENDINDEX + 1, LEN(@INPUT))
END
RETURN
END
SELECT * FROM DBO.SPLITINTS( 'HOLA,MUNDO', ',')
SELECT * FROM DBO.SPLITSTRING( 'HOLA,MUNDO', ',')
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION
@VALORES VARCHAR(8000)
AS
SET NOCOUNT ON
SELECT * FROM SALES.SALESORDERDETAIL D
WHERE D.SALESORDERID
IN
(
SELECT * FROM
DBO.SPLITINTS(@VALORES,',')
)
GO
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION2
@VALORES VARCHAR(8000)
AS
SET NOCOUNT ON
SELECT * FROM SALES.SALESORDERDETAIL D
WHERE D.SALESORDERID
IN
(
SELECT * FROM
DBO.SPLITSTRING(@VALORES,',')
)
GO
-- PRUEBAS
EXEC DBO.USP_SPLIT_STRING
'43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
EXEC DBO.USP_SPLIT_FUNCTION2
'43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
EXEC DBO.USP_SPLIT_FUNCTION
'43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
En algunas oportunidades necesitamos conocer el tamaño de nuestras bases de datos y/o Datafiles.
Les comparto los siguientes códigos en los cuales podrán obtener esta información por medio de comandos TSQL usando la vista de sistemas sys.master_files
-- ESPACIO POR DATAFILE
SELECT
DB_NAME(DATABASE_ID) AS DBNAME,
NAME AS LOGICAL_NAME,
TYPE_DESC,
PHYSICAL_NAME, (SIZE*8)/1024 SIZE_MB
FROM SYS.MASTER_FILES
ORDER BY 1
-- ESPACIO POR BASE DE DATOS
SELECT
DB_NAME(DATABASE_ID) AS DBNAME,
SUM((SIZE*8)/1024) SIZE_MB
FROM SYS.MASTER_FILES
GROUP BY
DB_NAME(DATABASE_ID)
ORDER BY 1
USE [master]
GO
CREATE LOGIN [DEMO1] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2019]
GO
CREATE USER [DEMO1] FOR LOGIN [DEMO1]
GO
USE [AdventureWorks2019]
GO
ALTER ROLE [db_owner] ADD MEMBER [DEMO1]
GO
Sin lugar a dudas que la seguridad es muy importante en nuestros entornos de base de datos ya sea on-prem o nube.
Ahora bien, ¿Cómo podríamos saber que tenemos configurado de forma correcta la seguridad en la instancia y bases de datos?
La primer medida es conocer las buenas prácticas de seguridad recomendadas por Microsoft para SQL Server
Pero cómo podemos revisar que nuestros servidores y bases estén de forma correcta?
Bueno para esto se ha incorporado al SSMs (SQL Server Management Studio) unos informes de auditoria que nos ayudaran a revisar toda la configuración e indicarnos las mejoras necesarias.
