Sql Server 2016 Temp Table or Table Variable inside WHILE Loop

Question

I have a while loop, that will run 300M times.

Each time this loop runs, it creates a 1 row table with two columns, which in the end of the loop this table is deleted and once the loop re-starts, this table is rebuilt again with 1 row with two columns but different data.

Currently I am using a table variable, but I would like to know your opinion on the type of table I should use to boost performance, given all this creation & erasing.

Answer 1

In your case, the key would be to use a same table variable, since it is processed in memory. A temporary table would be much slower.

Thks.