Table-Valued Function - Order by Is Ignored in Output

Table-Valued function - Order by is ignored in output

There were two things wrong with your original approach.

1. On inserting to the table it was never guaranteed that the ORDER BY on the INSERT ... SELECT ... ORDER BY would be the order that the rows were actually inserted.
2. On selecting from it SQL Server does not guarantee that SELECT without an ORDER BY will return the rows in any particular order such as insertion order anyway.

In 2012 it looks as though the behaviour has changed with respect to item 1. It now generally ignores the ORDER BY on the SELECT statement that is the source for an INSERT

DECLARE @T TABLE(number int)

INSERT INTO @T 
SELECT number
FROM master..spt_values
ORDER BY name

2008 Plan

2012 Plan

The reason for the change of behaviour is that in previous versions SQL Server produced one plan that was shared between executions with SET ROWCOUNT 0 (off) and SET ROWCOUNT N. The sort operator was only there to ensure the correct semantics in case the plan was run by a session with a non zero ROWCOUNT set. The TOP operator to the left of it is a ROWCOUNT TOP.

SQL Server 2012 now produces separate plans for the two cases so there is no need to add these to the ROWCOUNT 0 version of the plan.

A sort may still appear in the plan in 2012 if the SELECT has an explicit TOP defined (other than TOP 100 PERCENT) but this still doesn't guarantee actual insertion order of rows, the plan might then have another sort after the TOP N is established to get the rows into clustered index order for example.

For the example in your question I would just adjust the calling code to specify ORDER BY name if that is what it requires.

Regarding your sort_id idea from Ordering guarantees in SQL Server it is guaranteed when inserting into a table with IDENTITY that the order these are allocated will be as per the ORDER BY so you could also do

DECLARE @Customer TABLE (
  Sort_Id     INT IDENTITY PRIMARY KEY,
  Customer_ID INT,
  Name        INT,
  Expired     BIT )

INSERT INTO @Customer
SELECT Customer_ID,
       Name,
       CASE
         WHEN Expiry_Date < Getdate() THEN 1
         WHEN Expired = 1 THEN 1
         ELSE 0
       END
FROM   Customer
ORDER  BY Name 

but you would still need to order by the sort_id in your selecting queries as there is no guaranteed ordering without that (perhaps this sort_id approach might be useful in the case where the original columns used for ordering aren't being copied into the table variable)

Order By In a SQL Table Valued Function

The order by needs to be in the statement that selects from the function.

SELECT CompetitionId
FROM [dbo].fTest()
ORDER BY CompetitionId

This is the only way to get reliable results that are assured to not suddenly break in the future.

When would you use a table-valued function?

Table-valued functions are "just" parameterized views. This makes them extremely powerful for encapsulating logic that would otherwise be hidden behind an opaque stored procedure. Here's an example:

Inline Table-valued Function:

create function dbo.GetClients (
    @clientName nvarchar(max) = null
)
returns table
return (
    select *
    from dbo.Clients as a
    where ((a.ClientName = @clientName) or a.ClientName is null)
);

Stored Procedure:

create procedure dbo.usp_GetClients (
    @clientName nvarchar(max) = null
)
as
begin;
    select *
    from dbo.Clients as a
    where ((a.ClientName = @clientName) or a.ClientName is null)
end;

Unlike the stored procedure call, a table-valued function allows me to compose the logic from dbo.GetClients with other objects:

select *
from dbo.GetClients(N'ACME') as a
join ... as b
    on a.ClientId = b.ClientId

In such situations I cannot imagine using a stored procedure because of how restrictive it is when compared to the table-valued function. I would be forced to marshal the data around myself using a temp table, table variable, or application layer in order to combine results from multiple objects.

Inline table-valued functions are especially awesome because of the "inline" bit which is probably best explained here. This allows the optimizer to treat such functions no differently than the objects they encapsulate, resulting in near optimal performance plans (assuming that your indexes and statistics are ideal).

How to specify that a SQLCLR table valued function is ordered?

Unfortunately no, SSDT does not have a mechanism (i.e. Attribute such as SqlFacet) for supporting this option (nor several others). Your options are:

1. Create a post-deployment SQL Script to issue an ALTER FUNCTION statement that is exactly what you want it to be. Just add a SQL script to your project, and set its "Build Action" to "PostDeploy".

2. Generate the DDL (i.e. CREATE FUNCTION...) yourself and use a "Post-build event" (under Project Properties | Build Events) to execute the SQL via SQLCMD

3. Create your own attribute that you can mark the function with, and then create a Deployment Contributor ("Build Action" = "Deployment Extension Configuration"). This would allow it to be handled inline via SSDT, but seems to be a fair bit of work.

Other options not supported via SSDT (please vote for them to be supported via the following links :-):

• Implement OnNullCall property in SqlFunctionAttribute for RETURNS NULL ON NULL INPUT
• SSDT - Support T-SQL parameter defaults for SQLCLR objects via the SqlFacet attribute when generating the publish and create SQL scripts

Due to all of these unsupported options, I rarely use SSDT for the actual deployment, and when I do, I use Post Deployment SQL scripts to do the ALTER statements (though they are not dynamic, which is why it would be better to be supported via an Attribute in the code). Most of the time I just use my own deployment script ( .CMD ) that I trigger as a Post Build event.

---

P.S. I have now submitted a Microsoft Connection Suggestion for this particular feature:

SSDT - Support ORDER clause for SQLCLR TVFs via the SqlFunction attribute when generating the publish and create SQL scripts

Inline function versus normal select

Think of an ITVF as a view you can pass parameters to. It is essentially included in your script that references it as if it was plain old SQL and then executed. This is why they perform better than multi statement table valued functions, which have to be executed as a seperate statement.

Due to this, in your examples the statement:

select *
from InlineFun (1)

Is essentially passed to the query engine as:

select *
from (select *
      from Number
      where n = 1
     ) as a

So to actually answer your question, the scope of the function is the same as the scope of the statement that calls it.

Problem using a CTE in a table valued function

Not tested but something like this should do what you want.

   SELECT job.Jobnumber, COALESCE(CAST(AVG(CAST(jobMark.Mark AS DECIMAL(18,1))) AS DECIMAL(18,1)), 0.0) AS Average
    FROM  job 
      LEFT OUTER JOIN jobMark 
        ON job.Guid = jobMark.Guid
    WHERE job.Jobnumber = @jobnumber
    GROUP BY job.Jobnumber

No need to use a CTE.

BTW: What you do is that you check for Exists in the CTE Jobnumber in the case statement. If there are no rows in the CTE you will end up in the else part but since you use the CTE Jobnumber in the from clause of the main query you will not get any rows because the CTE Jobnumber did not return any rows.

So to be perfectly clear of what is happening. The case statement will never be executed if there are no rows in the CTE Jobnumber.

Updating a set of data from the output of a Table Valued Function

It has been two days and no answers, I just stuck with my cursor solution as it was performant enough for what I needed to do.

I did use a different method when I had to loop through a second time to find people who where not mapped in the previous pass, but it is just a while loop which behaves exactly like the previous cursor.

declare @tmpGuid uniqueidentifier
select @tmpGuid = CLIENT_GUID from #mapping where NEW_CLIENT_GUID is null 
while @@ROWCOUNT > 0
begin   
  --Set the first unset guid to itself
  update #mapping
  set NEW_CLIENT_GUID = @tmpGuid
  where CLIENT_GUID = @tmpGuid

  --set all other duplicates to the guid we just used.
  update #mapping
  set NEW_CLIENT_GUID = @tmpGuid
  where CLIENT_GUID in (select CLIENT_GUID from FindDuplicateClientsByClient(@tmpGuid))
       and NEW_CLIENT_GUID is null

  --get next guid
  select @tmpGuid = CLIENT_GUID from #mapping where NEW_CLIENT_GUID is null 
end
set nocount off
go

Insert into Select does not insert the same data order correctly

SQL result sets are unordered, unless you have an order by. SQL tables are unordered.

However, SQL Server does provide at least one method to do what you want. If you use an order by in a table with an identity column and the select has an order by, then the identity is incremented appropriately.

So, one way of doing what you want is to have such as column in [HöjdKortvågVänster]:

id int not null identity(1, 1) primary key

insert into [test].[dbo].[HöjdKortvågVänster]([Höjd kortvåg vänster (null)]) 
    select [Höjd kortvåg vänster (null)]
    from [test].[dbo].[test111]
    order by <appropriate column here>;

And then when you query the table, remember the order by:

select *
from [test].[dbo].[HöjdKortvågVänster]
order by id;

---

Related Topics