At what point should I abandon the Variable table for the Temp table?

Question

Is there a number of rows that makes the table variable inefficient or what? I understand the differences between them, and I saw several different numbers when this moment was reached, but I wonder if anyone knows.

Answer 1

If you need other indexes in the table, different from those that can be created in the temp table variable, or for large data sets (which are hardly stored in available memory), when the width of the table (the number of bytes per row) exceeds a certain threshold (this due to the fact that the number or rows of data on the input / output page are reduced, and productivity is reduced ... or if the changes you plan to make in the data set should be part of a transaction with several operations that can be rolled back (from changes in table variables are not written to the transaction log, changes in temp tables ...)

this code demonstrates that table variables are not stored in the 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 

Answer 2

tempdb, .

.

, , , .

, :

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

.

42 LOP_INSERT_ROWS.

( ) 42 LOP_DELETE_ROWS.

, .