title: Updating a partitioned table with aligned index locks all partitions
body: <p><strong>part function and schema:</strong></p>
  
  <pre><code>CREATE PARTITION FUNCTION SegmentPartitioningFunction (smallint)
      AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ;
  GO
  
  CREATE PARTITION SCHEME SegmentPartitioningSchema
      AS PARTITION SegmentPartitioningFunction ALL TO ([PRIMARY])
  GO
  </code></pre>
  
  <p><strong>I have a table (Note the table is M:N) :</strong></p>
  
  <pre><code>  PartTable 
    (
     prd_id int, 
     cat_id smallint, 
     datacolumn smallint,
     primary key (prd_id, cat_id) on SegmentPartitioningSchema(cat_id)
    ) on SegmentPartitioningSchema(cat_id)
  </code></pre>
  
  <p><strong>NOTE:</strong> I tried all 3 combinations (<code>part. table</code>, <code>part. index</code>, <code>part table and index</code>)</p>
  
  <p>In addition I have an index on <code>PRD_ID</code> and <code>CAT_ID</code> by them selves as they are Foreing keys.</p>
  
  <p>I set <code>LOCK_ESCALATION</code> to <code>AUTO</code> as confirmed using this select:</p>
  
  <pre><code>SELECT lock_escalation_desc FROM sys.tables
  </code></pre>
  
  <p>Now, what I am trying to do is and by my understending should work:</p>
  
  <pre><code>begin tran
  update PartTable set DataColumn = DataColumn where cat_id = 1
  -- commit tran -- this doesnt happen yet
  </code></pre>
  
  <p>and at the same time in different connection</p>
  
  <pre><code>begin tran
  update PartTable set DataColumn = DataColumn where cat_id = 2
  -- commit tran -- this doesnt happen yet
  </code></pre>
  
  <p><strong>The execution plan</strong></p>
  
  <p>Pastebin: <a href="http://pastebin.com/MgK6whYG" rel="nofollow">http://pastebin.com/MgK6whYG</a></p>
  
  <p>Bonus Q: Why is the TOP operator used?</p>
  
  <p>By my understanding, I should get <code>IX</code> locks on the partition and only one one partition at the same time, which means both transactions can complete at the same time and the second one where <code>cat_id = 2</code> shouldn't have to wait for the first one as both table and index are partitioned and the index is aligned.</p>
  
  <p>However this doesn't happen and when I check the execution plan, I see that the table scan happens on every partition even through that doesnt make sense to me.</p>
  
  <p>I tried <code>technet</code>, <code>documentation</code> and <code>stackoverflow</code> but all I didn't find an answer.
  Now I know that the <code>PK</code> should be <code>CAT_ID</code>, <code>PRD_ID</code> (this makes more sense the way the table is used)</p>
  
  <p>I am currently working on <code>SQL Server 2012</code>, but I suppose it should work better then on <code>2008R2</code>.</p>