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:
1 2 3 4 5 6 7 |
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.