Re: Temp tables versus table variables
Posted: Mon Jan 13, 2020 3:52 pm
I like to think of it as:
Table variable/Temp table/CTE (or sub-query versions of them) are really all just hints to SQL's query optimizer.
And we need to figure out the best hint for the most common use-case for the query in question. And that hint can change over time, too. Your database may work best with one option today, but in a year, when your database has gone from 2GB to 10GB, a different option may be needed to get better results.
Table variable/Temp table/CTE (or sub-query versions of them) are really all just hints to SQL's query optimizer.
And we need to figure out the best hint for the most common use-case for the query in question. And that hint can change over time, too. Your database may work best with one option today, but in a year, when your database has gone from 2GB to 10GB, a different option may be needed to get better results.