Is it possible to name the primary key for a table returned by a UDF?

Question

I have a user defined function similar to the following:

create function paying_customers(@report_month int, @report_year int)
returns @customer_subs table
(
  customer_id int primary key not null,
  amount money not null
)
as
begin
etc.

This works fine, but I'd like to name the primary key (which seems to remain as an object in the database).

The following gives an error message (incorrect syntax near the keyword constraint):

create function paying_customers(@report_month int, @report_year int)
returns @customer_subs table
(
  customer_id int not null,
  amount money not null,
  constraint pk_paying_customers primary key clustered (customer_id)
)
as
begin
etc.

I'm trying to name the primary key as we have a script that compares databases and it finds a difference - an object is created in sys.objects for the primary key for the temporary returned table the first time the UDF is run (I'm not sure why this should continue to exist....).

Answer 1

When you specify a primary key on the table variable returned by the function, it gets a name, but it's some sort of default name, and as far as I can see you can't specify your own within the function declaration syntax. To see what auto-named primary keys are in your database, use this query:

SELECT 
    i.name AS IndexName, 
    OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
    COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
and i.Name like 'PK[_][_]%'

When I created the example function the OP mentioned, and then ran the above query, I found that the primary key for the table returned by that function was named "PK__paying_c__CD65CB8504D1D82C".

I did find that you can rename the primary key after the function has been created. Continuing the example, it would be:

exec sp_rename 'PK__paying_c__CD65CB8504D1D82C', 'PK_PayingCustomersAreGreat'

Why do any of that? No great reasons, but in my case we had a naming convention that is supposed to be adhered to, and these auto-named primary keys started showing up.