Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2007-07-25 : 14:29:08
|
| I created a table called "version" with one varchar(50) column. The first insert below causes an error because it's greater than 50 characters (intentional) Why does the following insert "test3" into the table, shouldn't the whole thing be rolled back because it's using implicit_transactions?set implicit_transactions oninsert into versionselect '12345678901234657890123456798012345678901234567890123'insert into versionselect 'test3'commitset implicit_transactions off |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-25 : 14:35:55
|
| No, implicit transactions commit each statement after it completes. It does not wait to do it after the batch. You need to use explicit transactions.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-07-25 : 14:44:41
|
| Then I don't see the point of implicit transactions. ??? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-25 : 14:47:06
|
| You shouldn't use them if you have multiple DML operations that need to committed as a batch or rolled back as a batch. If each DML operation is independent of each other, then implicit transactions can be used.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-07-25 : 16:19:09
|
| What's the point of a having a "transaction" if each operation is independent? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-25 : 16:27:47
|
| You don't need explicit transactions if you don't care about data integrity of the individual statements in the batch.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
implicit_transactions confusion