At what point should a Table Variable be abandoned for a Temp Table?

Question

Is there a row count that makes table variable's inefficient or what? I understand the differences between the two and I've seen some different figures on when that point is reached, but I'm curious if anyone knows.

Answer 1

Internally, table variables can be instantiated in tempdb as well as the temporary tables.

They differ in scope and persistence only.

Contrary to the popular belief, operations to the temp tables do affect the transaction log, despite the fact they are not subject to the transaction control.

To check it, run this simple query:

DECLARE @mytable TABLE (id INT NOT NULL PRIMARY KEY)
;
WITH    q(num) AS
        (
        SELECT  1
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num <= 42
        )
INSERT
INTO    @mytable (id)
SELECT  num
FROM    q
OPTION (MAXRECURSION 0)

DBCC LOG(tempdb, -1)
GO
DBCC LOG(tempdb, -1)
GO

and browse the last entries from both recordsets.

In the first recordset, you will see 42 LOP_INSERT_ROWS entries.

In the second recordset (which is in another batch) you will see 42 LOP_DELETE_ROWS entries.

They are the result of the table variable getting out of scope and its record being deleted.

Answer 2

When you need other indices on the table other than those that can be created on a temp table variable, or, for larger datasets (which are not likely to be persisted in available memory), when the table width (number of bytes per row) exceeds some threshold (This is because the number or rows of data per I/O page shrinks and the performance decreases... or if the changes you plan on making to the dataset need to be part of a multi-statement transaction which may need to be rolled back. (changes to Table variables are not written to the transaction log, changes to temp tables are...)

this code demonstrates that table variables are not stored in Transaction log:

create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #' 
insert into @T select 'old value @' 
begin transaction 
     update #T set s='new value #' 
     update @T set s='new value @' 
rollback transaction 
select * from #T 
select * from @T