Let s Do CRUD with Data

Let's Do CRUD with Data

As previously mentioned, you can really only do four things with data: Create it, Read it, Update it, and Delete it. These four operations form the basis of what is commonly called CRUD operations. So far, you've been doing a lot of reading using the SELECT statement. Now it's time to work with data in a way that will affect the data in your database.

Adding Records

Adding rows is done using the INSERT statement. Inserts can be performed one row at a time or on multiple rows, depending on the technique, and target only one table. Before attempting to insert data into a table, it is important to know the following:

• Which columns require values

• Which columns have data-integrity constraints

• Which columns are managed by the database through functions

• Which columns have default values or allow null values

• What the data types of the destination columns are

To get started, take a look at the Product table in design view (see Figure 9-2).

Figure 9-2:

This table has 27 columns, but only 1 column, the Name, requires a value because every other column either has a default value or accepts NULL. The ProductID column is the primary key. Because it is designated as an identity column, an incremental value will automatically be generated for this column by the database.

INSERT Statement

If you only need to provide a value for this column, the statement would be quite simple:

 INSERT INTO Product (Name)  SELECT 'Widget 1' 

I'll pick this statement apart in the following sections and show you some variations of this type of query.

The INTO keyword is optional and is often omitted. I like to include it because I think it reads more like natural language. Two different styles are used with the INSERT statement, and each has its own subtleties. Generally, you could select one technique and pretty much use it for all of your insert operations. I'll show you some examples of each and let you decide when to use them.

INSERT. . . Values

The pattern of the INSERT statement is to provide a list of column names and then a list of values in parentheses. If values are provided for all columns or all columns with the exception of the identity column (only one identity column is allowed per table), no column name list is required. This technique is used to insert only one row into the table. The Contact table is one of the simplest in the Adventure Works2000 database so it makes a good example. I'm going to include values for four of the columns. Note that I'm skipping the ContactID column. If I were to include a value for the ContactID column, my INSERT statement would fail, because this column can only be managed by the IDENTITY() function of the database. In the INSERT statement itself, I include the column names and in the Values list, I provide a corresponding value:

 INSERT INTO Contact (Salutation, FirstName, LastName, Phone)  VALUES ('Ms.', 'Pebbles', 'Flintstone', '123-4567') 

Note the use of single quotes to denote literal values to be inserted into the table. If I had a numeric value to insert, I would not use the quotes. The order of the values must match the order of the columns in the table if no column list is provided, with the exception of any identity column that is omitted as the following example shows:

 INSERT INTO Contact  VALUES ( ‘Mr.’,  , DEFAULT, ‘111-123-4567’, , ‘IL0veWilm@’, 

Note

NULL and DEFAULT values are covered in the section titled "Inserting NULL, Defaults, and other Column Considerations."

INSERT. . . SELECT

This form of the INSERT statement is similar to the previous values form except it uses a SELECT statement to provide values. As discussed in earlier chapters, the SELECT statement can return scalar literal values, as well as sets of literal values, without even hitting a table in the database. Because SELECT can be used to include a variety of different types of values (from tables, joins, unions, groups, aggregates, and literals), this is a much more flexible technique. Practically anything can be inserted as long as the column count and data types match. Because a SELECT statement can return multiple rows, this would result in multiple rows being inserted into the destination table. Following is the equivalent of the previous example using this technique. In this example, columns and values for the MiddleName and NameStyle columns have been omitted. This insert is successful because NULL will automatically be inserted (because these columns allow the NULL value):

 INSERT INTO Contact (Salutation, FirstName, LastName, Phone)  SELECT 'Mr.', 'Bam Bam', 'Rubble', '234-5678' 

Because I've executed the previous two INSERT statements, I'll just return all the records from the Contact table (SELECT * FROM Contact) and scroll down to view the last two rows, shown in Figure 9-3.

Figure 9-3:

You can see that the ContactID column contains an auto-generated, sequential identity value. The MiddleName column is set to NULL and the NameStyle is set to its default value of 0.

Inserting NULL, Defaults, and other Column Considerations

Different settings at the server, database, and user session level for SQL Server can affect the way NULL is available for a particular column. Although not necessarily complicated, this can be a particularly confusing issue because there are a number of variable elements to be considered. Before you read on, please keep in mind that this only becomes an issue if the default settings are altered and, in any case, the situation can be remedied by explicitly defining columns in tables as NULL or NOT NULL.

Database properties can be modified through SQL Server Enterprise Manager or the SQL Server Management Studio or by using SQL script. In Enterprise Manager or Management Studio, right-click the database icon and choose Properties from the menu. For reference, I've included these interfaces. In both examples, the ANSI_NULL_DEFAULT option is set to False.

Figure 9-4 shows the Properties dialog (Options tab) for SQL Server 2000.

Figure 9-4:

Figure 9-5 shows the Properties dialog (Options tab) for SQL Server 2005.

Figure 9-5:

The same settings can be applied using script similar to the following:

 sp_dboption 'AdventureWorks2000','ANSI Null Default','False' 

The following script will override the database default setting when executed prior to creating or altering a table:

 SET ANSI_NULL_DFLT_ON ON 

When a table is created, the database settings apply unless they are explicitly overridden in the session. When ANSI_NULL_DEFAULT is set to ON (True), all user-defined columns and data types that have not explicitly been defined as NOT NULL during the creation or altering process of the table default to allowing NULL values. This is the opposite of the default setting for SQL Server when installed. It is good practice to explicitly define NULL and NOT NULL regardless of the current or eventual resetting of ANSI_NULL_DEFAULT. If this setting has been altered, the INSERT statements assume that the opposite will fail. Note that Constraints (Check and Default) will apply before NULL.

Looking at the Contact table, the columns that are explicitly NOT NULL are as follows:

• ContactID

• LastName

• NameStyle

• ModifiedDate

• rowguid

Of these, the ContactID value is supplied by the IDENTITY() function. The NameStyle, ModifiedDate, and rowguid are supplied by Default Constraints if no values are explicitly provided (the rowguid's Default Constraint employs the NEWID() function that can be used to generate a Globally Unique Identifier [GUID]). All other columns, as previously stated, are explicitly defined to either prohibit or allow NULL values.

Inserting Rows from Another Table

Using the Insert. . . Select pattern, I can bring data from another table or complex query. Conveniently, the Employee table contains human being–type data I can use to populate the Contact table. Columns in my select list don't have to have the same names but do have to be compatible data types. First, I'll just use a SELECT statement to view the Employee row I intend to insert:

 SELECT FirstName, LastName, EmergencyContactPhone FROM Employee WHERE EmployeeID = 265 

This returns one record, as shown in Figure 9-6.

Figure 9-6:

It is a best practice to always test the SELECT statements that will be used in transaction statements to prevent failures within the Insert, Update, or Delete statement. Step two is to add the INSERT statement to the front of this query and make sure the columns match up between the two lists:

 NSERT INTO Contact (FirstName, LastName, Phone) SELECT FirstName, LastName, EmergencyContactPhone  FROM Employee WHERE EmployeeID = 265 

Finally, execute a SELECT statement against the Contact table and scroll to the bottom of the list to check the result, as shown in Figure 9-7.

Figure 9-7:

Great care should be taken to limit the size (rows returned) of the SELECT statement with a WHERE clause when querying another table. Each row requested in the SELECT statement will remain locked from changes during the Insert process. This will momentarily block other requests for operations performed on the same table. In an active, multi-user database, nearly every operation carries a statistical possibility of coinciding with a conflicting operation. Most of the time, these operations are simply queued to execute in turn. The database engine will retry a blocked transaction before it times out in error. However, if two competing operations happen to lock the same tables and are waiting for the availability of locked objects before they can continue, this could result in a deadlock situation in the database. The specific behavior will depend on transaction-level locking options and database settings.

SELECT INTO

At times, you may want to populate a new or a temporary table with the results of a query, a technique that can be a real time-saver. Instead of creating a new table the old-fashioned way (by defining all of the columns and data types), and then using an INSERT statement to populate the new table from existing data, this statement simply creates the table on-the-fly using the output and data types from a SELECT statement. Although we try to avoid storing duplicate data when it's not necessary, sometimes it's just easier to copy results into a table for temporary storage — sort of a snapshot of information that alleviates locking of the transactional table by read requests for reports and so on. This might be especially useful to support a complicated report. The results of a complex query may be written to a table either in the current database, a reporting database or warehouse, or the TempDB database, where the data can be reused in other queries. The INSERT INTO statement requires that you target an existing table. If you want to create a new table from the results of the query (from one or multiple tables), use the SELECT INTO statement. In this example, I select a subset of contact records and create a new table called MyContacts:

 SELECT * INTO MyContacts  FROM Contact WHERE Salutation = 'Sr.' 

Keep in mind that the new table is a permanent member of the database like any other table, unless you have placed it in the TempDB database as a temporary table. Typically, in a production environment, a database administrator wouldn't grant the privilege to create new tables to all users. If you only need to use the data for a short time, you can create a temporary table by starting the table name with a pound sign (#) or double pound sign (##). A single pound sign denotes a local temporary object that is only visible from the current connection. A double pound sign denotes a global temporary object that is visible to all connections as long as the connection that created it is still active. Here is the same statement that creates a temporary table:

 SELECT * INTO #MyContacts  FROM Contact WHERE Salutation = 'Sr.' 

The difference between this and the previous example is that the table will be created in the TempDB database. When the connection used to create this table is closed, SQL Server automatically removes the table and reclaims the storage space. The local temporary table is often used in a stored procedure where cursors are inefficient. The stored procedure first creates the temporary table using a SELECT INTO statement and then returns rows from this table as a result set. When the stored procedure completes and the connection is closed, the local temporary table in TempDB is dropped.

Managing Inserts Using Stored Procedures

In a large-scale, production environment, administrators often limit users' ability to insert, update, and delete records directly. Implementing such restrictions while accommodating all user and application needs requires careful planning and can be a lot of work. One popular approach is to deny users all transactional activity against tables and create stored procedures to manage the inserts, updates, and deletes. For each major entity in the database, there will typically be at least three stored procedures: to insert, update, and delete records in that table. Using stored procedures offers the added benefit of enforcing any special business rules, simplifying complex tables and returning custom error messages.

Earlier you looked at the design of the Product table and should recall that several columns don't require values. For the sake of simplicity, I will create a stored procedure to insert rows into the Product table, providing values for just a few of these columns. Chapter 13 takes a comprehensive look at stored procedures. For now, I'll keep this simple.

 REATE PROCEDURE spIns_Product @ProductName    nVarChar(50), @ProductNumber  nVarChar(25), @StandardCost   Money AS INSERT INTO Product (name, ProductNumber, StandardCost) SELECT @ProductName, @ProductNumber, @StandardCost RETURN @@Identity 

@StandardCost. These parameters are used to pass values to the INSERT statement. The global constant @@Identity is used to obtain the last identity value, in this case, the new ProductID value generated by this INSERT statement. To test this procedure, execute the following SQL statement:

 spIns_Product 'Gadget', '23456', 49.95 

Modifying Records

When any data in a record or group of records is modified, the user or application making the changes must have exclusive access to the record or records for a short period of time. This locking mechanism is an important part of SQL Server's transaction management model. So, what exactly gets locked? This depends on different factors. SQL Server supports row-level locking and, when feasible, will lock a single row so that neighboring rows don't get locked, affecting other users. This was an issue in earlier versions of SQL Server that supported only page-level locking. In truth, SQL Server will sometimes lock all of the records in an 8KB page, SQL Server's native storage allocation unit. It may do this because it deems this method to be faster or more efficient than locking individual records. Based on inter-table relationships, when a row is modified in one table, locks may be placed on dependent rows in the related table. It may also choose to lock an entire table, groups of tables, or even the entire database, under certain circumstances.

The database engine must make decisions about how it will process each transaction. Based on the scope or number of records involved in a transaction, different locking options may be invoked to manage the transaction as efficiently as possible. With rare exception, this should be completely transparent to all users and operations. Any concurrent or conflicting requests are simply queued and executed in turn. Only after a transaction request has waited several seconds will it time-out and return an error. Fortunately, SQL Server manages record-locking and data modification automatically. Understanding the fundamentals of SQL Server's locking behavior will help you work with the database engine as you modify data. Multi-table updates and other conditions where you should be mindful of these issues are discussed at the end of this chapter.

UPDATE Command

Data is modified one table at a time. A single UPDATE statement can affect one, many, or all records in a table depending on filtering criteria. The syntax is uncomplicated. Column values are modified using the SET keyword.

Filtering Updates

There is no undo feature in SQL Server, short of restoring a backup. Because an update can affect many records, it's important to be absolutely sure of the records you intend to modify. To be cautious, a trial is advisable using only a SELECT query. Inspect the rows returned and then, after you verify that these are the rows you want to change and that the target values are correct, add the UPDATE command to your query. Here's an example. My objective is to raise the standard cost for all mountain bikes by 10 percent. I'm going to break this down into steps to verify the records and my calculation before actually performing the update.

The first step is to perform a simple SELECT query. This query returns the product name and current cost:

 SELECT Name, StandardCost  FROM Product WHERE ProductSubCategoryID = 1 

In the results, shown in Figure 9-8, I verify that these are all mountain bikes and that the StandardCost column contains the values I want to modify.

Figure 9-8:

The next step is to calculate the new value for the StandardCost column. I'll create an additional column with the calculated value:

 SELECT Name, StandardCost, StandardCost * 1.1 FROM Product WHERE ProductSubCategoryID = 1 

Now, I check the calculated value and take a quick look over the entire list to make sure everything is in order (see Figure 9-9).

Figure 9-9:

The calculated value is correct — a 10 percent increase — and the row selection is correct, so I'll move on to the next step and prepare to modify these rows.

The FROM clause gets moved to the top and becomes the UPDATE statement. I'm dropping the Name column because it's not affected by this query and then I'm using the remaining two references to the StandardCost column to form the SET statement:

 UPDATE Product SET StandardCost = StandardCost * 1.1 WHERE ProductSubCategoryID = 1 

When this statement is executed in Query Analyzer or the Query Editor, no results are returned. The following message is displayed on the Messages tab:

Command(s) completed successfully

To verify the results, I'll execute the first query again:

 SELECT Name, StandardCost  FROM Product WHERE ProductSubCategoryID = 1 

This time, the StandardCost values have increased by 10 percent, as shown in Figure 9-10.

Figure 9-10:

When working with important data, I try to make it a point to test my queries in this way before actually performing the update. Often, I won't if it's a simple query and I'm certain that I have it right the first time. However, I've learned that it's better to err on the side of caution. In any case, make sure you have a current backup copy of the data just in case things don't go as planned.

Now, let's look at some variations of the UPDATE statement. In the previous example, I updated one column value. Modifying multiple values is quite easy. Each column value assignment is included in a comma-delimited list in the SET statement. This statement shows how to update a product record with multiple column values:

 UPDATE Product SET Name = 'Trail Bike-500 Grey, 40mm' , StandardCost = 489.00 ,  Color = 'Grey' WHERE ProductID = 8 

I can view all mountain bikes with this query:

 SELECT ProductID, Name, StandardCost, Color  FROM Product WHERE ProductSubCategoryID = 1 

This, of course, includes my new grey trail bike with the inflated price, as shown in Figure 9-11.

Figure 9-11:

Updating Rows Based on Multiple Tables

Sometimes you will need to modify records in one table based on conditions in another table. This can be accomplished using a join or subquery. The rule is that you can only update column values in one table. In the following example, I join the SalesOrderHeader and SalesOrderDetail tables. Based on criteria in the header table, I modify the order quantity value in the related detail row:

 PDATE   SalesOrderDetail SET     OrderQty = 10 FROM    SalesOrderDetail INNER JOIN SalesOrderHeader          ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID WHERE   SalesOrderHeader.PurchaseOrderNumber =  

In this case, there is only one related detail row but there could be multiple detail rows related to the header row.

When setting values, it's common to derive those values from various system functions. One of the simplest and most common of these functions is GETDATE(), which returns the current date and time. Using this function, I'll stamp an order record to indicate that it has been shipped. First, I'll set the stage for this scenario. In the AdventureWorks2000 database, all of the order records have already been marked as having been shipped. Suppose that a customer calls your customer service department asking about their order. You look up the order and see that it is marked as having been shipped to the customer. Upon careful investigation, you discover that the package was returned because the customer's address was incorrect. Your first order of business is to update the record to indicate that it was not shipped (or at least not received by the customer). The order in question is SalesOrderID 5005. This statement will remove the ship date, setting it to null:

 UPDATE SalesOrderHeader SET ShipDate = Null WHERE SalesOrderID = 5005 

Now you obtain the correct address and schedule the package for shipment. Once confirmed, you update the order record with the current data and time using the GETDATE() function:

 UPDATE SalesOrderHeader SET ShipDate = GETDATE() WHERE SalesOrderID = 5005 

Updating Using Views

Most database professionals agree that the traditional purpose for views is to provide a read-only view of data from tables. One of the most compelling capabilities is that sensitive data can be secured and protected — both selected rows and columns hidden from the user's view. Complicated queries and joins can be represented as if they were a single table. We don't normally think of these "virtual tables" as being updatable but it is possible to update records through views under certain conditions. Updates can only affect the columns of one table at a time. Further, values created by grouping and aggregation cannot be updated. As a rule, if you intend to create a view to support updates, it should either reference only one table or multiple tables through inner joins. All required columns must be included, and update statements must include only references to a single table within the view.

Updating Records Using Stored Procedures

Probably the most comprehensive and secure method for managing record updates is to allow updates only through stored procedures. When this approach is taken, a separate stored procedure is typically used to modify the records for each major entity in the database. The following example is a stored procedure to modify a record in the Product table. I've made a point to include all of the columns in this table just to demonstrate what this would normally look like:

 LTER PROCEDURE spUpd_Product @ProductID            Int , @ProductName          nVarChar(50) , @ProductNumber        nVarChar(25) , @DiscontinuedDate     DateTime = Null , @MakeFlag             Bit = 1 @StandardCost           Money = Null , @FinishedGoodsFlag     Bit = 1 , @Color                 nVarChar(15) = Null , @SafetyStockLevel      SmallInt = Null , @ReorderPoint          SmallInt = Null , @ListPrice             Money = Null , @Size                  nVarChar(50) = Null , @SizeUnitMeasureCode   nChar(3) = Null , @ProductPhotoID        Int = Null , @WeightUnitMeasureCode nChar(3) = Null , @Weight                Float = Null , @DaysToManufacture     Int = Null , @ProductLine           nChar(2) = Null , @DealerPrice           Money = Null , @Class                 nChar(2) = Null , @Style                 nChar(2) = Null , @ProductSubCategoryID  SmallInt = Null , @ProductModelID        Int = Null , @SellStartDate       DateTime = Null , @SellEndDate            DateTime = Null AS UPDATE Product SET  Name = @ProductName , ProductNumber = @ProductNumber , DiscontinuedDate = @DiscontinuedDate , MakeFlag = @MakeFlag , StandardCost = @StandardCost , FinishedGoodsFlag = @FinishedGoodsFlag , Color = @Color , SafetyStockLevel = @SafetyStockLevel , ReorderPoint = @ReorderPoint , ListPrice = @ListPrice , Size = @Size , SizeUnitMeasureCode = @SizeUnitMeasureCode , ProductPhotoID = @ProductPhotoID , WeightUnitMeasureCode = @WeightUnitMeasureCode , Weight = @Weight , DaysToManufacture = @DaysToManufacture , ProductLine = @ProductLine , DealerPrice = @DealerPrice , Class = @Class , Style = @Style , ProductSubCategoryID = @ProductSubCategoryID , ProductModelID = @ProductModelID , rowguid = NEWID() , SellStartDate = @SellStartDate , SellEndDate = @SellEndDate WHERE ProductID = @ProductID 

Note that most of the parameters in the list of input parameters (the variable names preceded with @) are assigned default values. Some accept Null whereas others, such as the @MakeFlag and @FinishedGoodsFlag, are set to the bit value 1, or True. This is so these parameters are optional when executing the procedure. The rowguid column is set using the NEWID() function to generate a unique value. This may or may not be appropriate logic in an update procedure. I've included this just to demonstrate a variety of techniques for setting values.

This stored procedure is actually simplified. In production, procedures typically include some conditional business logic and error-handling code. These scripts can be time-consuming and cumbersome to write. However, once written and debugged, using stored procedures can significantly simplify data management going forward. Chapter 10 revisits this stored procedure when discussing the finer points of database programming.

To test the procedure, I'm going to update the record I inserted using the insert stored procedure, spIns_Product. The first thing I need to do is get the ProductID value for this record. I select all product records and find that the ProductID value is 1302. The following script can be used to modify this record using the procedure:

 spUpd_Product 1302, 'Super Gadget Deluxe', '98765' 

Because all of the input parameters after the first three have values set in the body of the procedure, I must supply values only for the first three. All other columns will be set to these default values.

Removing Records

Removing records from a table is very easy to do — maybe too easy. Depending on your viewpoint, this convenience could be a blessing or a curse. Take caution before you start practicing your deleting skills on production data. Having said that, I'm going to ask SQL Server to delete all of the product records in the AdventureWorks2000 database.

DELETE Command

As you can see, this is a simple statement. There is no need to address specific columns because the DELETE statement removes entire rows of data:

 DELETE FROM Product 

The FROM clause is actually optional. Personally, I find it a little easier to read this statement with the FROM clause but that's a minor point. This statement does the same thing:

 DELETE Product 

Did it work? No. SQL Server returned an error — fortunately:

erver: Msg 547, Level 16, State 1, Line 1     DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Product_BillOfMaterials_ComponentID' 'AdventureWorks2000', table      The statement has been terminated. 

Due to the foreign key constraint displayed in the error the statement failed. The database won't allow rows to be deleted if there are related rows in another table. What about the product records that do not have related records in another table? That's not going to happen either, because every Insert, Update, and Delete statement is automatically wrapped into a transaction. You'll recall that transactions are an all-or-nothing proposition; either all the records are affected or none of them are. In this case, no records are affected.

Embrace the WHERE Clause

Just as when updating records, it's important to test the water and make sure you delete the records you intend to. The Product table has foreign key constraints defined with several tables, which make it difficult to delete existing records. For demonstration purposes, I'll add a few products that we can play with:

 NSERT INTO Product (Name, ProductNumber, StandardCost, ListPrice) SELECT ‘Widget 2002’,  INSERT INTO Product (Name, ProductNumber, StandardCost, ListPrice) SELECT ‘Widget 2003’,  INSERT INTO Product (Name, ProductNumber, StandardCost, ListPrice) SELECT ‘Widget 2004’,  INSERT INTO Product (Name, ProductNumber, StandardCost, ListPrice) SELECT ‘Widget 2005’,  

Your objective is to remove all product records for products that would be unprofitable to sell, where the ListPrice is less than the StandardCost. Before actually deleting any records, test your criteria using a SELECT statement:

 SELECT ProductID, Name, ProductNumber, ListPrice, StandardCost  FROM Product WHERE StandardCost > ListPrice 

Two rows meet these criteria, as shown in Figure 9-12.

Figure 9-12:

After verifying that these are the records you intend to delete, write the DELETE statement, appending this WHERE clause to the end to affect the same records:

 DELETE FROM Product WHERE StandardCost > ListPrice 

These two records are deleted.

Deleting Records Based on Another Table

You may need to remove records from one table based on conditions in another table. Usually there will be a relationship of some kind between these tables. Deletes can be facilitated using any type of join or subquery expression between multiple tables.

How It Works

In this scenario, suppose that one of your salespeople, Amy Alberts, has left the company and you want to archive all of the related sales orders and detail records. You've already copied these records to their respective archive tables so now all you need to do is delete them. Amy was the salesperson for 39 sales order records with 586 related order detail records.

Try It Out

Before you continue I'm going to recommend that you back up the AdventureWorks2000 database just in case things don't go as planned. In this exercise, you should only remove a small number of records but it's better to be safe than sorry.

Figure 9-13 shows the relationship between the tables involved. By specifying an EmployeeID (on the left side of the diagram), you can delete related SalesOrderDetail rows. The SalesOrderHeader, which defines orders, is related to the SalesPerson table through the SalesPersonID foreign key. This is a many-to-many intersect table between Employee and SalesOrderHeader. Order details, in the SalesOrderDetail table, are related to orders through the SalesOrderID foreign key column. Because it's on the outer side of the relationships and nothing else depends on it, begin by deleting rows from this table first.

Figure 9-13:

There are a few techniques and this is one of the easiest. Earlier you saw how the IN() function is used to compare a value to a comma-delimited list of values. The same function can be used to compare a value with a single-column result set. The mechanics are simple: create any SELECT query that returns a single column. Pass this query to the IN() function used in the WHERE clause of a DELETE statement:

 ELETE FROM SalesOrderDetail WHERE    SalesOrderID IN( SELECT SalesOrderDetail.SalesOrderID FROM SalesOrderHeader INNER JOIN SalesPerson ON SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID INNER JOIN Employee ON SalesPerson.SalesPersonID = Employee.EmployeeID WHERE Employee.FirstName = ‘Amy’ 

This deletes the 586 order detail records. Removing the sales order records is easy and requires only a simple change. Because the SalesOrderID is also the identifying column for records in the SalesOrderHeader table, all you need to do is change the table name in the DELETE statement:

 ELETE FROM SalesOrderHeader WHERE    SalesOrderID IN( SELECT SalesOrderDetail.SalesOrderID FROM SalesOrderHeader INNER JOIN SalesPerson ON SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID INNER JOIN Employee ON SalesPerson.SalesPersonID = Employee.EmployeeID WHERE Employee.FirstName = ‘Amy’ 

This deletes the 39 sales orders. It was necessary to delete the order detail rows first because these records depend on the existence of the sales order records in the SalesOrderHeader table.

TRUNCATE TABLE

The DELETE command is a logged operation. For deletes on a small number of rows or manual operations, there may be a negligible difference in performance. However, for repeated and automated deletes, or on a large volume of records, skipping the transaction logging can improve performance dramatically.

The TRUNCATE TABLE command does only one thing — it removes all of the records in a table without logging a transaction. It performs the deletes in a manner far more efficiently than the DELETE command. You cannot filter specific rows to delete or use any kind of selection criteria. Please do not execute the following query. This statement will very efficiently remove all order detail records:

 TRUNCATE TABLE SalesOrderDetail 

I'm going to perform a performance test, comparing the Delete and Truncate techniques. I don't intend for you to follow along because it would take a bit of work to set this up. I've created two additional databases, called AW_1 and AW_2. Using a DTS package, I've copied all of the objects and data from AdventureWorks2000 into these two databases. The following script gets the current time before and after performing each of these two operations. After each, I compare the two times and display the number of milliseconds that it took to complete the operation.

 ECLARE @starttime DateTime DECLARE @endtime DateTime DECLARE @totaltime Int USE AW_1 -- Copy 1 of AdventureWorks2000 DB SET @starttime = GetDate() DELETE FROM SalesOrderDetail SET @endtime = GetDate() SET @totaltime = DateDiff(ms, @starttime, @endtime) PRINT ‘Time to Delete:   ‘ + CONVERT(VarChar(10), @totaltime) USE AW_2 -- Copy 2 of AdventureWorks2000 DB SET @starttime = GetDate() TRUNCATE TABLE SalesOrderDetail SET @endtime = GetDate() SET @totaltime = DateDiff(ms, @starttime, @endtime) PRINT ‘Time to Truncate: ‘ + CONVERT(VarChar(10), @totaltime)  

The results are as follows:

(121371 row(s) affected)     Time to Delete: 14030 Time to Truncate: 130

The Truncate technique takes less than 1 percent of the time it took to process the DELETE statement. The Delete operation took about 14 seconds and the Truncate operation took about 1/7th of a second. That's a big difference! This is because transactional operations are physically written to the transaction log and then rolled forward into the table, one row at a time. The TRUNCATE statement doesn't do all of this. It simply deallocates all of the data pages for a table. In reality, data in these pages is not actually changed, but the pointer entries in the index allocation map for these pages are removed. Eventually, data left in the pages gets overwritten but is not really removed.

The only limitation of the TRUNCATE command is that it is a privileged command and only database owners or system administrators can execute it, even if it is encapsulated in a stored procedure.