C H A P T E R  6

Manipulating Data

The data stored in most databases is not static. The application users constantly add data to tables as customers place orders, the company hires employees, and the payroll department writes checks. Automated processes periodically load new data into reporting databases, such as data warehouses, and into production systems. Users and processes also update existing rows or delete rows from tables.

In Chapters 1 through 5, you learned how to retrieve data from SQL Server. These skills are important for generating reports and displaying data. These skills will come in handy as you learn to insert new rows, update the values in existing rows, and delete rows from tables. This chapter covers how to manipulate data in many different scenarios, such as by using one table, joins, and subqueries.

Inserting New Rows

There are many ways to add new rows to tables in SQL Server databases. Be aware that there are other tools such as SQL Server Integration Services (SSIS) that you can use to load data into SQL Server, but because this book is about T-SQL, this section covers the T-SQL statements to insert data. To learn more about SSIS, read Pro SQL Server 2011 Integration Services by Francis Rodrigues and Michael Coles (Apress, 2012), and also SSIS Design Patterns by Andy Leonard, Tim Mitchell, Jessica Moss, and Michelle Ufford (Apress, 2012).

Run the following code to create a table that you will populate with data in this section:

Adding One Row with Literal Values

Adding one row with literal values is the simplest way to add data to an existing table. For example, a user may fill out a Windows or web form in an application and click Save. At that point, the application builds and sends a single INSERT statement to SQL Server containing the values that the user entered in the form. To insert new rows, you will use the INSERT statement. The syntax of the INSERT statement, which has two variations, is simple.

The INTO keyword is optional, but I like to include it. Type in and execute the code in Listing 6-1 to learn this technique. The last statement displays the inserted data.

Figure 6-1 shows the results of query 7. The INSERT INTO clause specifies the table name and optionally the column names. Statement 1 inserts the row using the VALUES clause. Notice that parentheses surround the literal values in the statement. Statement 2 uses a slightly different syntax with the keyword SELECT. In this case, you could successfully run the SELECT part of the statement because it is a valid statement by itself.

Figure 6-1. The results after inserting six rows

Both statements 1 and 2 specify the column names in parentheses. The order of the values must match the order of the column names. Statements 3 and 4 look very similar to the first two statements, but these statements don’t specify the column names. Although not specifying the column names will work some of the time, the best practice is to specify the columns. Not only does this help clarify the code, it often, but not always, keeps the code from breaking if new nonrequired columns are added to the table later.

Notice that statement 5 inserts NULL into the MiddleName column. Statement 6 just leaves MiddleName out of the statement altogether. Both of these statements work because the MiddleName column is optional.

Avoiding Common Insert Errors

The statements in the previous section successfully added six rows to the dbo.demoCustomer table because they were carefully written to avoid breaking any of the constraints and column requirements. Listing 6-2 demonstrates several invalid statements. Type in and execute the code to learn about some of the things that can go wrong when inserting data into tables.

Figure 6-2 shows the error messages that result from running Listing 6-2. Statement 1 attempts to add another row with the CustomerID value 1. Since a row with CustomerID 1 already exists in the table, the INSERT statement violates the primary key constraint. Because the primary key of a table uniquely identifies a row, you may not insert duplicate values. If the primary key is a composite key, however, you can have duplicate values in any of the columns but not duplicates of the entire key. A primary key may not contain any NULL values in the key columns.

Figure 6-2. The results of attempting to insert rows with invalid INSERT statements

Statement 2 violates the NOT NULL constraint on the FirstName column. Every row must contain a non-NULL value in the FirstName and LastName columns.

The database engine doesn’t discover problems with statements 1 and 2 until the code runs. The problems with statements 3 and 4 are compile errors that cause the entire batch to fail. To show all the error messages for the listing, the word GO separates statements 3 and 4 into their own batches. In fact, even the PRINT statement will not run if it is contained in the same batch as these statements. The intent of statements 3 and 4 is to insert a row with a NULL MiddleName. Because statement 3 doesn’t specify the column names, the database engine expects a value for each of the four columns in the table definition. Since the statement supplies only three values, the statement fails. Statement 4 does supply the column names, but the VALUES clause doesn’t supply the same number of values. Once again, the statement fails. Statement 5 also contains a compile error. It attempts to insert a string value when the column, CustomerID, accepts only an integer.

Inserting Multiple Rows with One Statement

You can write one statement using a feature called row constructors to insert multiple rows. With versions prior to SQL Server 2008, you could write an INSERT statement with a UNION query to avoid writing multiple INSERT statements. Type in and execute Listing 6-3 to learn how to use both techniques.

Figure 6-3 displays the rows inserted by Listing 6-3. Statement 1 uses the UNION query technique. You can successfully select just the five lines that make up the UNION query part of the INSERT statement and run it by itself. Statement 2 demonstrates how to use the row constructor technique. By using row constructors, you can specify multiple lists of values, separated by commas, in one VALUES clause.

  

Figure 6-3. Inserting multiple rows with one INSERT statement

Inserting Rows from Another Table

So far, you have learned how to insert literal values into a table. Often you will need to insert data from one table or query into another table. For example, you may need to load production data into a data warehouse. Often application programmers think about data in terms of individual rows and so insert data one row at a time when it is possible, and almost always more efficient, to insert more rows at once. They will often loop through one table, saving the values in variables, and then insert the values in the second table.

My favorite analogy involves 2 boxes and 100 car keys. How would you move the keys from one box to the other most efficiently without picking up either of the boxes? Would you pick up one key at a time and move it? No, you would probably grab up all the keys if you could and just move them all at once. If you could not pick up all the keys in one trip, you could at least move them in two or three batches. Listing 6-4 shows how to import rows from one table into another all within one statement. This example demonstrates “moving all the car keys at once.” Type in and execute the code to see how this works.

Figure 6-4 shows some of the rows added to the dbo.demoCustomer table by Listing 6-4. Statement 1 inserts the rows from the Person.Person table where the BusinessEntityID is between 19 and 35. Statement 2 inserts the rows from a query that joins the Person.Person and Sales.SalesOrderHeader tables. The SELECT parts of the statements are valid queries that you can run without the INSERT clauses. You can use any of the techniques you have learned so far to write SELECT statements to insert data into a table as long as the data selected meets the constraints and requirements of the target table.

Figure 6-4. The partial results of adding rows from another table

Inserting Missing Rows

You have seen what can happen when you attempt to insert a new row that violates the primary key. You can use the technique in the “Using OUTER JOIN to Find Rows with No Match” section of Chapter 4 to insert rows into one table that don’t exist in the source table. Type in and execute the code in Listing 6-5 to learn how this works.

Figure 6-5 shows the customer count after adding the missing rows. By using the LEFT OUTER JOIN and checking for NULL values in the target table, the SELECT part of the INSERT statement finds the missing rows and inserts those rows into the table. As mentioned in the “Inserting Rows from Another Table” section, any valid SELECT statement may be used to insert rows into a table. If you run the same code a second or third time, you will find that the row count doesn’t change. Since the code inserted all the missing rows, there are no new rows to insert after the first time.

Figure 6-5. The results of checking the row count after adding the missing rows

Creating and Populating a Table in One Statement

The SELECT INTO statement allows you to create a table and populate it with one statement. Developers often use this technique to create temporary tables, or work tables.

Type in and execute the code in Listing 6-6 to learn how to use this technique. The first part of the code drops the dbo.demoCustomer table because the SELECT INTO statement will fail if the table already exists.

Figure 6-6 displays the partial results. Statement 1 lists the columns and an expression along with the word INTO and the name of the table to create. The resulting table contains a column, FullName, that the statement created with the expression. Even though you could write a query that doesn’t specify an alias for the expression, you must specify the alias for the expression when writing SELECT INTO statements. The database engine uses the column and alias names when creating the new table.

Figure 6-6. The partial results of creating and populating a table with SELECT INTO

Figure 6-7 shows the table definition of the dbo.demoCustomer table found by navigating to the table in the Object Explorer window of SQL Server Management Studio. You may have to right-click Tables and select Refresh to see the new table. Except for the missing primary key, the column definitions match the columns from the Person.Person table, which was the source of the data. The data in the FullName column can be 152 characters—the three name columns plus the two spaces.

Figure 6-7. The dbo.demoCustomer table definition

Developers often use the SELECT INTO statement to create an empty table by adding 1=2 to the WHERE clause. Because one never equals two, the statement creates the table but doesn't add any rows. Even if you want to create and populate a work table, the performance of the entire system is often better by creating the empty table first and then populating it with a regular INSERT statement when you are working with a large number of rows. This is because the SELECT INTO statement locks system tables that can cause problems for other connections. Using a CREATE TABLE first and then populating it locks the system tables only momentarily. Using the SELECT INTO syntax locks the tables until the entire statement completes.

Inserting Rows into Tables with Default Column Values

Column definitions often specify a default value, called a default constraint, if the INSERT statement doesn’t supply a value for the column. This is different from inserting NULL. When inserting NULL, you specify NULL in the INSERT statement, or the NULL value is a result of the SELECT statement used to insert the data. You might also omit the column from the INSERT statement to insert NULL. If the column definition specifies a default constraint, you can just leave that column out of the INSERT statement to automatically insert the default value. Run the code in Listing 6-7 to learn how to insert data into tables when one or more of the columns have a default value. The first part of the code creates the table and adds the default constraints.

Figure 6-8 shows the results. Statement 1 inserts literal values into the HasADefault1 and HasADefault2 columns. Even though the two columns have default constraints, you can still override them and insert your own values. Notice that the row inserted with KeyColumn value 2 contains a NULL value in the HasADefault2 column. The statement specified and inserted NULL, not the default value.

Figure 6-8. The results of inserting rows into a table with column default constraints

Statements 2 and 3 take advantage of the default constraints. Statement 2 specifies the keyword DEFAULT instead of a value. Statement 3 just omits the two columns. Whenever using the keyword DEFAULT or omitting the columns, the default definition determines the values to be inserted. The GETDATE function provides default values for the HasDefault1 column. The literal value “the default” is filled in for HasADefault2.

Inserting Rows into Tables with Automatically Populating Columns

In addition to default constraints, four types of columns exist that can be autopopulated. In other words, you should not specify values for these columns. The columns types are as follows:

• rowversion: Formerly called TIMESTAMP, this contains a binary number that is unique within a database. Developers generally use ROWVERSION to determine whether changes have been made to a row.
• identity: This contains an autoincrementing numeric value. Developers often use INDENITY columns when an ID number is needed for a table.
• Computed columns: These have a definition that is usually based on the values of other columns in the same row. The values in a computed column can be stored in the table by specifying the keyword PERSISTED in the column definition. If the table definition doesn’t contain PERSISTED, it will be calculated each time it is accessed.
• Sequences: These are user-defined objects and act like identity columns but are not restricted to a specific table. Unlike identity numbers, sequence numbers can be reused and created across multiple tables and rows and you need to reference the sequence object in the insert statement.

Be sure to always specify the column names, avoiding the automatically populated columns when you write an INSERT statement to avoid causing an error.

Type in and execute the code in Listing 6-8. The first part of the code creates a table with the special column types.

Figure 6-9 shows the results. Statement 1 specified values for RegularColumn only. The database engine automatically determined the values for the other columns. Notice that the IdentityColumn contains an incrementing value. The ComputedColumn contains the result of the expression RegularColumn + CAST(IdentityColumn AS NVARCHAR).

Figure 6-9. The results of inserting rows into a table with autopopulating columns

Notice that the SequenceColumn, like the IdentityColumn, starts with one and increments by one. A difference between the two is you have the ability to reference the same demoSequence object when inserting values into another table. You can also set the command referencing the sequence object as a column default and this would allow the column to autopopulate. Another benefit to sequence numbers over identity values is you can determine the next number prior to inserting the value. SQL Server generates an identity number when a value is inserted but determines sequence numbers only when an application executes the NEXT VALUE FOR statement.

You have learned how to insert new rows into tables by using literal values or data from a query. Before moving on to the next section, where you will learn how to delete data, practice inserting new rows into a table by completing Exercise 6-1.

EXERCISE 6-1

Deleting Rows

You now know how to add new rows to tables. This section covers deleting existing rows, which is an important but dangerous task. Many developers and database administrators have accidentally removed the data from an entire table when intending to remove just one row. Care must be taken whenever deleting rows, especially when writing ad hoc queries.

Using DELETE

The DELETE statement is very simple. At a minimum, you need the word DELETE and the table name. This will remove all rows from the table. Most of the time, your goal will be to remove just a portion of the rows. Here is the syntax:

If you omit the WHERE clause, the statement removes every row from the table. The table still exists, just without any rows. When writing ad hoc DELETE statements, always test your WHERE clause with a SELECT statement first to make sure you know exactly which rows you are deleting. Type in and execute the code in Listing 6-9. The listing creates several copies of the main tables from the AdventureWorks2012 database. To avoid typing the table creation portion, you can download the code from the book’s page at www.apress.com.

You should now have several tables that you can use to practice deleting data. Type in and execute the code in Listing 6-10 to learn how to delete rows from tables.

Figure 6-10 shows the rows before and after running the DELETE statements affecting the dbo.demoCustomer and dbo.demoProduct tables. Running a SELECT statement before deleting data is a good idea and enables you to test your WHERE clause. Make sure you know which rows will be deleted before you delete them. Statement 2 removes every row from dbo.demoCustomer. Statement 5 removes the rows from dbo.demoProduct where the ProductID was greater than 900. The word FROM is optional.

Figure 6-10. The partial results before and after rows deleted

Deleting from a Table Using a Join or a Subquery

Listing 6-10 demonstrated how to remove rows from a table when the statement contains just one table. You can also remove rows from a table that is involved in a join to restrict which rows the statement deletes. You may delete rows from only one of the tables. Often developers will use a subquery instead of a join to accomplish the same thing. Here is the syntax:

The syntax shows INNER JOIN, but you could use an OUTER JOIN if that makes sense for the particular deletion. Type in and execute the code in Listing 6-11 to learn how to use both of these techniques. If you didn’t run the code in Listing 6-9 that creates the tables used in these examples and the code in Listing 6-10 that deletes some of the data, do that first.

Figure 6-11 shows the results before and after deleting. Again, write SELECT statements first to test your WHERE clause and to make sure you will delete the correct rows. Statement 2 joins the dbo.demoSalesOrderDetail table to the dbo.demoSalesOrderHeader table. The statement deletes the rows from the dbo.demoSalesOrderDetail table that have a SalesOrderNumber of SO71797 in the dbo.demoSalesOrderHeader table. The value in one table determines which rows in another table will be deleted. Notice that statements 1 and 2 are identical except for the first line in each. Statement 1 is a query to determine which rows that statement 2 will delete. The syntax and this example used an INNER JOIN, but you can also use an OUTER JOIN.

Figure 6-11. The partial results before and after deleting rows with a join and a subquery

Statement 5 uses a subquery in the WHERE clause to determine which rows to delete. Again, the code tests the WHERE clause first with a SELECT statement to make sure the correct rows will be deleted.

Another way to delete the rows using a join specifies the table name after the DELETE keyword instead of specifying the alias. Using the alias ensures that the DELETE part of the statement is tied to the SELECT part of the statement. I have seen developers write DELETE statements that inadvertently deleted all rows from a production table because the DELETE part of the statement wasn’t really connected to the rest of the statement. I recommend that you always use the technique shown in Listing 6-11 to avoid deleting all the rows in a table by mistake. Here is an example that is really a DELETE statement and a SELECT statement when the intention is just a DELETE statement:

Truncating

A way to quickly delete all the rows from a table is to use the TRUNCATE TABLE statement. This is a very fast way to empty a large table. Although deleting rows requires that the user account have DELETE permission on the table, truncating a table requires the user be in the dbo or db_ddladmin database roles or the sysadmin server role. Generally, users running an application will not be members of these powerful roles. Here is the syntax:

Listing 6-12 demonstrates how to use TRUNCATE. If you didn’t run Listing 6-9 to create the tables, do that first before typing and running the code in Listing 6-12.

Figure 6-12 shows the results before and after truncating the table. One of the reasons that TRUNCATE is so much more powerful than DELETE is that it actually drops and re-creates the table behind the scenes. That is much quicker than deleting all the rows, but no WHERE clause is allowed with TRUNCATE. The TRUNCATE statement can be used only when you intend to empty the table.

Figure 6-12. The results before and after truncating

Deleting data is a very risky operation. It’s why database administrators consider a good backup strategy an important part of their jobs. Practice what you have learned about deleting data by completing Exercise 6-2.

EXERCISE 6-2

Updating Existing Rows

Updating data is a very important part of T-SQL but requires extreme caution. Only deleting rows, which was discussed in the previous section, requires more care. Well, maybe that’s not true; you could actually drop tables or entire databases accidentally. Within applications, the user will usually be working with one row at a time. For example, they may be viewing a screen that displays and allows editing of one employee, one department, or one order. Automated processes often update data in entire tables or many rows at one time. In this section, you will learn how to use the UPDATE statement to update existing rows in several scenarios, including single tables, joins, and aggregates. Run the code in Listing 6-9 to repopulate the tables you will use for the examples in this section.

Using the UPDATE Statement

To update existing rows in a table, use the UPDATE statement. Usually you will add a WHERE clause to make sure that you update only the appropriate rows. Often database administrators have to restore backups of databases to get back data that has been accidentally updated because the WHERE clause was incorrect or missing. Here is the syntax of the UPDATE statement:

You can use expressions, literal values, or other columns to update existing data. Developers often think that updating data must be done one row at a time. I have talked to many developers who insist that they must update one row at a time because some of the rows must be updated with one value and some with another. In those cases, maybe they could write an UPDATE statement for each business rule or use the CASE function but not perform one update for each row. Type in and execute the code in Listing 6-13 to learn how to update data.

Figure 6-13 shows the results before and after the updates. Query 1 just displays a few rows from the table. Statement 2 updates all the rows in the table changing the NameStyle, a bit column, from 0 to 1. Query 3 shows the result of that change. Statement 4 changes the NameStyle value to 0 for the rows where the Title is Ms. And Query 5 displays the final changes.

Figure 6-13. The partial results before and after updating

Updating Data with Expressions and Columns

The statements in the previous section updated the dbo.demoCustomer table with literal values. You can also perform updates using expressions or other columns. Developers and database administrators often must perform large updates to data, sometimes on a periodic basis or to fulfill one-time requests. Here is the syntax for this technique:

Again, you can use expressions, hard-coded values, or other columns in your UPDATE statement. When multiple rows must be updated, such as batch processing that happens after business hours, you should perform updates in sets, not one row at a time. Type in and execute the code in Listing 6-14 to learn how to perform these updates.

Figure 6-14 shows the results before and after the update. Query 1 displays the data before the update. The NewCompany column contains the expression that will be used in the UPDATE statement. You will find it is useful to display the expression, especially if it is complicated, to make sure that your update will do exactly what you expect. Statement 2 updates the data, changing CompanyName in the dbo.demoPersonStore table to the new value derived from the FirstName and LastName columns. Finally, Query 3 displays the updated data. At this point, the CompanyName should be equivalent to the NewCompany expression.

Figure 6-14. The results before and after updating with an expression

Updating with a Join

So far, you have seen how to write UPDATE statements with a single table. When joining, you can update only a single table, but by joining with another table, you can limit the rows to be updated or use the second table to provide the value. Here is the syntax:

The syntax shows an INNER JOIN, but you could perform an OUTER JOIN if that makes sense for the particular update. Type in and execute the code in Listing 6-15 to learn how to perform an update using this technique.

Figure 6-15 shows the results before and after the update. In this case, Statement 2 uses columns from the second table, the Person.Person table, to build an expression to update AddressLine1. The statement uses another expression to move the original AddressLine1 and AddressLine2, if any, to AddressLine2. The dbo.demoAddress table doesn’t join directly to the Person.Person table but must join through an intermediary table, Person.BusinessEntityAddress.

Figure 6-15. The results before and after updating with a join

Updating with Aggregate Functions

The examples in the previous two sections demonstrated how you can update data using expressions involving literal values, columns, and functions. None of the examples included aggregate functions, however. You may not use expressions containing aggregate functions to update data directly. Type this code to see what happens when you try to use an aggregate function to perform an update:

You can isolate an aggregate query into a common table expression and then use the aggregated values to make the updates. Listing 6-16 shows how to use this technique. The first part of the code creates and partially populates a summary table that will be used in the example.

Figure 6-16 shows the results before and after the update. This code first creates a table called dbo.demoCustomerSummary. Then the code populates the new table with all the CustomerID values from the dbo.demoCustomer table and zeros in the summary columns. Statement 2 uses a common table expression containing an aggregate query summarizing the sales for each customer. The statement uses the values calculated in the common table expression to update the table. See the “Exploring Derived Tables and Common Table Expressions” section in Chapter 4 to review common table expression. See Chapter 5 to review aggregate queries.

  

Figure 6-16. The results before and after updating with a common table expression

You now know how to update data in existing tables with literal values, expressions, and other columns. Now practice what you have learned by completing Exercise 6-3.

EXERCISE 6-3

Using Transactions

A transaction is a unit of work in SQL Server. Most of the time, a transaction is one statement that inserts, updates, or deletes data. It is possible, however, to define an explicit transaction that includes more than one statement. You can also include SELECT statements in a transaction. Every statement within a transaction must succeed, or the entire transaction fails.

The classic example involves a bank ATM where a customer can transfer money from a savings account to a checking account. Imagine the problems created if an error occurred after the system subtracted the money from the savings account but before the money showed up in the checking account! By using an explicit transaction, any error between the two updates will roll back both of them. If an error occurred, the money would just go back to the savings account like nothing was ever done. This section covers writing multiple statements within explicit transactions, how to commit or roll back transactions, and what happens when transactions last longer than needed.

Writing an Explicit Transaction

The important thing to remember when working with SQL Server is to keep transactions as short as they can be and still do the work. Once a transaction starts, the database engine puts locks on tables involved within the transaction so that the tables usually may not be accessed by another query. For example, don’t write an application that begins a transaction and then waits for user input before completing the transaction. The tables could be locked while the user leaves the computer for a lunch break! Here is the syntax for a simple transaction:

Listing 6-17 demonstrates what happens when a transaction fails. Type in and execute the code.

Figure 6-17 shows the results. After running the batch, the query window will display the Messages tab first with an error message. You will have to click the Results tab to see the inserted rows. Transaction block 1 successfully inserts two rows with integer values into the table. Transaction block 2 inserts the value 3 and the value a. Because you can’t insert the string value a into a column of type INT, the statement fails. Because the two statements are within the same transaction, the entire transaction rolls back. Query 3 returns the inserted rows, which are only the rows inserted in the first transaction.

Figure 6-17. The results of using explicit transactions to insert data

Rolling Back a Transaction

You can purposely roll a transaction back before it is committed by issuing a ROLLBACK command even without an error condition. For example, what if the bank ATM added the money to the checking account before removing it from the savings account and didn’t check the savings account balance first? The transaction could roll back the transaction once the balance was checked but before the transaction was committed. Here is the syntax for rolling back a transaction:

In Chapter 7 you will learn how to trap errors and use conditional logic that will allow your code to COMMIT or ROLLBACK based on certain conditions. For now, type in and execute Listing 6-18 to learn how to use the ROLLBACK command.

Figure 6-18 shows the results. Transaction block 1 completes successfully and inserts the two rows in the table. Transaction block 2 contains two valid statements, but because it contains the ROLLBACK command instead of the COMMIT command, the transaction doesn’t complete. Query 3 shows that the batch inserts only values 1 and 2 into the table.

Figure 6-18. The results of a rolled-back transaction

Locking Tables

SQL Server has different isolation levels to control how transactions from one connection affect SELECT statements from another connection. Learning about isolation levels is beyond the scope of this book, but this section will demonstrate the default behavior. For this example, you will use two query windows, so follow the instructions carefully.

1.   From query window 1, run this code:

USE AdventureWorks2012;
GO
IF  EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[demoTransaction]')
               AND type in (N'U'))
DROP TABLE [dbo].[demoTransaction];
GO

CREATE TABLE dbo.demoTransaction (col1 INT NOT NULL);
GO

BEGIN TRAN
    INSERT INTO dbo.demoTransaction (col1) VALUES (1);
    INSERT INTO dbo.demoTransaction (col1) VALUES (2);

2.   Switch to window 2, and run this code:

USE AdventureWorks2012;
GO
SELECT col1 FROM dbo.demoTransaction;

3.   At this point, you will see nothing returned from the code from step 2 as it continues to execute. Switch to window 1, and run this code:

COMMIT TRAN

4.   Switch back to window 2 to view the results.

Once you committed the transaction in step 3, the SELECT statement in window 2 could complete. To learn more about locks, transactions, and isolation levels, read SQL Server 2008 Transact-SQL Recipes by Joseph Sack (Apress, 2008). You will learn how to add conditional logic to your transactions in Chapter 7. For now, practice what you have learned by completing Exercise 6-4.

EXERCISE 6-4

Thinking About Performance

SQL Server performs best when working on sets of data instead of one row at a time. Often developers write code that loops through a record set and performs an update or insert for each pass through the loop. The example code in Listing 6-19 demonstrates the difference in performance between the two techniques. Download and run the code from this book’s page on www.apress.com. You may need to stop the code execution after a few minutes.

After the code executes or you stop execution after a few minutes, click the Messages tab to see the results (Figure 6-19). Run this statement to see how many rows were actually inserted from the loop:

The loop inserted about 12,000 rows for me in over 7 minutes! The first INSERT statement, inserting more than 120,000 rows, took less than a second to run.

Figure 6-19. The results of comparing one insert vs. a loop with multiple inserts

Database Cleanup

Run the script in Listing 6-20 to clean up the tables used in this chapter. You can download the script from this book’s page at www.apress.com. Alternately, you can reinstall the sample databases by following the instructions in the “Installing the Sample Databases” section in Chapter 1.

Summary

Writing data modification statements is not difficult once you’ve mastered the basics of selecting data. These tasks do, however, require much more care because it’s possible to unintentionally modify and delete rows or even empty entire tables. Always check the WHERE clause with a SELECT statement first when writing ad hoc statements.

Whenever possible, do modifications on sets of data, not one row at a time. You will often see amazing differences in performance. Many developers learn to operate on one row at a time, but this is not the best way for SQL Server to work.