8

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 docs.microsoft.com.

0

3 Answers 3

9

Are there any alternatives than downgrading or waiting for a fix from Microsoft (workarounds)?

Nothing supported for a problem with a system procedure, no.

What would be the best way to contact Microsoft to receive a fix as quickly as possible?

Open a case with Microsoft Support.

I believe they refund the cost if the cause is confirmed to be a product defect.


This issue was resolved in SQL Server 2017 CU27 and SQL Server 2019 CU15.

0
3

I have found a solution via workaround. In my case the effected primary key consisted of 2 columns. The idea is to change 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.

Seemingly Microsoft have now taken note of the issue and are going to fix it.

2

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


They have confirmed that it is a bug and are targeting a fix in SQL Server 2017 CU27.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.