Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?

share|improve this question
up vote 6 down vote accepted

Every individual transaction (statement) in SQL Server is atomic, meaning it passes or fails as a unit.

If the 999,999th record fails on a constraint violation in a 1,000,000 row insert, all of the other rows get rolled back and the table is exactly as it was before the failed statement was attempted.

The same applies for updates and deletes, as well as multiple statements in an explicit transaction. For instance, if I have an explicit transaction that does a DELETE followed by an INSERT and an UPDATE, if I encounter an issue in the UPDATE and issue a manual ROLLBACK all of those actions get undone.

share|improve this answer
Could also mention that if ignore_dup_key is on then only the dupes fail (for inserts) . – Martin Smith Dec 30 '13 at 17:20
@MartinSmith great point. If you have a unique constraint with ignore_dup_key set on then those duplicate rows will be discarded from the insert list. – JNK Dec 30 '13 at 17:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.