Atomicity is one of the four key properties of the
ACID (Atomicity, Consistency, Isolation, Durability) database management system model.
SQL Server, like other relational database management systems, uses the concept of atomicity to ensure the consistency and integrity of data.
Atomicity means that a transaction is treated as a single, indivisible unit of work that either completes successfully or is rolled back if any part of the transaction fails.
Here's an example of how atomicity works in SQL Server:
Suppose we have a database with a table called "Customers" that has columns for customer ID, name, email, and phone number. We want to add a new customer to the table, but we also want to update the phone number of an existing customer in the same transaction.
To do this, we can use a SQL Server transaction that includes both the
UPDATE statements, like this:
INSERT INTO Customers (ID, Name, Email, Phone)
VALUES (1001, 'John Smith', 'email@example.com', '555-1234')
SET Phone = '555-5678'
WHERE ID = 1000
In this example, the
BEGIN TRANSACTION statement starts the transaction, and the
COMMIT TRANSACTION statement completes the transaction if everything succeeds. If any part of the transaction fails, the
ROLLBACK TRANSACTION statement would undo all changes made by the transaction.
Thanks to the atomicity property, if the INSERT statement succeeds but the UPDATE statement fails (for example, if there is no customer with ID 1000), then the entire transaction is rolled back and neither change is made to the database. On the other hand, if both statements succeed, then both changes are committed to the database as a single, atomic unit of work.