Microsoft® SQL Server® 2012 Bible

291

CHAPTER

12

Modifying Data In SQL Server

IN THIS CHAPTER

Inserting Data From Expressions, Other Result Sets, and Stored Procedures

Updating and Deleting Data

Mastering the Merge Command

Exposing Inserted and Deleted Tables to the Dml

T

hings change. Life moves on. Because the purpose of a database is to accurately represent

reality, the data must change along with reality. For SQL programmers, that means inserting,

updating, and deleting rows — using the basic data manipulation language (DML) commands.

However, these operations aren’t limited to writing single rows of data. Working with SQL means

thinking in terms of data sets. The process to modify data with SQL draws on the entire range of

SQL Server data-retrieval capabilities — the powerful SELECT, joins, full-text searches, subqueries,

and views.

This chapter is all about modifying data within SQL Server using the INSERT, UPDATE, DELETE,

and MERGE SQL commands. Modifying data raises issues that you need to address, or at least con-

sider. Inserting surrogate primary keys requires special methods. Table constraints may interfere

with the data modifi cation. Referential integrity demands that some DELETE operations cascade

to other related tables. This chapter can help you understand these concerns and offer some ways

to deal with them. Because these potential obstacles affect INSERT, UPDATE, MERGE, and, to some

degree, DELETE, they are addressed in their own sections after the sections devoted to the indi-

vidual commands.

The ACID database properties (atomic, consistent, isolated, and durable) are critical to the modifi cation of data.

For many databases, SQL Server’s default transactional control is suffi cient. However, misapplied transaction

locking and blocking represents one of the top four causes of poor performance. Chapter 47, “Managing

Transactions, Locking, and Blocking,” digs into SQL Server’s architecture and explains how data modifi cations

occur within transactions to meet the ACID requirements, and how SQL Server manages data locks.

c12.indd 291c12.indd 291 7/30/2012 4:42:34 PM7/30/2012 4:42:34 PM

http://www.it-ebooks.info