title: Performance difference between Primary Key and Unique Clustered Index in SQL Server
body: <p>We have 2 tables that have about 40M rows. the size of database is about 20GB, most are for these 2 tables. Everyday, We need delete some data, i.e. about 10M rows. So, we are using batch delete to keep the log file within certain size.</p>
  
  <p>originally, there is no primary key for the table. But has unique, clustered index for each table. the delete takes for ever. i.e. it takes about 2-3 hours to delete 500K rows on a virtual machine. * before delete, the index was rebuilt.</p>
  
  <p>now, we converted the unique, clustered index to primary key. it takes about 20-30 minutes to delete 2M rows. </p>
  
  <p>I understand there is difference between primary key and unique clustered index, but why the performance is so different?</p>
  
  <p>anyone has some insight?</p>
  
  <p>thanks</p>