quote:

---

Originally posted by sent_sara

[Code]

Declare @i int
Begin Transaction
WHILE (@i <=10)
Begin
Insert into dbo.Employee1(EmpNo,EmpName,Period)
select @i,'empname' +@i,1
Set @i=@i + 1
END
Commit Transaction
[/code]

Normally sql server by default it will be implicit Transaction.I've seen in some of the stored procedure in our
company they have given explicitly..can i know what would be the reason behind this. I guess it would be realated to log file.

---

CREATE TABLE AX(id INT NOT NULL);

Declare @i INT = 1;
Begin Transaction
WHILE (@i <=10)
BEGIN
	Insert into AX (id) 
		select @i
	IF (@i >= 5) INSERT INTO AX VALUES (@i,'abcd');
	Set @i=@i + 1	
END	
Commit TRANSACTION

DROP TABLE AX;

quote:

---

Originally posted by James K

One other thing to keep in mind is that if you run into any batch terminating errors, the behavior may not be as you expect. For example, how many rows if any would be inserted into the table in the query below? And, it will leave around an open transaction.

CREATE TABLE AX(id INT NOT NULL);

Declare @i INT = 1;
Begin Transaction
WHILE (@i <=10)
BEGIN
	Insert into AX (id) 
		select @i
	IF (@i >= 5) INSERT INTO AX VALUES (@i,'abcd');
	Set @i=@i + 1	
END	
Commit TRANSACTION

DROP TABLE AX;

If you want a set of statements to be executed atomically, use the pattern on this MSDN page: http://msdn.microsoft.com/en-us/library/ms175976.aspx

---

quote:

---

Originally posted by James K

The internals of the log file are really not of much use from a logical perspective. Nonetheless: the log file records the begin transaction, each of the updates and the commit transaction. For implicit transactions, there will be a set of begin tran, update and commit for each update.

---