TRUNCATE with WAITFOR DELAY

  • Greetings,

    I've recently inherited a slew of databases and many of the table population stored procedures seem to have a common methodology:

    Truncate Table TableA

    Set @count = (select count(*) from TableA)

    While @count <> 0

    Begin

    WAITFOR DELAY '000:00:30'

    Set @count = (select count(*) from TableA)

    End

    If (Select count(*) from TableA) = 0

    Begin

    Insert into TableA

    SELECT <RowList>

    FROM TableB

    WHERE <FilterConditions>

    End

    I don't see any particular reason why the original designer would have used this technique. While I've seen this out in a very few query examples in the "wild", I haven't seen any explanations as to why it was being used. Mostly it was part of queries being used to discuss other subjects.

    I know that truncating large tables can defer deallocation (pagecount > 1024), but I nothing I've researched indicates that the rowcounts or data being truncated could be present after the truncate executes, so why bother with the whole WAITFOR and verify row counts operation?

    (Note: I have issues as to the feasibility of a) the TRUNCATE/Reload mechanism and b) not using SSIS to handle the ETL processes, but that is a whole other discussion I need to have with the business).

    Does anyone have any suggestions or best practice considerations as to why this WAITFOR methodology would be necessary?

    Thanks.

    -=Janrith

  • No, I don't recall anything like that, not on newer versions. Maybe under SQL 6.5, which I never used. Perhaps under SQL 7.0, which probably didn't defer the page allocations.

    That said, the code doesn't really hurt anything. If you do want to use the code, you can streamline it a bit:

    Truncate Table TableA

    If Exists(Select Top (1) * From TableA)

    Begin

    WAITFOR DELAY '000:00:30' --that's a LONG wait

    End

    If Not Exists(Select Top (1) * From TableA)

    Begin

    Insert into TableA

    SELECT <RowList>

    FROM TableB

    WHERE <FilterConditions>

    End

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks Scott!

    -=Janrith

  • Janrith (9/9/2015)


    Greetings,

    I've recently inherited a slew of databases and many of the table population stored procedures seem to have a common methodology:

    Truncate Table TableA

    Set @count = (select count(*) from TableA)

    While @count <> 0

    Begin

    WAITFOR DELAY '000:00:30'

    Set @count = (select count(*) from TableA)

    End

    If (Select count(*) from TableA) = 0

    Begin

    Insert into TableA

    SELECT <RowList>

    FROM TableB

    WHERE <FilterConditions>

    End

    I don't see any particular reason why the original designer would have used this technique. While I've seen this out in a very few query examples in the "wild", I haven't seen any explanations as to why it was being used. Mostly it was part of queries being used to discuss other subjects.

    I know that truncating large tables can defer deallocation (pagecount > 1024), but I nothing I've researched indicates that the rowcounts or data being truncated could be present after the truncate executes, so why bother with the whole WAITFOR and verify row counts operation?

    (Note: I have issues as to the feasibility of a) the TRUNCATE/Reload mechanism and b) not using SSIS to handle the ETL processes, but that is a whole other discussion I need to have with the business).

    Does anyone have any suggestions or best practice considerations as to why this WAITFOR methodology would be necessary?

    Thanks.

    -=Janrith

    Quick thought, the WAITFOR DELAY in this case is a futile exercise as once the TRUNCATE TABLE command is issued there are no rows in the table or in fact any data pages associated with the table.

    😎

  • I agree, which is why it doesn't make any sense to me as to why they would have added the WAITFOR in the first place.

    Figuring I may have been missing something about TRUNCATE which would make the WAITFOR necessary, I went out to the interwebs to investigate.

    Thanks folks for the reassurance.

    -=Janrith

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply