How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table

by

Calling DELETE FROM TableName will do the entire delete in one large transaction. This is expensive.

Here is another option which will delete rows in batches :

deleteMore:
DELETE TOP(10000) Sales WHERE toDelete="1"
IF @@ROWCOUNT != 0
    goto deleteMore

More Related Contents:

  1. to select all the employees who have joined most recently under king
  2. T-SQL split string
  3. Unpivot with column name
  4. How can I delete using INNER JOIN with SQL Server?
  5. How can I get column names from a table in SQL Server?
  6. Conditional WHERE clause in SQL Server
  7. How can I select the first day of a month in SQL?
  8. How to group time by hour or by 10 minutes
  9. Convert varchar into datetime in SQL Server
  10. Select statement to find duplicates on certain fields
  11. Check if a row exists, otherwise insert
  12. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  13. Convert Xml to Table SQL Server
  14. How to create a PivotTable in Transact/SQL?
  15. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  16. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  17. How does this CASE expression reach the ELSE clause?
  18. T-SQL and the WHERE LIKE %Parameter% clause
  19. Store multiple bit values in a single table column
  20. Which SQL query is faster? Filter on Join criteria or Where clause?
  21. SQL Server equivalent to MySQL enum data type?
  22. Generate all combinations in SQL
  23. Grant SELECT permission on a view, but not on underlying objects
  24. Using IF ELSE statement based on Count to execute different Insert statements
  25. Average of multiple columns
  26. Cumulative Total in MS Sql server [duplicate]
  27. COALESCE Function in TSQL
  28. Flattening of a 1 row table into a key-value pair table
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Select rows where column value has changed

Leave a Comment