none
joining a table to a user-defined function??? RRS feed

  • Question

  • Suppose I have a SQL Server table named 'gadget'. 'gadget' has an integer field named 'gadget_key', which is the primary key of the table.

    Now suppose I have a user-defined function named 'udf_gadget_values'. This function takes as its input parameter an integer variable named '@nGadgetKey'. This function returns a table which will always contain exactly one record. This one record has one field named 'nGadgetKey', which contains the same value that was passed to the function in parameter '@nGadgetKey'

    I would like to join the table 'gadget' with the table that function 'udf_gadget_values' returns kind of like this:

    SELECT TOP 10 *
    FROM gadget, udf_gadget_values(gadget.gadget_key)

    The purpose of this query is to get the top 10 records from 'gadget', as well as the values associated with each record, as returned by the function.

    The real issue is this: how do I pass gadget.gadget_key to the function as an input parameter?  Or if this will not work, is there an alternative?
    Wednesday, May 25, 2005 3:27 PM

Answers

  • Hi,

    This syntax is neither supported in Yukon nor Shiloh.  I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

    In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this.  The LHS of CROSS APPLY is a table source and the RHS is a table-valued function.  The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset.  In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset.  In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

    It would look like this:

    select * from gadget cross apply udf_gadget_values(gadget_key)

    Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list.  The examples below illustrate:

    -- Yukon :  works
    -- Shiloh: !works
    select * from gadget where exists
      (select * from udf_gadget_values(gadget.gadget_key))

    -- Yukon : works
    -- Shiloh: !works
    select *
    ,      (select gadget_desc
             from dbo.udf_gadget_values(gadget.gadget_key))
                                           as function_value
    from gadget


    Regards,
    Clifford Dibble
    Program Manager, SQL Server

    Wednesday, May 25, 2005 8:43 PM

All replies

  • Hi,

    This syntax is neither supported in Yukon nor Shiloh.  I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

    In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this.  The LHS of CROSS APPLY is a table source and the RHS is a table-valued function.  The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset.  In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset.  In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

    It would look like this:

    select * from gadget cross apply udf_gadget_values(gadget_key)

    Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list.  The examples below illustrate:

    -- Yukon :  works
    -- Shiloh: !works
    select * from gadget where exists
      (select * from udf_gadget_values(gadget.gadget_key))

    -- Yukon : works
    -- Shiloh: !works
    select *
    ,      (select gadget_desc
             from dbo.udf_gadget_values(gadget.gadget_key))
                                           as function_value
    from gadget


    Regards,
    Clifford Dibble
    Program Manager, SQL Server

    Wednesday, May 25, 2005 8:43 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. 

    >> Suppose I have a SQL Server table named "Gadgets". "Gadgets" has an integer field [sic] named "Gadgets_key", which is the primary key of the table. <<

    Tables model sets, so their names are plural or collective nouns. Columns are nothing like fields. Rows are not records [sic]. In SQL, strings use single quotes and data element name use double quotes. 

    >> Now suppose I have a user-defined function named "udf_gadgets_values". <<

    You would have another violation of ISO-11179 rules. A function or procedure has a name with “<verb>_<object>” syntax. We do not use prefixes in RDBMS; that was BASIC which needed the $ for strings, the “fn_” for in-line functions, etc. 

    But the real problem is that we hate UDF's; they do not port, they do not optimize and they can have side effects. 

    >> This function takes as its input parameter an integer variable named "@n_gadgets_key". <<

    No. The postfix “_key” is how it is used and I am afraid that the prefix “n_” means integer (that is from FORTRAN II! All integer variables begin with the letters I to N). This might be “Gadgets_id” in a properly designed schema. 

    >> This function returns a table which will always contain exactly one record [sic]. This one record [sic] has one field [sic] named "n_gadgets_key", which contains the same value that was passed to the function in parameter "@n_gadgets_key" <<

    A table with one column and one row is called a constant. If it is empty, it is cast to a NULL. Doesn't that hit you as silly? Why add the overhead and screw up the optimizer for no good reason. 

    SELECT TOP 10 G.* 
      FROM Gadgets AS G
     WHERE @in_gadget_id = G.gadget_id;

    >> The purpose of this query is to get the top 10 records [sic] from "Gadgets", as well as the values associated with each record [sic], as returned by the function. << 

    This makes no sense. I will guess that you are an OO programmer who wants SQL to look like it has methods or something. This is like using Korean grammar with English. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 23, 2012 12:00 AM
  • This is minimal polite behavior on SQL forums. 

    Minimal polite behavior is not to reply to threads from May 2005.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 23, 2012 11:01 AM