SQL Server : ADO.Net GetUpdateCommand ok with PK, fails after adding another clustered unique index

Question

I have such a table with a non-clustered primary key:

CREATE TABLE [dbo].[StudentGrade](
    [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
    [CourseID] [nvarchar](10) NOT NULL,
    [StudentID] [int] NOT NULL,
    [Grade] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_StudentGrade] PRIMARY KEY NONCLUSTERED ([EnrollmentID] ASC))

The select statement is:

select EnrollmentID,Grade from StudentGrade

GetUpdateCommand works fine, the non-clustered primary key is recognized.

When adding another clustered unique index (on the both columns not even part of the select clause):

CREATE UNIQUE CLUSTERED INDEX [Badguy] ON [dbo].[StudentGrade] ([CourseID] ASC, [StudentID] ASC)

GetUpdateCommand fails with this exception:

System.InvalidOperationException: Dynamic SQL generation for the
UpdateCommand is not supported against a SelectCommand that does not return
any key column information.

If the index is not unique OR not clustered, there is no error.

It looks like the clustered unique index is preferred against the primary key for describing metadata. And if there are several key candidates (pk / unique index), it does not try to use the one having all columns in the query.

Is this an expected behaviour ? Any simple way to fix apart from selecting indexed columns or expliciting the update command ?

I need to have this clustered index for performance purposes, the primary key is not often used in queries.

Answer 1

It looks like the clustered unique index is preferred against the primary key for describing metadata.

Yes, that's the crux of the issue. You can see this using sp_describe_first_result_set. With only the non-clustered primary key in place, the EnrolmentID column is the column identified as "is_part_of_unique_key":

EXEC sp_describe_first_result_set
      @tsql = N'SELECT EnrollmentID,Grade FROM StudentGrade'
    , @params = NULL
    , @browse_information_mode = 1;

+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal |     name     | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
|         0 |              1 | EnrollmentID |           0 |             56 | int              |          4 |        10 |     0 | NULL           | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | StudentGrade | EnrollmentID  |                  1 |                     1 |             0 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |          56 |          4 | NULL             | NULL                  |
|         0 |              2 | Grade        |           0 |            106 | decimal(3,2)     |          5 |         3 |     2 | NULL           | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | StudentGrade | Grade         |                  0 |                     0 |             1 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |         106 |         17 | NULL             | NULL                  |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+

After adding the unique clustered index on the composite natural key columns, CourseID and StudentID become the unique key preferred by client APIs. These are returned as hidden metadata columns flagged as "is_part_of_unique_key":

+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+------------------------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal |     name     | is_nullable | system_type_id | system_type_name | max_length | precision | scale |        collation_name        | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+------------------------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
|         0 |              1 | EnrollmentID |           0 |             56 | int              |          4 |        10 |     0 | NULL                         | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | StudentGrade | EnrollmentID  |                  1 |                     0 |             0 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |          56 |          4 | NULL             | NULL                  |
|         0 |              2 | Grade        |           0 |            106 | decimal(3,2)     |          5 |         3 |     2 | NULL                         | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | StudentGrade | Grade         |                  0 |                     0 |             1 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |         106 |         17 | NULL             | NULL                  |
|         1 |              3 | CourseID     |           0 |            231 | nvarchar(10)     |         20 |         0 |     0 | SQL_Latin1_General_CP1_CI_AS | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | StudentGrade | CourseID      |                  0 |                     1 |             1 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |         231 |         20 | 13632521         | 52                    |
|         1 |              4 | StudentID    |           0 |             56 | int              |          4 |        10 |     0 | NULL                         | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | StudentGrade | StudentID     |                  0 |                     1 |             1 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |          56 |          4 | NULL             | NULL                  |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+------------------------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+

The unique clustered index is preferred because that is the most efficient key to use for singleton queries. The implication is the client needs the key values returned by the SELECT query in order for auto-generated CRUD statements to function.

There are a couple of options to avoid adding the natural key columns to your select query. One method is to create view with the VIEW_METADATA option that encapsulates the query and use that in your code instead of the table directly:

CREATE VIEW vw_StudentGrade
WITH VIEW_METADATA
AS
SELECT EnrollmentID, Grade from StudentGrade;

The metadata is then limited to the columns returned by the view so the EnrollmentID is recognized as the unique key column even with the unique clustered index (or unique constraint) in place:

EXEC sp_describe_first_result_set
      @tsql = N'SELECT EnrollmentID,Grade FROM vw_StudentGrade'
    , @params = NULL
    , @browse_information_mode = 1;

+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+-----------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal |     name     | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema |  source_table   | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+-----------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
|         0 |              1 | EnrollmentID |           0 |             56 | int              |          4 |        10 |     0 | NULL           | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | vw_StudentGrade | EnrollmentID  |                  1 |                     1 |             0 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |          56 |          4 | NULL             | NULL                  |
|         0 |              2 | Grade        |           0 |            106 | decimal(3,2)     |          5 |         3 |     2 | NULL           | NULL         | NULL               | NULL             | NULL           | NULL                         | NULL              | NULL                    | NULL                  | NULL                |               0 |                 0 |                        0 | NULL          | tempdb          | dbo           | vw_StudentGrade | Grade         |                  0 |                     0 |             1 |                  0 |                    0 | NULL                     | NULL                   | NULL                 |         106 |         17 | NULL             | NULL                  |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+-----------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+

The other option is to manually create the UpdateCommand instead of using CommandBuilder. That will give you full control so you can use EnrollmentID in the WHERE clause regardless of the metadata returned.