Can Scalar Functions Be Applied Before Filtering When Executing a SQL Statement

Filter SQL query based on scalar function result

When I try both ways, they have the exact same execution plan, so looks like the query optimizer is smart enough to find out that they are the same query and run it once...
So both ways are OK I assume...

But if want to use the subquery approach and use the alias you can use a something like this:

Select * from (
SELECT *, [dbo].[myFunc] (X.Prop1) AS Width FROM X 
) T
WHERE Width > 0

How are the scalar functions executed by the optimizer

For scalar UDFs that aren't inlined, the actual query plan is only generated once (obviously assuming no statistics updates, index rebuilds or any DDL changes in between executions). The plan is cached and re-used like any other query plan.

It does not get any data in sys.dm_exec_query_stats, so you cannot get any statistics on it that way. But you can use sys.dm_exec_function_stats to get similar info.

You can see the cached query plan using the following query

SELECT
  p.objtype,
  OBJECT_NAME(f.object_id),
  st.text,
  qp.query_plan,
  p.refcounts,
  p.usecounts,
  f.execution_count
FROM sys.dm_exec_function_stats f
JOIN sys.dm_exec_cached_plans p ON p.plan_handle = f.plan_handle
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) st
WHERE f.object_id = OBJECT_ID(N'dbo.fnTest')
OPTION(RECOMPILE);

You can turn this on or off using the Database Scoped Configuration EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS (so far that knob is just for Azure SQL DB and Managed Instance).

---

The actual execution of the function does occur in a separate scope, requiring marshalling of the parameters and return result back and forwards, as well as context switching (different SET options, and different local variable scopes for example). This is one of the main reasons they perform so badly.

SUBSTRING in a Scalar UDF - Unexpected Return

When not specifying the size of a (n)varchar parameter you get the default length of 1 character.

Change

CREATE FUNCTION [dbo].[test8] (@input nvarchar)

to

CREATE FUNCTION [dbo].[test8] (@input nvarchar(4))

question about aggregate function internals in SQL/Postgres

So, I found this, http://helmingstay.blogspot.com/2009/06/postgresql-poetry-aggregate-median-with.html, which claims that it does indeed use the accumulator pattern. Hmmm.

Calling a SQL User-defined function in a LINQ query

Ok, I think I understand the question - the gist of it is you want to be able to call a SQL UDF as part of your Linq to Entities query.

This is if you're using database or model first:

This article explains how to do it: http://msdn.microsoft.com/en-us/library/dd456847(VS.100).aspx

To sum it up, you first need to edit your edmx file in an xml editor, in the edmx:StorageModels >> Schema section you need to specify a mapping to your sql udf, eg

<Function Name="SampleFunction" ReturnType="int" Schema="dbo">
    <Parameter Name="Param" Mode="In" Type="int" />
</Function>

Then you need to create a static function somewhere with the EdmFunction attribute on it, something like this:

public static class ModelDefinedFunctions
{
    [EdmFunction("TestDBModel.Store", "SampleFunction")]
    public static int SampleFunction(int param)
    {
      throw new NotSupportedException("Direct calls are not supported.");
    }
}

This method will get mapped to the UDF at query time by entity framework. The first attribute argument is the store namespace - you can find this in your edmx xml file on the Schema element (look for Namespace). The second argument is the name of the udf.

You can then call it something like this:

var result = from s in context.UDFTests
            select new
            {
                TestVal = ModelDefinedFunctions.SampleFunction(22)
            };

Hope this helps.

SUBSTRING in a Scalar UDF - Unexpected Return

When not specifying the size of a (n)varchar parameter you get the default length of 1 character.

Change

CREATE FUNCTION [dbo].[test8] (@input nvarchar)

to

CREATE FUNCTION [dbo].[test8] (@input nvarchar(4))

---

Related Topics