Does a "Begin... End" in a While loop act as a transaction?

  • 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

  • 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

  • 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