none
Why is ORDER BY allowed in TVF but not in INLINE TVF?

    Question

  • There must be a real simple explanation which escapes me right now. Thanks.

    Does it mean I can trick the database engine? Use TVF with ORDER BY instead of ITVF?

    CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
    RETURNS @Sales TABLE (ProdID int, Name varchar(50), Total money)
    AS
    BEGIN  INSERT @Sales
        SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
        FROM Production.Product AS P 
        JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
        JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
        JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
        WHERE C.StoreID = @storeid
        GROUP BY P.ProductID, P.Name
    	ORDER BY P.ProductID, P.Name;
    	RETURN ;
    END
    GO
    
    CREATE FUNCTION Sales.ufn_SalesByStore1 (@storeid int)
    RETURNS TABLE
    AS
    RETURN 
    (
        SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
        FROM Production.Product AS P 
        JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
        JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
        JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
        WHERE C.StoreID = @storeid
        GROUP BY P.ProductID, P.Name
    	ORDER BY P.ProductID, P.Name;
    );
    GO
    /*
    Msg 1033, Level 15, State 1, Procedure ufn_SalesByStore1, Line 13
    The ORDER BY clause is invalid in views, inline functions, derived tables, 
    subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
    */

    BOL: "Inline user-defined functions are a subset of user-defined functions that return a tabledata type. Inline functions can be used to achieve the functionality of parameterized views."

    http://msdn.microsoft.com/en-us/library/ms189294(v=sql.105).aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    Saturday, June 29, 2013 8:49 PM

Answers


  • The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions ...

     

    From Inside Microsoft SQL Server 2008: T-SQL Programming:
    - page 64: "Inline table-valued UDFs are similar to views in the sense that their returned table is defined by a query specification ...  SQL Server actually treats inline UDFs very similarly to views. The query processor replaces an inline UDF reference with its definition; in other words, the query processor “expands” the UDF definition and generates an execution plan accessing the underlying objects."
    - page 3: "A view is similar to a table in the sense that it represents a logical entity with no predetermined order to its rows"


    ORDER BY Clause 

    Sorting Rows with ORDER BY



    Saturday, June 29, 2013 10:04 PM
  • ORDER BY is always permitted when you insert into a table, alhough it does not mean anything unless the table has a target table has an IDENTITY column or a column with default of NEXT VALUE FOR (SQL 2012 only).

    Thus, in the example you cannot trick anything. The Database Engine is free to completely ignore your ORDER BY clause.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 29, 2013 10:27 PM

All replies


  • The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions ...

     

    From Inside Microsoft SQL Server 2008: T-SQL Programming:
    - page 64: "Inline table-valued UDFs are similar to views in the sense that their returned table is defined by a query specification ...  SQL Server actually treats inline UDFs very similarly to views. The query processor replaces an inline UDF reference with its definition; in other words, the query processor “expands” the UDF definition and generates an execution plan accessing the underlying objects."
    - page 3: "A view is similar to a table in the sense that it represents a logical entity with no predetermined order to its rows"


    ORDER BY Clause 

    Sorting Rows with ORDER BY



    Saturday, June 29, 2013 10:04 PM
  • ORDER BY is always permitted when you insert into a table, alhough it does not mean anything unless the table has a target table has an IDENTITY column or a column with default of NEXT VALUE FOR (SQL 2012 only).

    Thus, in the example you cannot trick anything. The Database Engine is free to completely ignore your ORDER BY clause.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 29, 2013 10:27 PM
  • The ORDER BY ispart of a cursor and not part of a SELECT (or any other declarative SQL statement). Since a SELECT produces a table, and tables have no ordering by definition, it makes no sense. It also screws up any hope for optimization. 

    But more than that, you should not be writing UDFs of any kind. It is s a proprietary feature that does not, optimize and tells the world that your mindset is still locked back in a procedural language and has not evolved to sets and declarative coding.  You even put the old "fn_" prefix on the names! We had to do that in FORTRAN II and then in 1960's BASIC to signal the compiler. This is a version of a bad coding practice called "tblling" (for putting "tbl-" on table names. An SQL programmer would have used  a VIEW. The VIEW can be expanded in lien, optimized and ported. He also would not have all of those ISO-11179 violations and design flaws you have, but that is another issue; here is a clean up:

    CREATE VIEW Store_Sales
    (store_id, product_id, product_name, product_sales_tot)
    AS
    SELECT SH.store_id, P.product_id, P.product_name, SUM(SD.order_qty * P.unit_price)
        FROM Products AS P, 
             SalesOrderDetails AS SD,
             SalesOrders AS SH
     WHERE SD.product_id = P.product_id
       AND SH.sales_order_nbr = SD.sales_order_nb
     GROUP BY SH.store_id, P.product_id, P.product_name;

    --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, June 30, 2013 12:36 AM
  • Joe,

    Trouble with VIEWs that they don't take parameters.

    Consider INLINE TABLE-VALUED function as a view with parameters:

    /******** INLINE table-valued user-defined function ***********/
    CREATE FUNCTION  ufnSalesByCountry (@CountryTerritory varchar(60))
    RETURNS TABLE AS
    RETURN 
    SELECT TOP 1000 [SalesPersonID]
          ,[FullName]
          ,[JobTitle]
          ,[SalesTerritory]
          ,[2006]
          ,[2007]
          ,[2008]
      FROM [AdventureWorks2012].[Sales].[vSalesPersonSalesByFiscalYears]
      WHERE SalesTerritory = @CountryTerritory;
    GO
    
    SELECT * FROM ufnSalesByCountry('Germany');
    GO
    /*
    SalesPersonID	FullName	JobTitle	SalesTerritory	2006	2007	2008
    288	Rachel B Valdez	Sales Representative	Germany	NULL	NULL	1827066.7118
    */

    BOL: "Comparing TVF to tables and views

    So what's the difference between a TVF and a table? A TVF produces a dynamic table at the time of execution, depending on parameters. So, is it like a view? Well, yes and no. Like a view, a TVF creates a result set only when it's executed, but, unlike a view, it can be parameterized. If you need a subset of rows from a view, you need to apply a search criteria in a WHERE clause, but an ITVF can accept search criteria as function parameters. Let's look at some examples and see how the same task can be implemented via a stored procedure, view, ITVF, and MTVF."

    http://msdn.microsoft.com/en-us/library/aa175924(v=sql.80).aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, June 30, 2013 3:41 PM