The answer is: 'It depends'.
If your table variable gets too big to hold in memory, SQL will automatically create a temporary table as backing storage. If your SQL has no WHERE clause and therefore always does a table scan, there probably will be no impact in having a large number of rows in your table variable.
If your SQL has a WHERE clause, it could possibly get better performance by exploiting indexes. You can create indexes on a temporary table but not on a TV (apart from the PK). However, SQL will not use an index if the number of pages in the table is so low that a table scan will not harm performance. Therefore the breakpoint comes when the table grows above about 500KB, when SQL would normally determine that index access is faster than a table scan. To translate this to an approximate row count, divide your row length into 500KB. If you know you will always have less rows than this number then a TV will not harm performance, otherwise consider using a temporary table.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara