Tuesday, November 17, 2015

How to delete millions of records without performance issue ?

How to delete millions of recs without performance issue:

Here I  provide video solution .

SELECT file_id, name, type_desc, physical_name, (size*8)/1024 SizeinMB, max_size
FROM sys.database_files;

DECLARE @DeleteRowCnt INT
SET @DeleteRowCnt = 1

DECLARE @DeleteBatchSize INT
SET @DeleteBatchSize=4000

WHILE (@DeleteRowCnt > 0)
BEGIN
DELETE TOP (@DeleteBatchSize) [dbo].[Customer]
WHERE RegionCD = 'AS;
 SET @DeleteRowCnt = @@ROWCOUNT;
END