Temp tables are your friend, but there are other options. One of those is using a local TABLE variable table in your query. Performance wise, for simple queries, TABLE variable tables will be faster as they avoid the additional transaction logging created when using temp tables. This also means you can’t use transactions with a TABLE variable table. The TABLE variable table is also only available in scope to the query you are executing. Meaning it’s not persistent across queries. Lastly, TABLE variable tables are implicitly dropped at the end of query execution so you can skip the cleanup step of your query to remove any temp tables. See the link below with some more performance/functionality trade-offs to consider when evaluating which approach to use.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @tempData TABLE(id int) INSERT INTO @tempData(id) VALUES(1),(2),(55),(123),(9) SELECT * FROM someTable WHERE someTableFieldID IN (SELECT id FROM @tempData) SELECT * FROM someOtherTable WHERE someOtherTableFieldID IN (SELECT id FROM @tempData) |
Views – 1961