0
  • I read that Derived tables are much better in terms of performance than temporary tables.

  • In my code, I have a procedure used to search on basis of the parameters passes to it and matches them against the data in database Now, the results of matched rows returned by their respective functions are stored in a Temporary table like this :

     select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionId = 2;
 INSERT INTO #Temp2(RowNumber, ValFromFunc, FuncWeight, percentage)
 SELECT RowNumber, PercentMatch, @constVal, PercentMatch * @constVal from dbo.MatchMiddleName(@MiddleName);

  • dbo.MatchMiddleName is a multiline TVF here and returns a table wiht two columns.

  • I use temporary table like this:

     SELECT RowNumber, (SUM(Percentage)/@cnt) as Percentage FROM #Temp2 GROUP BY RowNumber  order by Percentage desc End

  • Shall I consider using derived tables instead for better performance?

Virbhadrasinh
  • 529
  • 6
  • 19
Simran
  • 539
  • 2
  • 8
  • 28
  • possible duplicate of [When should I use a table variable vs temporary table in sql server?](http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server) – Giorgi Nakeuri Jan 28 '15 at 13:15
  • @GiorgiNakeuri: The OP isn't asking about table variables, he's asking about derived tables. – Jon Egerton Jan 28 '15 at 13:23
  • In this instance I don't really see much need for either TBH - a straightforward query would do fine? – Jon Egerton Jan 28 '15 at 13:24

0 Answers0