Summary

Whether you explicitly declare a transaction or not, all data inserts, updates, and deletes are managed as transactional operations. SQL Server uses the transaction log to queue requested operations and to test validation rules prior to completing the physical operations on rows in the tables. Transactions provide the mechanism to guarantee that all operations either complete successfully or fail all together. Multiple operations can be explicitly included in a single transaction to ensure that they are managed as an autonomous unit.

Chapter 13 discusses how inserts, updates, and deletes can be managed in secured stored procedures. The INSERT statement supports two different syntax forms, either using the VALUES keyword or SELECT. Using SELECT is more flexible and allows values to be retrieved from other tables and sources.

When updating data, the database engine will lock data at various levels, depending on the scope of the operation and user concurrency. Locking may occur at the row, page, table, or database level. Updates and deletes should always be tested using a SELECT statement before they are performed. These operations may be irreversible without a backup of the database.

The DELETE command allows affected rows to be filtered, based on practically any criteria. For large-scale delete operations, this comes at the cost of transaction management. The TRUNCATE command offers a far more efficient method to effectively remove all rows in a table without the overhead of having to log and manage the deletions as transactions, but can only be executed by a user with elevated privileges.