SQL to batch delete specific rows wrapped wrapped in transaction.

batch_delete.sql

/*
SQL to batch delete specific rows wrapped wrapped in transaction.
*/

DECLARE @BatchSize INT = 1000; 
DECLARE @RowsAffected INT = 1;
DECLARE @Batches INT = 10;
DECLARE @BatchCount INT = 0;

WHILE @RowsAffected > 0 AND @BatchCount < @Batches 
BEGIN

	BEGIN TRANSACTION;

	DELETE TOP(@BatchSize) FROM [your_schema].[your_table] 
	WHERE [your_indexed_date_column] >= '2020-02-15 00:00:00' AND [your_indexed_date_column] < '2020-03-15 00:00:00';

	SET @RowsAffected = @@ROWCOUNT;
	SET @BatchCount = @BatchCount + 1;
	
	IF @RowsAffected  > 0
	BEGIN
		COMMIT TRANSACTION;
		PRINT 'Delete operation committed successfully.';
	END
	ELSE
	BEGIN
		ROLLBACK TRANSACTION;
		PRINT 'Delete operation rolled back due to no matching rows.';
	END
END

general_helper.sql

SELECT
    YEAR(your_indexed_date_column) AS year,
    MONTH(your_indexed_date_column) AS month,
    COUNT(*) AS total_records
FROM [your_schema].[your_table] WITH (NOLOCK)
WHERE YEAR(your_indexed_date_column) IN (2020, 2019)
GROUP BY YEAR(your_indexed_date_column), MONTH(your_indexed_date_column)
ORDER BY YEAR(your_indexed_date_column), MONTH(your_indexed_date_column);



SELECT
    YEAR(your_indexed_date_column) AS year,
    MONTH(your_indexed_date_column) AS month,
	DAY(StorageCreateDate) AS Day,
    COUNT(*) AS total_records,
	CONVERT(date, your_indexed_date_column) AS your_indexed_date_column
FROM [your_schema].[your_table] WITH (NOLOCK)
WHERE YEAR(your_indexed_date_column) IN (2020)
GROUP BY YEAR(your_indexed_date_column), MONTH(your_indexed_date_column), DAY(your_indexed_date_column), CONVERT(date, your_indexed_date_column)
ORDER BY YEAR(your_indexed_date_column), MONTH(your_indexed_date_column), DAY(your_indexed_date_column);



SELECT MIN(ID) AS MIN_ID, MAX(ID) AS MAX_ID, MIN(your_indexed_date_column) as MIN_DATE
, MAX(your_indexed_date_column) as MAX_DATE, COUNT_BIG(*) AS Total_Count 
FROM [your_schema].[your_table] WITH (NOLOCK)