Lesson 1: Modifying Data by Using INSERT, UPDATE, and DELETE Statements 47
Inserting Data 48
Updating Data 50
Deleting Data 52
Lesson Summary 57
Lesson 2: Enhancing DML Functionality with the OUTPUT Clause and MERGE Statement 58
Using the OUTPUT Clause 58
Using the MERGE Statement 60
Lesson Summary 66
Lesson 3: Managing Transactions . . . 67
Understanding Transactions 67
Defining Explicit Transactions 68
Understanding Special ROLLBACK Scenarios 68
Gathering Information About Transactions 69
Understanding Locking 70
Setting Transaction Isolation Levels 75
Lesson Summary 77 Chapter Review . . . 78 Chapter Summary 78 Key Terms . . . 78 Case Scenarios 78 Suggested Practices . . . 79 Modify data by using INSERT, UPDATE, and DELETE statements 79
Return data by using the OUTPUT clause 79
Modify data by using MERGE statements 80
Manage transactions 80
n Modify data by using MERGE statements. n Manage transactions.
Lessons in this chapter:
n Lesson 1: Modifying Data by Using INSERT, UPDATE, and DELETE Statements 47 n Lesson 2: Enhancing DML Functionality with the OUTPUT Clause
and MERGE Statement 58
n Lesson 3: Managing Transactions 67
before You begin
To complete the lessons in this chapter, you must have:
n A basic understanding of relational database design concepts and terminology n A basic understanding of SQL Server data types
n A general understanding of SELECT statement syntax
n Knowledge about how SQL Server Management Studio (SSMS) functions. as well as the ability to open new query windows and existing query files from within SSMS
n SQL Server 2008 Developer Edition, Enterprise Edition, or Enterprise Evaluation Edition, with the AdventureWorks2008 and Northwind sample databases installed
real World
Ann Weber
T
ransactions are key to providing a consistent and reliable view of the data to all users at all times. As a consultant and as a trainer, I have come across numerous difficulties caused by poorly written transactions. One of the most frustrating errors I came across was when a company had long transactions that would cause deadlock situations to arise from time to time in the application. Shortening and reordering the order that tables were accessed within the transactions would have minimized the number of deadlock errors that occurred. But even more frustrating was the fact that the code did not handle the errors. The users would lose all the data they had been entering (which may have taken them up to 15 minutes to complete) and they would receive the following error message: “Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” Even if you do not expect deadlock situations to occur, you need to program for the possibility. Users should never see this kind of message. The client needed to capture the user’s input before opening the transaction, intercept the deadlock error, and attempt to resend the user’s input before finally sending a user-friendly message stating that the insert failed and to please try again later.Lesson 1: Modifying Data by Using INSERT, UPDATE,
and DELETE Statements
The INSERT, UPDATE, and DELETE statements allow you to add, remove, and modify data in your databases. These statements can be used in a variety of ways to allow you to specify fixed values. The table schema (also known as the table definition) affects the values that may or may not be defined, modified, and removed in INSERT, UPDATE, and DELETE statements. For example, the IDENTITY property is used to create an identity column that automatically assigns incremental values in the identity column each time a new row is inserted. Because of this, under normal circumstances, you cannot enter a value for an identity column when executing an INSERT or UPDATE statement. You can overcome this limitation by issuing the SET IDENTITY_INSERT ON command in the current connection. NULL definitions and constraints also have an effect on DML statements.
More info cOnStRaintS anD IDENTITY cOLUMnS
For more information about table schemas, constraints, and the IDENTITY property, see Chapter 3, “Tables, Data Types, and Declarative Data Integrity.”
After this lesson, you will be able to:
n Use the INSERT statement to add new rows to your tables. n Use the UPDATE statement to modify rows in your tables. n Use the DELETE statement to remove rows from your tables.
Estimated lesson time: 45 minutes
real World
Ann Weber
D
ML statements are a core functionality in SQL Server. As a trainer and consultant, I have come across many cases of data loss caused by improperly constructed UPDATE or DELETE statements. To minimize the possibility of data loss when issuing DML commands, you can follow several precautions. First, develop and test all code on a nonproduction server. Second, build the logic for the UPDATE, INSERT, or DELETE command as a SELECT statement to verify the correct result set is being returned. In addition, either execute the command as part of an explicit transaction and do not commit the transaction until you verify success, or turn on implicit transactions while you are building ad hoc queries to change data. Remember that with implicit transactions, a DML statement automatically starts a transaction, but you must execute a COMMIT or ROLLBACK statement manually.The fact that implicit transactions are disabled by default on a server running SQL Server has caught many Oracle developers off guard. Unless you change the SQL Server default settings or explicitly start a transaction, there is no way to roll back a command once it has been executed. Implicit transactions actions are always enabled on Oracle servers. If you would like to have the same functionality in SQL Server, enable the SET_IMPLICIT_TRANSACTIONS connection property for each connection created.