I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.
This is the code to insert into the table variable
DECLARE @Data TABLE(...) INSERT INTO @DATA( ... ) SELECT .. FROM ...
This is the code to insert into the temp table
CREATE #Data TABLE(...) INSERT INTO #DATA( ... ) SELECT .. FROM ... DROP TABLE #Data
The temporary table doesn't have any keys or indexes, the select part is the same between the 2 queries, and the number of results returned by the select is ~10000 rows. The time needed to execute the select alone is ~10 seconds.
The temp table version takes up to 10 seconds to execute, I had to stop the table variable version after 5 minutes.
I have to use a table variable because the query is part of a table value function, that doesn't allow access to temporary table.
Execution plan for the table variable version
Execution plan for the temp table version