July 7, 2015 at 7:44 pm
Is there a way to remove a table-type variable when you're done with it?
I have a large procedure that declares a table-type variable, loads data into it with a SELECT statement, uses it as a filter in the next SELECT statement, and is no longer needed for all of the subsequent statements. It just hangs around tying up resources. I'd like to get rid of it without waiting for the next "GO" to come around and complete the batch. Is there any way to manually deallocate or drop it?
Thanks!
July 8, 2015 at 2:22 am
No, it'll be automatically dropped as soon as it goes out of scope.
Why are you using a table variable (with it's attendant row estimation problems) over a temp table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2015 at 2:08 am
GilaMonster (7/8/2015)
No, it'll be automatically dropped as soon as it goes out of scope.
So there's no other way to drop it before that?
Why are you using a table variable (with it's attendant row estimation problems) over a temp table?
What's a attendant row estimation problem? Never heard of it...
July 12, 2015 at 9:52 am
mtlsql (7/12/2015)
GilaMonster (7/8/2015)
No, it'll be automatically dropped as soon as it goes out of scope.So there's no other way to drop it before that?
Why are you using a table variable (with it's attendant row estimation problems) over a temp table?
What's a attendant row estimation problem? Never heard of it...
The row estimation problem for table variables is that the optimizer will usually create a query execution plan based on the table variable containing at most 1 row; if the table variable actually contains a fairly large number of rows this can result in a very inefficient plan, which is going to cause performance problems (and risks causing serious performance problems).
There are a couple of alternative approaches which don't have this row count estimation problem: (a) use a temp table instead; that also solves your dropping it issue because you can explicitly drop it (and, contrary to common mythology, a temp table is no more likely than a table variable to hold its rows on disc); and (b) use a CTE (if the table variable was populated by a single insert statement it's always possible to use a CTE instead if the table variable was used only in a single query, as you stated it is here, and even when it isn't using a lot of UNION opertors can get around multiple insert cases provided the base tables aren't changed in between them). (b) risks making the query sufficiently complex that the optimser doesn't do enough analysis to get a good plan in cases where populating the table variable was a very complex operation.
Tom
July 13, 2015 at 7:42 am
mtlsql (7/7/2015)
Is there a way to remove a table-type variable when you're done with it?I have a large procedure that declares a table-type variable, loads data into it with a SELECT statement, uses it as a filter in the next SELECT statement, and is no longer needed for all of the subsequent statements. It just hangs around tying up resources. I'd like to get rid of it without waiting for the next "GO" to come around and complete the batch. Is there any way to manually deallocate or drop it?
Thanks!
From your explanation I wonder if you even need any kind of temporary storage at all. You say you run a select statement to populate this table variable and then use it as a filter in your next query. Sounds like you could probably just skip the temporary storage concept entirely and inline the whole thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 13, 2015 at 7:49 am
mtlsql (7/12/2015)
Why are you using a table variable (with it's attendant row estimation problems) over a temp table?
What's a attendant row estimation problem? Never heard of it...
Attendant (adjective)
- being present or in attendance; accompanying.
- consequent; concomitant; associated; related;
Hence not the name of a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply