Cómo depurar registros de una tabla y moverlos a otra

Cómo depurar registros de una tabla y moverlos a otra

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
Cómo depurar registros de una tabla y moverlos a otra

¿Porque no usa SQL Server mis índices?

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;

Cómo depurar registros de una tabla y moverlos a otra

Usando String_split() para pasar una lista de valores a tus Procedimientos almacenados

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'

Cómo depurar registros de una tabla y moverlos a otra

Cómo obtener el tamaño nuestras bases y archivos usando TSQL

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
Cómo depurar registros de una tabla y moverlos a otra

Cómo copiar logins entre instancias SQL

En nuestro trabajo diario nos toca muchas veces copiar los logins que tenemos en una instancia a la otra.

En este tutorial paso a paso te muestro las distintas metodologías que podes usar para realizar de forma efectiva esta tarea y no tener problemas.

Transfer logins and passwords between instances – SQL Server | Microsoft Docs

dbatools – the community’s sql powershell module

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
Cómo depurar registros de una tabla y moverlos a otra

Revisando la seguridad de tu SQL Server

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.

Este nuevo componente se llama Vulnerability assessment

En el siguiente video tutorial de mi canal de Youtube te voy a mostrar cómo se utiliza esta tremenda herramienta