Simon Fermor Simon Fermor - 20 days ago 5
SQL Question

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

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

A UDF returns a table variable, not a concrete table (think DECLARE @t TABLE vs. CREATE TABLE), so neither the return table, nor the primary key are manifested in the database. You can't name a table variable, because you could have more than one copy floating around at any given time, so the names would be in conflict.

You could CREATE a table, give it a named primary key, and put the UDF results into the table. Or you could forget about naming the Primary Key...