title: Why is there a big difference in latch waits for an update against a table variable vs temp table?
body: <p>Help me to understand latches difference between table variable and temp table.
  Example:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET NOCOUNT ON
  
  DECLARE @t TABLE (id int NOT NULL IDENTITY(1,1) PRIMARY KEY, ss varchar(50))
  INSERT INTO @t (ss)
      VALUES ('TestTest'), ('TestTest')
  
  declare @n int = 0
  
  WHILE  @n &lt; 100000
  BEGIN
      SET @n += 1
  
      UPDATE @t
      SET ss = REVERSE(ss)
  END
  </code></pre>
  
  <p>Run it!
  And check wait statistic:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = &lt;spid&gt;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>session_id wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
  
  951        PAGELATCH_SH                                                 1                    0                    0                    0
  951        PAGELATCH_EX                                                 200002               12655                379                  12414
  951        SOS_SCHEDULER_YIELD                                          22                   636                  205                  636
  951        MEMORY_ALLOCATION_EXT                                        45                   0                    0                    0
  </code></pre>
  
  <p>It seems that latches were taken 2 times for insert and 200000 times for update.
  But when we switch table variable (@t) to temp table (#t) we have no latches at all:</p>
  
  <pre><code>session_id wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
  
  1108       SOS_SCHEDULER_YIELD                                          910                  83                   4                    82
  1108       MEMORY_ALLOCATION_EXT                                        110                  0                    0                    0
  </code></pre>
  
  <p>I have 2 questions:</p>
  
  <ol>
  <li>Why we have latches in table variable and no one for temp table?</li>
  <li>Why latches taken for EVERY row? Why not only once per update (transaction)?</li>
  </ol>