Use the partition by clause to partition an unpartitioned table or repartition an already partitioned table. The task requires a data copy; all data rows are redistributed according to the specified partition criteria.
You may run this task in parallel if the SAP ASE server is configured for parallel processing. You must set the select into/bulkcopy/pllsort option to true. If the table has indexes, you must drop the indexes before you can change:
An unpartitioned table into a semantic-partitioned table.
The partitioning type.
The partitioning key – you need not drop indexes to change other attributes of the partitions, such as number of partitions, partition bounds, or partition location; the indexes are built automatically.
You can use the add partition clause to add empty partitions to list- or range-partitioned tables, but not to hash or round-robin-partitioned tables.
For range-partitioned tables, you can add new partitions only to the high end of the partition conditions. If the last existing partition has the maximum boundary (values <= (MAX)), you cannot add new partitions.
The partition number_of_partition and unpartition clauses are provided for compatibility with versions of SAP ASE earlier than 15.0. You can use partition number_of_partition only on unpartitioned tables to add (number_of_partition-1) empty round-robin partitions; existing data is placed on the first partition, with subsequent data distributed among all partitions. If the table has a global clustered index, the SAP ASE server places subsequent data rows in the first partition. To redistribute the data, drop and re-create the index.
You can use the unpartition clause only on round-robin-partitioned tables without indexes.
You cannot partition system tables.
You cannot partition remote proxy tables.
You cannot issue the partition-related alter table commands within a user-defined transaction.
You cannot change a table’s partitioning properties using the partition by clause if there are active open cursors on the table.
After using the partition by clause, you must perform a full database dump before you can use dump transaction.
You cannot drop a column that is part of a partitioning key.
Alter key columns with care. In some cases, modifying the datatype of a key column might redistribute data among partitions. See the Transact-SQL Users Guide.
Changing a table’s partitioning properties increments the schema count, which causes existing stored procedures that access this table to recompile the next time they are executed.