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.

Example

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 INSERT and UPDATE statements, like this:

BEGIN TRANSACTION
INSERT INTO Customers (ID, Name, Email, Phone)
VALUES (1001, 'John Smith', 'johnsmith@email.com', '555-1234')
UPDATE Customers
SET Phone = '555-5678'
WHERE ID = 1000
COMMIT TRANSACTION

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.