The order of index output differs between .NET Framework and .NET Core.
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- SqlPackage or DacFx Version: 162.5.57.1
- .NET Framework (Windows-only) or .NET Core: .NET Framework and .NET Core
- Environment (local platform and source/target platforms): File output from SQL Server using ExtractTarget=schemaobjecttype
**Steps to Reproduce:**
1.Run the following script to create a test table.
```
$sql= @"
CREATE TABLE DACTestxx(
C1 int PRIMARY KEY CLUSTERED,
C2 int,C3 int,C4 int,C5 int,C6 int,C7 int,C8 int,C9 int,C10 int
)
CREATE INDEX NCIX_DACTest_IX01 ON DACTestxx(C2, C3, C4, C5) INCLUDE (C6, C7, C8, C9, C10)
CREATE INDEX NCIX_DACTest_IX02 ON DACTestxx(C5, C6, C8, C9) INCLUDE (C7, C10)
CREATE INDEX NCIX_DACTest_IX03 ON DACTestxx(C5, C6, C8, C9) INCLUDE (C7, C10)
CREATE INDEX NCIX_DACTest_IX04 ON DACTestxx(C7, C6, C8, C9) INCLUDE (C10)
"@
1..100 | %{
$tablename = $("DACTest{0:000}" -f $_)
$runSql = $sql -replace "DACTestxx", $tablename
Invoke-Sqlcmd -ServerInstance "xxxxx" -Database "DACTest" -Query $runSql -Username "xxxxxx" -Password "xxxxx" -TrustServerCertificate
}
```
2.Execute the following command to output files using the .NET Framework and .NET Core SqlPackage.
```
SqlPackage.exe /Action:Extract /p:ExtractTarget=schemaobjecttype /ssn:xxxxx /sdn:DACTest /su:xxxxxx /sp:xxxxx /SourceEncryptConnection:False /TargetFile:D:\DAC\Dactest
```
3.When the files are compared, they look like this.
**.NET Framework**
```
CREATE TABLE [dbo].[DACTest015] (
[C1] INT NOT NULL,
[C2] INT NULL,
[C3] INT NULL,
[C4] INT NULL,
[C5] INT NULL,
[C6] INT NULL,
[C7] INT NULL,
[C8] INT NULL,
[C9] INT NULL,
[C10] INT NULL,
PRIMARY KEY CLUSTERED ([C1] ASC)
);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX01]
ON [dbo].[DACTest015]([C2] ASC, [C3] ASC, [C4] ASC, [C5] ASC)
INCLUDE([C6], [C7], [C8], [C9], [C10]);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX02]
ON [dbo].[DACTest015]([C5] ASC, [C6] ASC, [C8] ASC, [C9] ASC)
INCLUDE([C7], [C10]);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX03]
ON [dbo].[DACTest015]([C5] ASC, [C6] ASC, [C8] ASC, [C9] ASC)
INCLUDE([C7], [C10]);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX04]
ON [dbo].[DACTest015]([C7] ASC, [C6] ASC, [C8] ASC, [C9] ASC)
INCLUDE([C10]);
GO
```
**.NET Core**
```
CREATE TABLE [dbo].[DACTest015] (
[C1] INT NOT NULL,
[C2] INT NULL,
[C3] INT NULL,
[C4] INT NULL,
[C5] INT NULL,
[C6] INT NULL,
[C7] INT NULL,
[C8] INT NULL,
[C9] INT NULL,
[C10] INT NULL,
PRIMARY KEY CLUSTERED ([C1] ASC)
);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX02]
ON [dbo].[DACTest015]([C5] ASC, [C6] ASC, [C8] ASC, [C9] ASC)
INCLUDE([C7], [C10]);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX04]
ON [dbo].[DACTest015]([C7] ASC, [C6] ASC, [C8] ASC, [C9] ASC)
INCLUDE([C10]);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX01]
ON [dbo].[DACTest015]([C2] ASC, [C3] ASC, [C4] ASC, [C5] ASC)
INCLUDE([C6], [C7], [C8], [C9], [C10]);
GO
CREATE NONCLUSTERED INDEX [NCIX_DACTest_IX03]
ON [dbo].[DACTest015]([C5] ASC, [C6] ASC, [C8] ASC, [C9] ASC)
INCLUDE([C7], [C10]);
GO
```
For files output using the .NET Framework's sqlpackage, the order of the output indexes is fixed. (It looks like they are in IndexId order)
However, the output order is not fixed for .NET Core's sqlpackage.(Some files are in IndexId order, but others are in a different order.)
[162.4.92 SqlPackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver16#162492-sqlpackage) includes a fix for the order of the Extract index output, but is the current index output order as expected?
> Fixes an issue where the extract operation would reorder the indexes on a table when writing the table definition out to .sql files.
It seems that the order of the .NET Framework's sqlpackage is as expected.
However, the order of the indexes output by .NET Core sqlpackage does not seem to be consistent.
If the order of the output of the .NET Core index is the expected order, what order is it sorted by?
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the issue with the provided table-creation script and SqlPackage ExtractTarget=schemaobjecttype command, then compare the .NET Framework and .NET Core index output. Trace the extraction path responsible for writing table indexes and determine whether the .NET Core order is intentional and what ordering rule it follows. Done means the expected order or inconsistency is established and covered by an appropriate regression test.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Needs clarification
- Newbie friendliness
- 43/100