quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

If you want to delete all data, use truncate otherwise delete in batches

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

If you want to delete all data, use truncate otherwise delete in batches

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by raky

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

If you want to delete all data, use truncate otherwise delete in batches

Madhivanan

Failing to plan is Planning to fail

---

Iam not deleting the entire data from table only some part of it.

---

quote:

---

Originally posted by raky

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

If you want to delete all data, use truncate otherwise delete in batches

Madhivanan

Failing to plan is Planning to fail

---

Is the below query will perform good

while 1 = 1
begin

delete top ( 1000 )
from test
where keyval is null

if @@rowcount = 0 BREAK

end

Pls reply madhi..

---

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by raky

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

If you want to delete all data, use truncate otherwise delete in batches

Madhivanan

Failing to plan is Planning to fail

---

Iam not deleting the entire data from table only some part of it.

---

As per my concern..(Though i am a beginner of sql)If you are using sql server 2005 and above..please try this appraoch of TOP Operator..
DELETE TOP (200) tbl WHERE ...

---

quote:

---

Originally posted by raky

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by raky

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by raky

Hi,

I need to delete a large number of records ( around 8 Crores ) from a table. What is the best way ( Quicker Way) to do it?

---

If you want to delete all data, use truncate otherwise delete in batches

Madhivanan

Failing to plan is Planning to fail

---

Iam not deleting the entire data from table only some part of it.

---

As per my concern..(Though i am a beginner of sql)If you are using sql server 2005 and above..please try this appraoch of TOP Operator..
DELETE TOP (200) tbl WHERE ...

---

iam also trying with top operator but it ran for around 1 hour( No record deleted) so i stopped.

---

DECLARE	@intRowCount int,
	@intRowNo int,
	@intBatchSize

SELECT	@intBatchSize = 10000	-- Set initial batch size
DECLARE	@TempTable TABLE
(
	MyID INT IDENTITY(1,1) NOT NULL,
	MyPK INT NOT NULL,
	PRIMARY KEY
	(
		MyID,
		MyPK
	)
)

RAISERROR (N'Building @TempTable', 10, 1) WITH NOWAIT
INSERT INTO @TempTable(MyPK)
SELECT	MyPK
FROM	MyTable
WHERE	MyKeyValue IS NULL	-- Full set of criteria identifying the rows-to-be-delete here
ORDER BY MyPK
SELECT @intRowCount = @@ROWCOUNT
RAISERROR (N'Rows to delete: %d', 10, 1, @intRowCount) WITH NOWAIT

SELECT @intRowNo = 1 -- Set Initial start point
WHILE @intRowCount > 0
BEGIN
	RAISERROR (N'Loop %d', 10, 1, @intRowNo) WITH NOWAIT
	DELETE D
	FROM	MyTable AS D
		JOIN @TempTable AS T
			 ON T.MyPK = D.MyPK
			AND T.MyID >= @intRowNo
			AND T.MyID <= @intRowNo + @intBatchSize
	WHERE	MyKeyValue IS NULL	-- Repeat the full set of criteria here IF the data could have changed
	SELECT	@intRowCount = @@ROWCOUNT
	-- Optionally adjust @intBatchSize Up/Down if the batch is runner faster/slower than allowed
	SELECT	@intRowNo = @intRowNo + @intBatchSize
	WAITFOR DELAY '000:00:05'	-- Optional loop delay to allow other processes to continue running
	-- Backup TLog every N iterations (to prevent it growing exceptionally large)
END
RAISERROR (N'Completed', 10, 1) WITH NOWAIT

quote:

---

Originally posted by Kristen

Note that if the number of rows to be deleted is MOST of the table then this would be better:

Copy rows-to-keep to new, temporary table
Drop original table
Rename temporary table to original table's name

I don't think that DELETE TOP nnnn is a good choice. The SELECT has to be performed numerous times and, assuming a non-trial WHERE clause, the query will most likely take a measurable amount of time to run, and as more and more data is deleted the "shape" of the table will change - which may trigger statistics to be rebuilt multiple times and so on.

My preference is to get the PKs (actually the keys for the Clustered Index - usually the PK) for the rows-to-be-deleted into a Temp table, and then JOIN that to the main table (in batches) for deletion:

DECLARE	@intRowCount int,
	@intRowNo int,
	@intBatchSize

SELECT	@intBatchSize = 10000	-- Set initial batch size
DECLARE	@TempTable TABLE
(
	MyID INT IDENTITY(1,1) NOT NULL,
	MyPK INT NOT NULL,
	PRIMARY KEY
	(
		MyID,
		MyPK
	)
)

RAISERROR (N'Building @TempTable', 10, 1) WITH NOWAIT
INSERT INTO @TempTable(MyPK)
SELECT	MyPK
FROM	MyTable
WHERE	MyKeyValue IS NULL	-- Full set of criteria identifying the rows-to-be-delete here
ORDER BY MyPK
SELECT @intRowCount = @@ROWCOUNT
RAISERROR (N'Rows to delete: %d', 10, 1, @intRowCount) WITH NOWAIT

SELECT @intRowNo = 1 -- Set Initial start point
WHILE @intRowCount > 0
BEGIN
	RAISERROR (N'Loop %d', 10, 1, @intRowNo) WITH NOWAIT
	DELETE D
	FROM	MyTable AS D
		JOIN @TempTable AS T
			 ON T.MyPK = D.MyPK
			AND T.MyID >= @intRowNo
			AND T.MyID <= @intRowNo + @intBatchSize
	WHERE	MyKeyValue IS NULL	-- Repeat the full set of criteria here IF the data could have changed
	SELECT	@intRowCount = @@ROWCOUNT
	-- Optionally adjust @intBatchSize Up/Down if the batch is runner faster/slower than allowed
	SELECT	@intRowNo = @intRowNo + @intBatchSize
	WAITFOR DELAY '000:00:05'	-- Optional loop delay to allow other processes to continue running
	-- Backup TLog every N iterations (to prevent it growing exceptionally large)
END
RAISERROR (N'Completed', 10, 1) WITH NOWAIT

---