Is it possible to add partitions in synapse using Partition Function

Question

We know all of us can add partitions on a table in synapse dedicated pool using CTAS. But is there any other way to add partitions on a table in synapse dedicated pool ?

Answer 1

Hi Narayana Nayudu ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As I understand your query, you want to know if there is any way to add partition on the table in dedicated sql pool in synapse other than CTAS.

You can use the ALTER TABLE command with the TRUNCATE_TARGET option. This option allows you to overwrite existing data in the partition with new data. It not an alternate to CTAS , however, you can create partition on existing data by altering the table .

To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match.

Loading data into partitions with partition switching is a convenient way to stage new data in a table that is not visible to users. It can be challenging on busy systems to deal with the locking contention associated with partition switching.

To clear out the existing data in a partition, an ALTER TABLE used to be required to switch out the data. Then another ALTER TABLE was required to switch in the new data.

In dedicated SQL pool, the TRUNCATE_TARGET option is supported in the ALTER TABLE command. With TRUNCATE_TARGET the ALTER TABLE command overwrites existing data in the partition with new data. Below is an example :

ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);  

Reference documentation: Load new data into partitions that contain data in one step

---

Hope it helps. Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'