DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to delete 100 million rows from a table of 5 billion rows?

The following Microsoft SQL Server T-SQL scripts demonstrate large DELETEs.

-- SQL DELETE simple syntax - Create table for testing with SELECT INTO

USE tempdb;

SELECT * INTO Product FROM AdventureWorks2008.Production.Product

-- (504 row(s) affected)

DELETE Product WHERE ListPrice = 0 or Color is NULL

-- (259 row(s) affected)

Instead of DELETE, SELECT INTO what you keep:

-- Backup database
use DatabaseName;
sp_rename TableX, OldTableX;
go
select * into TableX from OldTableX
where (specify criteria for inclusion - what you want to keep)
go
-- create indexes/constraints on TableX
-- Backup database
-- drop table OldTableX -- for example 2 weeks later

go

Alternative to [SELECT INTO new table] is to delete in small batches. Demo follows.

-- SQL DELETE large number of rows from table in batches

USE tempdb;

SELECT ProductID=CONVERT(int,ProductID), ProductName=Name,

       ProductNumber, ListPrice, Color

INTO ProductHuge

FROM AdventureWorks2008.Production.Product

GO

INSERT ProductHuge SELECT * FROM ProductHuge  -- Increase table rows for testing

GO 10

SELECT COUNT(*)FROM ProductHuge

GO

-- 516096

DECLARE @BatchSize int = 10000

-- DELETE loop with 1 sec delay

-- Other processes can use the table during the DELETE

WHILE (@@ROWCOUNT > 0)

BEGIN

  WAITFOR DELAY '00:00:01'

  DELETE TOP (@BatchSize) FROM ProductHuge

  WHERE ListPrice = 0

END

GO

SELECT COUNT(*)FROM ProductHuge

GO

-- 311296

DROP TABLE tempdb.dbo.ProductHuge


Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE