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. This is the 2017 CU25 variant:
create procedure sys.sp_pkeys
(
@table_name sysname,
@table_owner sysname = null,
@table_qualifier sysname = null
)
as
declare @table_id int
-- quotename() returns up to 258 chars
declare @full_table_name nvarchar(517) -- 258 + 1 + 258
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin -- If unqualified table name
select @full_table_name = quotename(@table_name)
end
else
begin -- Qualified table name
if @table_owner = ''
begin -- If empty owner name
select @full_table_name = quotename(@table_owner)
end
else
begin
select @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name)
end
end
select @table_id = object_id(@full_table_name)
select
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
KEY_SEQ = (SELECT convert(smallint, index_column_id)
FROM sys.index_columns
WHERE object_id = @table_id AND index_id = i.index_id and column_id = c.column_id),
PK_NAME = convert(sysname,k.name)
from
sys.indexes i,
sys.all_columns c,
sys.all_objects o,
sys.key_constraints k
where
o.object_id = @table_id and
o.object_id = c.object_id and
o.object_id = i.object_id and
k.parent_object_id = o.object_id and
k.unique_index_id = i.index_id and
i.is_primary_key = 1 and
c.column_id IN
(SELECT column_id
FROM sys.index_columns
WHERE object_id = @table_id AND index_id = i.index_id)
order by 1, 2, 3, 5
And this is the SQL Server 2017 CU24 and SQL Server 2019 variant:
create procedure sys.sp_pkeys
(
@table_name sysname,
@table_owner sysname = null,
@table_qualifier sysname = null
)
as
declare @table_id int
-- quotename() returns up to 258 chars
declare @full_table_name nvarchar(517) -- 258 + 1 + 258
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin -- If unqualified table name
select @full_table_name = quotename(@table_name)
end
else
begin -- Qualified table name
if @table_owner = ''
begin -- If empty owner name
select @full_table_name = quotename(@table_owner)
end
else
begin
select @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name)
end
end
select @table_id = object_id(@full_table_name)
select
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
KEY_SEQ = convert (smallint,
case
when c.name = index_col(@full_table_name, i.index_id, 1) then 1
when c.name = index_col(@full_table_name, i.index_id, 2) then 2
when c.name = index_col(@full_table_name, i.index_id, 3) then 3
when c.name = index_col(@full_table_name, i.index_id, 4) then 4
when c.name = index_col(@full_table_name, i.index_id, 5) then 5
when c.name = index_col(@full_table_name, i.index_id, 6) then 6
when c.name = index_col(@full_table_name, i.index_id, 7) then 7
when c.name = index_col(@full_table_name, i.index_id, 8) then 8
when c.name = index_col(@full_table_name, i.index_id, 9) then 9
when c.name = index_col(@full_table_name, i.index_id, 10) then 10
when c.name = index_col(@full_table_name, i.index_id, 11) then 11
when c.name = index_col(@full_table_name, i.index_id, 12) then 12
when c.name = index_col(@full_table_name, i.index_id, 13) then 13
when c.name = index_col(@full_table_name, i.index_id, 14) then 14
when c.name = index_col(@full_table_name, i.index_id, 15) then 15
when c.name = index_col(@full_table_name, i.index_id, 16) then 16
end),
PK_NAME = convert(sysname,k.name)
from
sys.indexes i,
sys.all_columns c,
sys.all_objects o,
sys.key_constraints k
where
o.object_id = @table_id and
o.object_id = c.object_id and
o.object_id = i.object_id and
k.parent_object_id = o.object_id and
k.unique_index_id = i.index_id and
i.is_primary_key = 1 and
(c.name = index_col (@full_table_name, i.index_id, 1) or
c.name = index_col (@full_table_name, i.index_id, 2) or
c.name = index_col (@full_table_name, i.index_id, 3) or
c.name = index_col (@full_table_name, i.index_id, 4) or
c.name = index_col (@full_table_name, i.index_id, 5) or
c.name = index_col (@full_table_name, i.index_id, 6) or
c.name = index_col (@full_table_name, i.index_id, 7) or
c.name = index_col (@full_table_name, i.index_id, 8) or
c.name = index_col (@full_table_name, i.index_id, 9) or
c.name = index_col (@full_table_name, i.index_id, 10) or
c.name = index_col (@full_table_name, i.index_id, 11) or
c.name = index_col (@full_table_name, i.index_id, 12) or
c.name = index_col (@full_table_name, i.index_id, 13) or
c.name = index_col (@full_table_name, i.index_id, 14) or
c.name = index_col (@full_table_name, i.index_id, 15) or
c.name = index_col (@full_table_name, i.index_id, 16))
order by 1, 2, 3, 5
The crucial part is how KEY_SEQ is determined. It would work correctly if the second variant would be used.
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.
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 from Microsoft (workarounds)?
What would be the best way to contact Microsoft to receive a fix as quickly as possible?
Microsoft has written about a change to sp_pkeys in their release notes for CU25.
Adds support for 32 key columns to system sp_pkeys and fixes the performance regression that occurs after the installation of Cumulative Update 21 (CU21) for SQL Server 2017
FYI: There is no difference with CU26 which was just released.
This question was also cross-posted in learn.microsoft.com.
