question

db2222-2862 avatar image
0 Votes"
db2222-2862 asked DanGuzman commented

SQL Server 2017 - CU25 - sp_pkeys - Wrong order

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.


sql-server-general
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

The only work-around I can think of is to reorder the table columns such that it matches the primary key order.



1 Vote 1 ·

Hi Dan,

thank you for your response.

Yes, that is also what I considered but it comes with other problems. Or is there another way to reorder the table columns without having to recreate the whole table (unfortunately already has content).

0 Votes 0 ·

Unfortunately, the only way to reorder columns is to recreate the table. Tools like SSDT can facilitate this, also taking care of constraints and indexes.

0 Votes 0 ·

Hi, This is probably because the order is determined by the way the query planner decides to build the result set.
The order can even change between two identical queries, simply because the data changes between those queries.

0 Votes 0 ·

Hi, thanks for your response. However this is not the case. The ordering is deterministic.
It orders by "1 (TABLE_QUALIFIER), 2 (TABLE_OWNER), 3 (TABLE_NAME), 5 (KEY_SEQ)".
So the needed order by KEY_SEQ is included at the right place.

The problem definitely has to do with the stored procedure code change in CU25.

0 Votes 0 ·
db2222-2862 avatar image
0 Votes"
db2222-2862 answered

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 :-)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db2222-2862 avatar image
0 Votes"
db2222-2862 answered DanGuzman commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

I think we all agree a fix is the best solution. Consider opening a support case with Microsoft. They might already have a fix available or can develop one.

0 Votes 0 ·

I realize it is not the best option, but rather than use the proc, you could use the INFORMATION_SCHEMA views to retrieve the information. Which would give you more control over the return values.

0 Votes 0 ·
DanGuzman avatar image DanGuzman TomPhillips-1744 ·

If a code change is a viable solution, @db2222-2862 could also use the object catalog views (e.g. sys.index_columns) to determine key ordinals.

0 Votes 0 ·
PhilipBarry-0004 avatar image
2 Votes"
PhilipBarry-0004 answered DanGuzman commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I mentioned that I'd raised this with the SQL Server Product Group. They have confirmed that it is a bug and are targeting a fix in SQL Server 2017 CU27.

0 Votes 0 ·
DanGuzman avatar image DanGuzman PhilipBarry-0004 ·

Thanks for the update, @PhilipBarry-0004 .

0 Votes 0 ·