SQL Interview: 6: Multi-row INSERT operations

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.
Section: Development Level: Intro
Question:
Look at the following multi-row INSERT statement:
INSERT Sales.CustomerGroups
(
CustomerGroupID, CustomerGroupName
)
VALUES (1, 'Group A'),
(2, NULL),
(3, 'Group C');
The column CustomerGroupName is defined as NOT NULL so the second row cannot be inserted.
How many rows are inserted by this statement, assuming there are no other errors?
Answer:
INSERT statements are atomic, even for multi-row INSERT statements. Either all the rows are inserted, or none are.
If one row fails (as in this case), no rows are inserted.
2021-04-06