SQL Server Data Tools and partition functions

Question

I'm creating a sliding window loading scenario and the partition functions will be changing their boundaries through time.

I have created some partition functions in my SQL Server Data Tools (SSDT) database project with some initial boundaries hardcoded.

However, as time goes on and the partition functions' boundaries change, future SSDT database publishes will revert the boundaries to the original ones.

Is there some way to handle this scenario gracefully, possibly by disabling the publishing of the partition functions?

I've tried changing the Build Action property of the partition functions in SSDT, from the default Build, to None, but then the project fails to build due to a missing reference in the depending objects.

Answer 1

I was able to find a solution to my problem — hope this helps someone else.

To avoid each database publish recreating the partition function, you can check the Ignore partition schemes option in the Advanced Publish Settings dialog (Advanced... button in the Database Publish dialog).

From the description of the option (emphasis mine):

Specifies whether differences in partition schemes and functions should be ignored or updated when you publish to a database.

However, if you have defined a partitioned object (table or index) with page or row compression, although the Ignore partition schemes option no longer recreates the partition function, the partitioned object will get recreated anyway.

This happens because the partitioned object gets scripted with the compression defined per partition, and since the object has a different number of partitions than it was originally defined, SSDT recreates the object on publish. E.g. (formatted):

WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1)
     , DATA_COMPRESSION = PAGE ON PARTITIONS (2)
     , ...

In order for this not to happen, you can also check the Ignore table options option, in the same Advanced Publish Settings dialog — just keep in mind that you will be ignoring other options, such as ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS (reference of table options).

Answer 2

Ouch, that's not a good situation. As far as I know SSDT doesn't support this. The best workaround I can think of is to use a Pre-Deployment script to store the boundaries in a table and then alter the partition function using those values in the Post-Deployment script.