partitioned Table is always completely rebuild without reason

[j-dc]

versions

sqlpackage /version
162.0.52.1
dotnet --version
7.0.305
get-content *.sqlproj | select-string '<sdk'
<Sdk Name="Microsoft.Build.Sql" Version="0.1.10-preview" />

Windows 11, Version 22H2, 10.0.22621

steps to reproduce

1. Create a sql project
2. Add a Partition function

CREATE PARTITION FUNCTION [PF_TEST](DATETIME2 (2))
    AS RANGE LEFT
    FOR VALUES (
       '2023-01-01 00:00:00', '2023-02-01 00:00:00'
    );

3. Add a Partition Scheme

CREATE PARTITION SCHEME [PS_TEST]
    AS PARTITION [PF_TEST]
    TO (		[PRIMARY],         [PRIMARY],         [PRIMARY]    );

4. Add a table using the partion

CREATE TABLE [dbo].[partitionTable] (
   [Timestamp]  DATETIME2 (2) NOT NULL
) on [PS_TEST] ([Timestamp])

5. build

dotnet build

6. Publish

sqlpackage -action:Publish -sourcefile:"partitiontest.dacpac" -targetServername:'.' -targetdatabasename:"partitionstest" /TargetTrustServerCertificate:true /p:GenerateSmartDefaults='True'

7.Publish again

sqlpackage -action:Publish -sourcefile:"partitiontest.dacpac" -targetServername:'.' -targetdatabasename:"partitionstest" /TargetTrustServerCertificate:true /p:GenerateSmartDefaults='True'

=> when publishing again right after the first publish, the table is completely rebuild.. twice. The reason I partition a table is because it contains a huge amount of data. So the INSERT INTO temp table takes forever and is unnecessary and unacceptable in production.

steps to reproduce (EXAMPLE)

git clone https://github.com/j-dc/PartitionTest.git
cd PartitionTest
dotnet build
.\SCRIPTS\publishLocal.ps1

No working version found.

[j-dc]

Workaround: Create the partition function with dates in this format: MDY

I presume that this explains it?

select dateformat from sys.syslanguages where name = @@LANGUAGE

In my case this returns:

	dateformat
1	mdy

This is rather confusing, considering the value column of this query:

select * from sys.partition_range_values

function_id	boundary_id	parameter_id	value
65541	1	1	2023-01-01 00:00:00.000
65541	2	1	2023-02-01 00:00:00.000

Here the dates are in a 'sortable' format. This format has nothing to do with the defaults of my client/server. So I presumed that using the 'sortable' format as returned by partition_range_values was the best, and easiest. It wasn't.

This workaround result in this partition function (altered datetime format):

CREATE PARTITION FUNCTION [PF_TEST](DATETIME2 (2))
    AS RANGE LEFT
    FOR VALUES (
       '1/1/2023 00:00:00', '2/1/2023 00:00:00'
    );

This remains a workaround. Changing the SQL code so that the tooling is able to handle it always feels wrong. It's my opinion that tooling should be capable of handling all correct SQL code.

[agiBart]

Unfortunaly above workaround does not seem to work for me.

select dateformat from sys.syslanguages where name = @@LANGUAGE mdy

select * from sys.partition_range_values 65559 89 1 2023-09-11 00:00:00 65559 90 1 2023-09-18 00:00:00

CREATE PARTITION FUNCTION [WeeklyPartitionFunction] (datetime2(6)) AS RANGE RIGHT FOR VALUES ( '09-11-2023', '09-18-2023')

every deploy still... :-(

azure sql database deployment log .... 2023-09-12T07:01:13.3394116Z Unbinding columns from changing objects on table [earniehistory].[loonbrieven]... 2023-09-12T07:01:15.9691006Z Caution: Changing any part of an object name could break scripts and stored procedures. 2023-09-12T07:01:15.9832630Z Dropping [WeeklyPartitionScheme]... 2023-09-12T07:01:16.2140437Z Dropping [WeeklyPartitionFunction]... 2023-09-12T07:01:16.2648162Z Creating [WeeklyPartitionFunction]... 2023-09-12T07:01:16.3040378Z Creating [WeeklyPartitionScheme]... 2023-09-12T07:01:16.3235515Z Partition scheme 'WeeklyPartitionScheme' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'WeeklyPartitionScheme'. 2023-09-12T07:01:16.3388304Z Starting rebuilding table [earnieHistory].[boek_provisies]... 2023-09-12T07:01:18.0792567Z Caution: Changing any part of an object name could break scripts and stored procedures. 2023-09-12T07:01:18.0797346Z Caution: Changing any part of an object name could break scripts and stored procedures. 2023-09-12T07:01:18.1363183Z Creating [earniehistory].[boek_provisies].[IX_Modified_earniehistory_boek_provisies]... ......

[j-dc]

Another possible workaround (not in all scenario's) is adding this parameters to the sqlpackage cli:

/p:ExcludeObjectType='PartitionFunctions' /p:ExcludeObjectType='PartitionSchemes'

This will ignore any changes made to partitions.

[takoau]

Another possible workaround (not in all scenario's) is adding this parameters to the sqlpackage cli:

/p:ExcludeObjectType='PartitionFunctions' /p:ExcludeObjectType='PartitionSchemes'

This will ignore any changes made to partitions.

Doesn't work for my case. My table is partitioned and with page compression. /p:ExcludeObjectTypes=PartitionFunctions;PartitionSchemes still rebuilds the tables

[Mnior]

<IgnoreFilegroupPlacement>True</IgnoreFilegroupPlacement>
<ExcludeObjectType>PartitionFunctions PartitionSchemes</ExcludeObjectType>

Adding keys to Publish.xml also does not help.

And with DeployReport, operations are also generated:

<Alert Name="DataMotion">
<Operation Name="Drop"><!-- some constraint if exists -->
<Operation Name="UnbindTable">
<Operation Name="Create"><!-- some indexes if exists -->
<Operation Name="TableRebuild">

[ssreerama]

Hey @j-dc , I have tried to repro the issue by manually creating a project and also with your sample partitions project. I don't see any table re-creation with the re-publish. Did you happen to change the PF/PS after the publish? Would like to know if there are any additional steps required to repro the issue. Thanks

[j-dc]

Hey @j-dc , I have tried to repro the issue by manually creating a project and also with your sample partitions project. I don't see any table re-creation with the re-publish. Did you happen to change the PF/PS after the publish? Would like to know if there are any additional steps required to repro the issue. Thanks

considering previously given steps:

git clone https://github.com/j-dc/PartitionTest.git cd PartitionTest dotnet build .\SCRIPTS\publishLocal.ps1

The resulting change.sql for this steps looks like this: https://raw.githubusercontent.com/j-dc/PartitionTest/master/SCRIPTS/changeScriptOnMySystem.sql

This file contains a very unwanted/unneeded drop table.

The fact that you don't have it and I do, probably means we have different default server settings? Could it be locale settings? What extra information can I provide?

[ssreerama]

@j-dc , I have the dotnet tool version of SqlPackage installed in my machine as 162.1.167 which is the latest one available. With the provided project of SDK "<Sdk Name="Microsoft.Build.Sql" Version="0.1.10-preview" />", I'm still unable to repro the issue as the generated script with the provided PowerShell scripts is empty.

Can you verify what version installed in your machine please.

Can you check the same with the project in Visual studio and do right click and 'publish', and then right click on project again and do 'Generate Script'? does this process also giving the same result to you? for this I'm using Visual studio 2022 and deployed the database on SQL server 2022, also used the SqlPackage version of 162.1.167.1(latest) to publish and generate script manually.

Microsoft.Build.Sql SDK version: 0.1.10-preview NET version: 162.1.167.1 Core version to test: sqlpackage-win7-x64-en-162.1.167.1 dotnet version installed microsoft.sqlpackage: 162.1.167

I would suggest to update all to latest versions and give a try. Please get the latest from here: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15. Thanks

[ssreerama]

@j-dc , Do you have any update? Thanks