Hi NG,
I have a little problem. In a DB table a have an attribute which just
contains an number which must be unique. Therefore I added a unique
index on the attribute. My application have objects which reflect the
records in this table. The application should be able to delete/Update
and Create such objects in any random order. For saving the objects I
open a transaction and iterate through my object list to either
inseert/update/delete the records in the DB. Unfortunately from time
to time I get some unique index violation error.

Of course if the save order is not correct the unique index can be
violated.

My question now is:

Is it possible to configure the DB or Index like that, that the unique
constraint is just checked with the commit statement. I mean that the
DB is inconsistent for my unique number,until the commit statement has
been called.

Thanks
Marcel

schaf (dlocke (AT) mysunrise (DOT) ch) writes:
No, SQL Server does not have commit-time constraints.

But it sounds like the problem stems from a poor implementation. If you
are on SQL 2008, the best solution is probably to send down all your
data with in table-valued parameter, and then perform all updates in a
single MERGE statement.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx