yogi123 (11/5/2012)

---

Hello

I need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.

please help me

while @@ROWCOUNT > 0
	delete top(1000)
	from YourTable
	Where ???

Sean Lange (11/5/2012)

---

Why do you think you need to loop to delete the top 1,000 rows?

You didn't provide any details so it is impossible to provide much help.

If you need to use batches to delete rows from a table you can use something like this.

while @@ROWCOUNT > 0
	delete top(1000)
	from YourTable
	Where ???

Matt Miller (#4) (11/7/2012)

---

Also - since there's no physical order in a DB, there had better be a good predictable ORDER BY clause added in to define how the TOP 1000 is supposed to find said 1000.

Sean Lange (11/7/2012)

---

It actually really doesn't make any difference here. The intention is to delete rows in a batch. Why not let sql decide which rows are the top? It will determine the top based on whatever it finds as the easiest way to get them. It doesn't make any difference the order they are deleted as long as at the end they are all deleted in the end.

Matt Miller (#4) (11/7/2012)

---

Well if you can align the ORDER BY to the CI, the delete won't have to revisit the same pages multiple times which might help improve writes. That said - if the intent is to keep cycling to run this across the entire table (instead of just doing the top 1000 once and then stopping), it likely won't make much of a difference.