Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does a "Begin... End" in a While loop act as a transaction? Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 7:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:00 AM
Points: 979, Visits: 5,090
Reading Lynns comments in this topic: http://www.sqlservercentral.com/Forums/Topic1447456-391-1.aspx, I found myself wondering if a script I've got to re-org or re-build indexes was going to use transactions, or be one big transaction.

The code in question reads the tables that need to be re-orged or re-built from a temp table, and loops through until every row in the temp table has been read.

So it's similar to:
Set @loopcnt = 0
While @loopcnt != (select COUNT(DISTINCT TableName) from #IndicesToReorganize)
Begin
Set @loopcnt = @loopcnt + 1
set @tblName = (Select tabletowork from (Select TableName as tabletowork, ROW_NUMBER() OVER(ORDER BY TableName) as rn
from #IndicesToReorganize) tmp
Where rn = @loopcnt)
Set @Alter = N'alter index ALL on ' + @dbname + '.dbo.' + @tblName + ' REORGANIZE'
exec(@Alter)
End

So, do those Begin / Ends inside the While run each reorg as a transaction, or do I need to wrap the "exec(@Alter)" in a "Begin Transaction / End Transaction"?
Taking a look in BOL, it looks like, NO they do not function as a begin / end transaction, and I will need to put such in the loop.
Correct?

Thank you,
Jason
Post #1447504
Posted Monday, April 29, 2013 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 5,308, Visits: 9,700
Jason

You're only running one ALTER INDEX statement at a time, so it doesn't make any difference whether you use an explicit transaction or not. I think what Lynn was asking in the other thread was whether the whole WHILE loop was enclosed in an explicit transaction.

John
Post #1447522
Posted Monday, April 29, 2013 7:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:00 AM
Points: 979, Visits: 5,090
Ah, OK.

Thanks,
Jason
Post #1447529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse