FillSchema() reports CLUSTERED alternate key as primary key if the primary key is declared as NONCLUSTERED

[srana1-equitable]

I have this table definition:

CREATE TABLE [dbo].[TEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ENTITYID] [int] NOT NULL,
	[LEVELNO] [smallint] NOT NULL,
	[STATUS] [smallint] NOT NULL
 CONSTRAINT [PK_TEST] PRIMARY KEY NONCLUSTERED  ([ID] ASC) ON [PRIMARY],
 CONSTRAINT [AK_TEST] UNIQUE CLUSTERED ([ENTITYID] ASC,	[LEVELNO] ASC) ON [PRIMARY]
) ON [PRIMARY]

and this C# code:

        var dt = new DataTable();
        using (var da = new SqlDataAdapter(cmd))
        {
            // This populates dt.Columns and dt.PrimaryKey
            da.FillSchema(dt, SchemaType.Source);
        }

        Console.WriteLine("Primary key columns:");
        foreach (var col in dt.PrimaryKey)
        {
            Console.WriteLine(col.ColumnName);
        }

It shows ENTITYID and LEVELNO as part of primary key, instead of ID.

[mdaigle]

Hi @srana1-equitable, you've run into a poorly documented behavior with the data adapter. When we query the server for the table metadata, the engine itself tells us which columns uniquely identify a row. Because you've defined your alternate key as clustered, the engine has decided to prefer that key because it is functioning more like a primary key typically would.

As a workaround, you should be able to manually update the PrimaryKey property for the DataTable to reference the ID column.

Can you tell me more about your use case for having a unique clustered alternate key?

[srana1-equitable]

Hi @mdaigle, I am using an existing table that I can't really modify. My actual use case is to use SqlCommandBuilder to generate the insert, update, and delete queries. I narrowed it down to this issue and asked the question. I believe I cannot really tell SqlCommandBuilder to use a different primary key. Do you know if that is possible?

[mdaigle]

@srana1-equitable I see, and what issue do you face when you try to generate those queries? A repro that shows something closer to your end goal will be helpful!