Top 10 SQL Partition Commandments

I wrote this in 2010, before Clustered Column Store Indexes, SQL Pools (so rule 3 looks a bit odd), and before date data type was mainstream, but the rest of these still look quite

  1. Thou Shalt use something that exists in your query as a partition key
  2. Thou shalt not make up a surrogate partition key
  3. Thou shalt use the partition key as the first key in the clustered index
  4. Thou shalt honour the partition key in every query
  5. Thou shalt make sure you do not have any “select top 1 or max on non-partitioned columns”
  6. Thou shalt keep it simple and share partition schemes
  7. thou shalt consider filegroup design in partitioning
  8. thou shalt try to keep a standard partition design across tables within a solution
  9. thou shalt be aware that 23:997 is before midnight and 23:998 is actually midnight
  10. thou shalt leave an empty partition at the end of tables

Leave a Reply