Why use table valued function instead of a temp table in SQL?

Question

I am trying to speed up my monster of a stored procedure that works on millions of records across many tables.

I've stumbled on this: Is it possible to use a Stored Procedure as a subquery in SQL Server 2008?

My question is why using a table valued function be better then using a temp table.

Suppose my stored procedure @SP1

declare @temp table(a int)


insert into @temp 
select a from BigTable 
where someRecords like 'blue%'

update AnotherBigTable
set someRecords = 'were blue'
from AnotherBigTable t 
inner join
@temp
on t.RecordID = @temp.a

After reading the above link it seems that the consunsus is instead of using my @temp as temp table, rather create a table valued function that will do that select. (and inline it if its a simple select like I have in this example) But my actual selects are multiple and often not simple (ie with subqueires, etc) What is the benefit?

Thanks

Answer 1

Generally, you would use a temporary table (#) instead of a table variable. Table variables are really only useful for

• functions, which cannot create temporary objects
• passing table-valued data (sets) as read-only parameters
• gaming statistics for certain query edge-cases
• execution plan stability (related to statistics and also the fact that INSERT INTO table variables cannot use a parallel plan)
• prior to SQL Server 2012, #temp tables inherit collation from the tempdb whereas @table variables uses the current database collation

Other than those, a #temporary table will work as well as if not better than a variable.

Further reading: What's the difference between a temp table and table variable in SQL Server?

Answer 2

Probably no longer relevant... but two things I might suggest that take two different approaches.

Simple approach 1:

---

Try a primary key on your table valued variable:

declare @temp table(a int, primary key(a))

---

Simple approach 2:

In this particular case try a common table expression (CTE)...

;with

   temp as ( 
      SELECT      a as Id
      FROM        BigTable
      WHERE       someRecords like '%blue'
   ),

 UPDATE    AnotherBigTable
 SET       someRecords = 'were Blue'
 FROM      AnotherBigTable
 JOIN      temp
   ON      temp.Id = AnotherBigTable.RecordId

---

CTE's are really great and help to isolate specific data sets you actually want to work on from the myriad of records contained in larger tables... and if you find your self utilizing the same CTE declaration repeatedly consider formalizing that expression into a view. Views are an often overlooked and very valuable tool for DBA and DB programmers to manage large complex data sets with lots of records and relationships.