Sql-server – Batch Inserts and Batch Deletes

sql server

I have two tables with same structures in a MS Sql server : T1 and T2, T1 has around 4 Billion rows and T2 is an empty table. Need to insert T1 data in to T2 in batches, After each Batch Insert in to T2 from T1, I have to Delete that data(batch Delete) from T1.
FYI: Tables contains 1 Primary Key and 4 non-clustered indexes

Please suggest me the best options/best query.

Best Answer

You can try this query to get good performance without using temp table or joining to delete from T1. And it's very simple.

SET ROWCOUNT 10000

WHILE EXISTS (SELECT TOP 1 'EXISTS' FROM T1 WHERE COLUMN=VALUE)
BEGIN
WAITFOR DELAY '00:00:10' -- You can pass it if you want interval between next delete
DELETE FROM T1
OUTPUT DELETED.* INTO T2
WHERE COLUMN=VALUE
END

SET ROWCOUNT 0

You can comment or remove WHERE clause if not required.

Thanks

Related Solutions

Sql-server – Index fragmentation increase as more row being deleted

This isn't fragmentation.

Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.

If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.

Do you have an index on CreationDate
Do you have delete cascades?
Is the TOP 1000 in a single transaction?

For point 3, doing a loop inside a transaction is pointless: is this it?

At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.

Other options:

why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
use SYNONYMs for poor man's partitioning

SQL Server – Moving Primary Key Constraint from One Index to Another

The following script illustrates an efficient way to convert the existing nonclustered primary key to clustered, and to rename it:

-- How the table looks now
CREATE TABLE dbo.Example
(
    pk integer NOT NULL,
    some_data integer NOT NULL,

    CONSTRAINT PK_UnusualName
        PRIMARY KEY NONCLUSTERED (pk)
);

-- Some data
INSERT dbo.Example (pk, some_data)
VALUES (1, 100), (2, 200), (3, 300);

-- Change the nonclustered PK to clustered
CREATE UNIQUE CLUSTERED INDEX PK_UnusualName
ON dbo.Example (pk)
WITH (DROP_EXISTING = ON);

-- Rename
EXECUTE sys.sp_rename 
    @objname = N'dbo.Example.PK_UnusualName',
    @newname = N'PK__dbo_Example_pk',
    @objtype = 'INDEX';

-- Tidy up
DROP TABLE dbo.Example;

Best Answer

Related Solutions

Sql-server – Index fragmentation increase as more row being deleted

SQL Server – Moving Primary Key Constraint from One Index to Another

Related Question