Check Partition Counts for Tables

When a table is created, it has one partition. When you use ALTER TABLE ... ADD COLUMN to add new columns, a new partition is added to the table. Adding multiple columns with a single ALTER TABLE ... ADD COLUMN statement only adds one partition.

There is a limit of 999 partitions per table. Exceeding this number generates error 8204. An extra read for each new partition slightly degrades performance for each of the new partitions. A high partition count should be avoided. On replicated tables that have multiple partitions, additional space is used for each update on the subscriber side, proportional to the number of partitions. This can result in the subscribers using slightly more perm space than the master.

The partition value for each table is tracked in the SYS16 column of the system table, SYS.TABLES. Obtain the partition counts for tables by using the following query: 

SELECT tblname, sys16 FROM SYS.TABLES;

If you discover that a table has too many partitions, do one of the following:

  • Recreate the table.

  • Save and restore the table. Use ttMigrate -c to create a migration file. Then restore the table without additional partitions by using ttMigrate -r -relaxedUpgrade.

ALTER TABLE ... DROP COLUMN does not remove partitions from a table. On replicated systems, all master and subscriber databases must be migrated using the -relaxedUpgrade option. Replication does not occur for tables that have different partition structures.