September 9, 2015 at 3:55 pm
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
September 9, 2015 at 5:14 pm
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".
September 9, 2015 at 6:05 pm
Thanks Scott!
-=Janrith
September 9, 2015 at 10:39 pm
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.
😎
September 10, 2015 at 12:42 pm
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