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)
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'
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.