Stuff function in Sql Server

comma separated values in sql server

OUTPUT Clause with INSERT,UPDATE, DELETE

Deleting Rows in Chunks

Combining Result Sets with UNION

Using the TABLESAMPLE to Return Random Rows

Summarizing Data Using WITH CUBE

Using GROUPING with WITH CUBE

Summarizing Data with WITH ROLLUP

Adding a Column to an Existing Table

Changing a Column Definition

Dropping a Table Column

Sql Server Case Sensitive

CreateTable with a Primary Key

Adding a Primary Key Constraint to an Existing Table

Creating a Table with a Foreign Key in Sql Server

Adding a Foreign Key to an Existing Table

Sql Server Rebuilding Indexes

Rename a Column Name or Table Name

Displaying the Oldest Active Transaction

using group by all

Selectively Query Grouped Data Using HAVING

Sql Table Variables

Introduction to Locking in SQL Server

Viewing Lock Activity in Sql

Sql Server Isolation Levels

Using SET TRANSACTION ISOLATION LEVEL

Sql Server Blocking

Using Set Lock Timeout

Sql Server Deadlocking

Setting Deadlock Priority

Deleting Rows in Chunks in Sql Server

In this recipe, I show you how to modify data in blocks of rows in multiple executions, instead of an entire result set in one large transaction. First, I create an example deletion table for this example:

SELECT * INTO Production.Example_BillOfMaterials
FROM Production.BillOfMaterials

Next, all rows will be deleted from the table in 500 row chunks:

WHILE (SELECT COUNT(*)FROM Production.Example_BillOfMaterials)> 0
BEGIN
DELETE TOP(500)
FROM Production.Example_BillOfMaterials
END

This returns:

(500 row(s) affected)
(500 row(s) affected)
(500 row(s) affected)
(500 row(s) affected)
(500 row(s) affected)
(179 row(s) affected)

How It Works

In this example, I used a WHILE condition to keep executing the DELETE while the count of rows in the table was greater than zero .
WHILE (SELECT COUNT(*)FROM
Production.Example_BillOfMaterials)> 0
BEGIN
Next was the DELETE, followed by the TOP clause, and the row limitation in parentheses:
DELETE TOP(500)
FROM Production.BillOfMaterials
This recipe didn't use a WHERE clause, so no filtering was applied and all rows were deleted from the table—but only in 500 row chunks. Once the WHILE condition no longer evaluated to TRUE, the loop ended. After executing, the row counts affected in each batch were displayed. The first five batches deleted 500 rows, and the last batch deletes the remaining 179 rows.