StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInsert failed, but identity value grows, does this break the Atomicity rule?
text
Body
copied!<p>When I importing data to a new table from a large excel, if one record failed, then nothing is imported. I think it's ok because it meet the Atomicity rule. However, when I fixed the source data error and import again, the identity column does not start from 1, but start from a big value.</p> <p>For example</p> <pre><code>create table #test (id int identity(1,1), name varchar(4) default '') insert into #test (name) values('1 insert will failed'); select ident_current('#test') as ident_current insert into #test (name) values('2 insert will failed'); select ident_current('#test') as ident_current insert into #test (name) values('3 OK'); select ident_current('#test') as ident_current select * from #test drop table #test </code></pre> <p>Result</p> <pre><code>id name ----------- ---- 3 3 OK </code></pre> <p>Wikipedia descripbe <a href="http://en.wikipedia.org/wiki/ACID" rel="nofollow">ACID</a> as the following</p> <blockquote> <h2>Atomicity</h2> <p>Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and <strong><em>the database state is left unchanged</em></strong>. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.</p> </blockquote> <p>So, it looks like SQL Server doesn't let the database state (the identity value) unchanged if insert failed, so, does this break the ACID rule?</p> <p>BTW, PostgreSQL doesn't let identity(serial) value grows when insert failed. (Update: Only sometimes, see comments. <b>DO NOT</b> rely on this.).</p> <pre><code>test=# create table AutoIncrementTest (id serial not null, name varchar(4)); NOTICE: CREATE TABLE will create implicit sequence "autoincrementtest_id_seq" for serial column "autoincrementtest.id" CREATE TABLE test=# insert into autoincrementtest(name) values('12345'); ERROR: value too long for type character varying(4) test=# insert into autoincrementtest(name) values('12345'); ERROR: value too long for type character varying(4) test=# insert into autoincrementtest(name) values('1234'); INSERT 0 1 test=# select * from autoincrementtest; id | name ----+------ 1 | 1234 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload