title: Unexpected #temp table performance
body: <p>Bounty open:
  Ok people, the boss needs an answer and I need a pay rise. It doesn't seem to be a cold caching issue.</p>
  
  <p>UPDATE:</p>
  
  <p>I've followed the advice below to no avail. How ever the client statistics threw up an interesting set of number.</p>
  
  <p><strong>#temp</strong> vs <strong>@temp</strong></p>
  
  <p>Number of INSERT, DELETE and UPDATE statements
  0 vs 1</p>
  
  <p>Rows affected by INSERT, DELETE, or UPDATE statements
  0 vs 7647</p>
  
  <p>Number of SELECT statements
  0 vs 0</p>
  
  <p>Rows returned by SELECT statements
  0 vs 0</p>
  
  <p>Number of transactions 
  0 vs 1</p>
  
  <p>The most interesting being the number of rows affected and the number of transactions. To remind you, the queries below return identical results set, just into different styles of tables.</p>
  
  <hr>
  
  <p>The following query are basicaly doing the same thing. They both select a set of results (about 7000) and populate this into either a temp or var table. In my mind the var table <strong>@temp</strong> should be created and populated quicker than the temp table <strong>#temp</strong> however the var table in the first example takes 1min 15sec to execute where as the temp table in the second example takes 16 seconds.</p>
  
  <p>Can anyone offer an explanation?</p>
  
  <pre><code>declare @temp table ( 
  id uniqueidentifier, 
  brand nvarchar(255), 
  field nvarchar(255),
  date datetime, 
  lang nvarchar(5), 
  dtype varchar(50)
  )
  insert into @temp (id, brand, field, date, lang, dtype )
  select id, brand, field, date, lang, dtype
  from view 
  where brand = 'myBrand' 
  -- takes 1:15
  </code></pre>
  
  <p>vs</p>
  
  <pre><code>select id, brand, field, date, lang, dtype
  into #temp
  from view 
  where brand = 'myBrand'
  
  DROP TABLE #temp
  -- takes 16 seconds
  </code></pre>