How to apply a derived column list (table and colu

问题:

I'm emulating PostgreSQL style generate_series() table-valued functions in SQL Server as such:

CREATE FUNCTION generate_series(@d1 DATE, @d2 DATE)
RETURNS TABLE AS
RETURN
  -- Ignore implementation (returning a single DATE column) for this question

This can now be used as expected:

SELECT *
FROM generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t

Now I'd like to use derived column list syntax to rename columns along with the table as such:

SELECT *
FROM generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t(col)

Which is producing this error:

Msg 195, Level 15, State 15, Line 5
'generate_series' is not a recognized function name.

... whereas this syntax (qualifying the function) ...

SELECT *
FROM dbo.generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t(col)

... is producing this error:

Msg 317, Level 16, State 1, Line 5
Table-valued function 'generate_series' cannot have a column alias.

This syntax is perfectly valid in my opinion. It works this way in PostgreSQL, standard SQL, and in "ordinary" T-SQL as well, e.g.:

SELECT *
FROM (
  VALUES (1),(2)
) t(col)

What am I doing wrong? How could I achieve this? (I'm aware of the possibility of emulating this, but I'd like to really use derived column list syntax).

I'm using SQL Server Express 2014

回答1:

The relevant part of the from clause is here.

column_alias is only supported for derived tables and @variable.function_call

[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    | rowset_function [ [ AS ] table_alias ]   
        [ ( bulk_column_alias [ ,...n ] ) ]   
    | user_defined_function [ [ AS ] table_alias ]  
    | OPENXML <openxml_clause>   
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]   
    | <joined_table>   
    | <pivoted_table>   
    | <unpivoted_table>  
    | @variable [ [ AS ] table_alias ]  
    | @variable.function_call ( expression [ ,...n ] )   
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]  
    | FOR SYSTEM_TIME <system_time>   
}  

So, at least for the time being, you would need to wrap the user_defined_function call. e.g. in a derived table or CTE to alias the columns.

回答2:

SELECT *
FROM
    (
       SELECT *
       FROM dbo.generate_series(
        CAST ('2005-07-01' AS DATE),
        CAST ('2005-07-31' AS DATE)
    )) t(col)

I would guess it is because although dbo.generate_series is a function it is returning a table. so:

SELECT *
FROM
  TableName t(Col)

Wouldn't work

but

SELECT *
FROM
(
   SELECT *
   FROM
     TableName t
) t(col)

Would work

So in other words Derived Columns list will work with a derived table but not when the table is NOT derived?

I would assume the preferred syntax which I am sure you know is to use column aliases rather than derived column list.

SELECT d as Col
FROM
    dbo.generate_series(
        CAST ('2005-07-01' AS DATE),
        CAST ('2005-07-31' AS DATE)
    ) t

回答3:

You can't add a column alias in this case. This is not using a table valued constructor, it is returning from a table valued function. Just drop the column alias and it is fine.

SELECT *
FROM dbo.generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t

FWIW, I would suggest using a tally table instead of recursive cte in your function. If your date range is lengthy the performance is going to suffer because you are effectively using a rcte to count. If you examine the execution plan it is the same thing as a while loop. Jeff Moden has an awesome article discussing this in great detail. http://www.sqlservercentral.com/articles/T-SQL/74118/

--EDIT--

To create an alias can you not simply add an alias?

SELECT t.d as MyAlias
FROM dbo.generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t