April 29, 2013 at 7:23 am
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
April 29, 2013 at 7:41 am
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
April 29, 2013 at 7:47 am
Ah, OK.
Thanks,
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply