February 12, 2015 at 4:14 pm
Let's say that I have a stored proc that is assigned to a service broker queue and is constantly running while it waits for messages in said queue. When a message comes in on the queue, the stored proc creates a table variable based off of the contents of the message and performs various operations with the data. Since the stored proc is constantly running, do the contents of this table variable ever truly get emptied? Should I be deleting the contents of the table variable at the end of the operation to ensure that stale data doesn't persist?
February 13, 2015 at 1:42 am
I wouldnt worry about deleting the data from a temp table or table var just let SQL Server clean up when it drop out of scope.
And, No the data does not persist in that way.
February 13, 2015 at 9:02 am
Interesting. We're seeing evidence that when the table variable is used directly in the stored procedure assigned to the SB queue, the data does not get cleared out. We experienced dramatically different results when going from this:
DECLARE @myTable TABLE (id INT, val VARCHAR(MAX))
INSERT INTO @myTable
VALUES (@id, @val)
SELECT * FROM @myTable
To this:
DECLARE @myTable TABLE (id INT, val VARCHAR(MAX))
DELETE FROM @myTable
INSERT INTO @myTable
VALUES (@id, @val)
SELECT * FROM @myTable
I know this seems counter-intuitive, but I think that because from SQL's perspective, the stored proc never finishes execution (since the primary role of it is to wait for new messages), so the variable never gets destroyed.
February 13, 2015 at 9:07 am
That's really interesting. I'd think that every execution of the proc from the queue would be a separate instance of the procedure, but perhaps not. Maybe there's some bleed.
If you select the contents before the insert and drop them in a audit table with a datetime, is there anything there?
DECLARE @myTable TABLE (id INT, val VARCHAR(MAX))
insert MyAudit (mydate), id, val) select getdate(), id, val from @Mytable
INSERT INTO @myTable
VALUES (@id, @val)
SELECT * FROM @myTable
February 13, 2015 at 9:35 am
Interesting. I can't reproduce it in this way. Apparently, I'm going insane. 🙁
February 13, 2015 at 3:08 pm
OK, this effect is *probably* what you are seeing..
declare @x integer=0
while(@x<3)begin
declare @tab table
(
col1 integer
)
insert into @tab values(@x)
Select * from @tab
select @x=@x+1
end
The table is not 'destroyed' when it drops out of scope inside a loop.
So in this case you will have to delete the data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply