SQL Server 2017 - CU25 - sp_pkeys - Wrong order

Question

After our customers have installed CU25 there is a problem with the stored procedure sp_pkeys.
It may now return the wrong order if a primary key has multiple columns.

It can be tracked down in the code itself.
Compare the CU25 with the CU24/2019 variant of the attachments:
132247-sql2017-cu25.txt
132223-sql2017-cu24-or-sql2019.txt

The crucial part is how KEY_SEQ is determined.
It would work correctly if the second variant would be used (CU24 or 2019).

Alternatively even the first variant would work if the sub-select would be done like this:

SELECT convert(smallint, key_ordinal)  
FROM sys.index_columns   
WHERE object_id = @table_id AND index_id = i.index_id and column_id = c.column_id  

So key_ordinal instead index_column_id needs to be used.

The release notes mention a change to sp_pkeys. See https://support.microsoft.com/en-us/topic/kb5003830-cumulative-update-25-for-sql-server-2017-357b80dc-43b5-447c-b544-7503eee189e9#bkmk_13975159.

However as it is a system stored procedure it seemingly is not possible to change it.
At least not without very critical and unadvised steps.

Are there any alternatives than downgrading or waiting for a fix?

Edit: Just tested CU26. There is no change regarding the stored procedure.

Answer 1

I have found a solution via workaround. In my case the effected primary key consisted of 2 columns.
This picks up the idea from above about changing the column order but without having to recreate the whole table.
The goal is to move the first column of the primary key behind the second one in the table.
So not changing the order of the primary key (index) but the column order in the table itself.
This then results in the correct order via sp_pkeys.

The solution goes as follows:

• Dropped the primary key.
• Created a temporary column.
• Copied the value of all rows from the original column (first column of pk) to this temporary column.
• Dropped the original column.
• Recreated the original column. Now it is behind the second column of the primary key.
• Copied the value of all rows from the temporary column to the new column.
• Dropped the temporary column.
• Made the new column not nullable.
• Recreated the primary key.

Obviously this may not be a solution to others as they maybe cannot do such table modifications.
In general this also means downtime for the application (should not be accessed whilst doing the steps above).

Just as an idea if others are in the same pickle and need workarounds.
Nevertheless the fix from Microsoft obviously is still needed as a proper solution.

Thanks to everyone for their ideas and support :-)

Answer 2

I'm one of the Support Escalation Engineers for SQL Server that work at Microsoft. I have received a case for this issue and will be engaging the SQL Server Product Group about it.

Answer 3

I agree KEY_SEQ is not correct. However, why does it make a difference to your code?

Answer 4

After our app starts it does a validation of the database schema and compares it with the defined schema in the application code.
If the validation fails it does not start as it a undefined state. The validation now incorrectly fails as the stored procedure returns the wrong order for a primary key.
The structure actually is correct. The stored procedure results in a misinterpretation.

Changing this logic is not trivial for us. We use a component of another team/company that has the validation logic (so not our code). This component itself uses the schema data functions of the JDBC MSSQL driver. That driver itself uses the stored procedure for the primary keys.

So the best solution would be a fix of the stored procedure. Seemingly other people are also affected.