Chapter 11: Deleting Data from Microsoft SQL Server 2005 | Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

Download CD Content

After completing this chapter, you will be able to:

Delete information from your database
Manage deletions and control row deletions
Delete data using ADO.NET

Using the DELETE Statement

There will be occasions when you need to delete some or all of the information in your database. You may need to remove a record inserted by error, or you may decide to delete information physically from a table when the user wants to delete some records. Alternatively, you may need to remove old information from an historical table. In all of these cases, you will use the DELETE statement.

The basic DELETE sentence is:

 DELETE FROM [<ServerName>.][<DataBseName>.]<SchemaName>.<TableName>       WHERE <Conditions>

If you execute the DELETE sentence with a connection to the current database, you can simply use the <SchemaName>.<TableName> form without including the server and database names . You can use the DELETE statement without a WHERE condition, but typically you do not want to remove all of the content from a table.

Tip	If you want to remove all of the rows from a table, you can use the TRUNCATE TABLE statement instead of the DELETE statement. It will perform the task quickly, but you must be sure that there are no relationships from or to the table.

Creating a DELETE Sentence with SQL Server Management Studio

To create a DELETE sentence using SQL Server Management Studio, perform the following steps.

Creating a DELETE Sentence

Right-click on the desired table in Object Explorer.
Choose Script Table As.
Choose Delete To.
Choose New Query Editor Window. SQL Server Management Studio will create a template for you.

Important

If you work through the procedures in this chapter using the AdventureWorks database, it is important to back up your database first.

Defining the WHERE Condition

You can use any or all columns of a table to create the WHERE condition. You can enhance the performance of the WHERE condition by using the following guidelines.

Use the Primary.Key column/s in the condition.
Use columns that are part of an index.

These are suggestions only, for you can delete rows by using any column of a table in the WHERE condition. It is also not necessary to use a column from an index.

The following is an example of a simple DELETE sentence:

 DELETE FROM [AdventureWorks].[Person].[Address]       WHERE AddressID=1

The WHERE condition can be used with any of the conditional arguments it accepts to delete information, including those that establish ranges of data, or with logical combinations of AND, OR, and NOT.

Tip	If you need to delete a set of records that depends on several conditions, it is better to write a SELECT query with the same filters first to check that you will be deleting only the necessary records.

Review the following example (included in the sample files as \Ch11\AdvWorks\Queries\Sample01.sql) in which different WHERE filtering operators are utilized.

 DELETE FROM         Production.Product WHERE (MakeFlag = 1)       AND        (ReorderPoint BETWEEN 200 AND 600)       AND        (SellStartDate < CONVERT(DATETIME, '2000-01-01 00:00:00', 102))

You may need to remove rows from one table depending on conditions applied to another table or tables. The best way to accomplish this is to use the IN clause, which is applied over a field included on one index. The following example removes the history information about those products matching the same conditions we used in the previous sample (included in the sample files as \Ch11\AdvWorks\Queries\Sample02.sql).

 DELETE FROM [AdventureWorks].[Production].[ProductInventory]       WHERE ProductID in    (  SELECT     ProductID       FROM       Production.Product       WHERE     (MakeFlag = 1)       AND (ReorderPoint BETWEEN 200 AND 600)       AND (SellStartDate < CONVERT(DATETIME, '2000-01-01 00:00:00', 102))     )

Another way to remove these rows is to create a WHERE condition that is applied over relationships between the tables, but to specify the table from which you want to delete the rows. This code is included in the sample files as \Ch11\AdvWorks\Queries\Sample03.sql.

 DELETE Production.ProductInventory    FROM Production.ProductInventory       INNER JOIN Production. Product    ON Production.ProductInventory.ProductID =        Production. Product .ProductID    WHERE (Production. Product .MakeFlag = 1)      AND (Production. Product .ReorderPoint BETWEEN 200 AND 600)      AND (Production. Product .SellStartDate <        CONVERT(DATETIME, '2000-01-01 00:00:00', 102))

Using Relationships to Perform Deletions

When you design a database, you can establish rules about how the deletion process occurs. In a relationship between tables, you can configure the relationship to delete in cascade during the deletion process of a row.

Assume that the Purchase Order business rule establishes that it is possible to delete cancelled purchase orders. In this scenario, you can use data definition language (DDL) to define that when some process deletes a row from the PurchaseOrderHeader table, the corresponding rows in the PurchaseOrderDetails table will automatically be deleted as well. The DDL would look something like the following (included in the sample files as \Ch11\AdvWorks\Queries\Sample04.sql):

 ALTER TABLE Purchasing.PurchaseOrderDetail ADD CONSTRAINT    FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID FOREIGN KEY    (    PurchaseOrderID    ) REFERENCES Purchasing.PurchaseOrderHeader    (    PurchaseOrderID    ) ON UPDATE  NO ACTION     ON DELETE  CASCADE GO

If you use SQL Server Management Studio to create your database objects, pay attention to the Insert And Update Specification option when creating a relationship, as shown in Figure 11-1.

Figure 11-1: Specifying properties of foreign key relationhips.

Important

Be careful when using the Cascade feature. Deleting rows with a dependency on other tables can be dangerous, and important information can be lost. Referential integrity will prevent you from losing the rows that you need. When you enable the Cascade Delete feature, you must be sure that none of your essential data will be lost.