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.
Best Answer
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):
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):
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_LOCKSandALLOW_PAGE_LOCKS(reference of table options).