Use case for SQL server table partitioning

Michelle Gutzait
by Michelle Gutzait
2 min read
Jan 11, 2016 12:00:00 AM
  Often we are asked by our clients about Table Partitioning, and specifically, which tables will be good candidates to be partitioned? Here are some of the main use cases for Table Partitioning:
  1. You have the Enterprise Edition of SQL Server 2005 or higher.
  2. The table contains or will contain more than 5-6 million rows and growing fast, or its size is growing by around 1GB per month or more.
  3. The FULL backup is taking too long or the backup file is too large and older data is not being updated (i.e.: users can only update data from the last 3 months).
  4. Data needs to be archived or purged on a regular basis, potentially the current archiving or deletion of data is causing blocks or deadlocks to other processes.
  5. There is a NOT NULL date column or another NOT NULL sequential column that the table can be partitioned upon.
  6. Better if most queries are including the partitioned column in WHERE clauses (i.e: between the date range).
When partitioning a table, here are few things to look into:
  1. Create a file and a filegroup per partition (even if the files are created in the same place). This way, it is easy to backup (i.e.: FILEGROUP backup), maintain and archive/purge.
  2. The best way to partition a table is by a date column because data is usually archived or purged by a date. If you do not have such a column, you may want to consider adding a column that will contain the current date/time when the row is created. This column can contain the default GETDATE(). Using an ID or a calculated column may cause too many headaches. If the application returns an error when adding the date column, consider using a view on top of the underlying partitioned table.
  3. Partitioning requires maintenance:
    1. To add files, filegroups and partitions on a regular basis and in advance.
    2. Monitor data growth and potentially modify the partition architecture (i.e.: move from a monthly partition to a weekly or daily partition).
    3. Archiving/purging partitions on a regular basis. Consider using a SWITCH partition for quick archiving and purging.
    4. You can defragment indexes per partition.
    5. Remember that statistics cannot be updated by partition, however, you may want to consider FILTERED INDEXES or FILTERED STATISTICS to avoid updating statistics on the entire table, as well as improving performance in specific cases.
  4. Consider using MAXDOP <> 1 on the instance level or for specific queries that span multiple partitions in order to take advantage of parallelism. Configure parallelism with caution.
Additional links: There is a lot of information around SQL Server Table Partitioning. Here are some of the useful links: SQL Server Database Partitioning Myths and Truths Table Partitioning in SQL Server – The Basics How To Decide if You Should Use Table Partitioning Query Processing Enhancements on Partitioned Tables and Indexes   Discover more about our expertise in SQL Server.
On this page
Oracle: How to move a table to another schema?

Oracle: How to move a table to another schema?

Jul 21, 2006 12:00:00 AM 2 min read
FLASHBACK TABLE vs. DBA_OBJECTS . LAST_DDL_TIME

FLASHBACK TABLE vs. DBA_OBJECTS . LAST_DDL_TIME

Jun 6, 2008 12:00:00 AM 3 min read
SQL server: Create missing indexes with unmessing names

SQL server: Create missing indexes with unmessing names

Nov 14, 2011 12:00:00 AM 3 min read

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.