SQL Server CLR UDF Parallelism redux

c#, parallel-processing, sql-server, sqlclr

Solution

According to the MSDN forum that is linked to in the first comment on the question, your C# code roughly starts out as:

 [Microsoft.SqlServer.Server.SqlFunction()]
    public static SqlString MyUDF(SqlString data)

and according to the question you have added `DataAccessKind.None` making it:

 [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlString MyUDF(SqlString data)

To start with, both `DataAccess` and `SystemDataAccess` default to `DataAccessKind.None`, so setting them explicitly to `DataAccessKind.None`, while a good practice, shouldn't have made any noticeable difference.

There are two other properties that need to be set: `IsDeterministic` and `IsPrecise`. These properties are meta-data that the Query Optimizer uses and are both `false` by default. Hence, it is best to set one or both of them to true (assuming, of course, that the settings accurately reflect the code within that particular function).

- Determinism means that the same inputs are guaranteed to have the same output. So if your function will always return the same value for a particular set of inputs, then it is deterministic and should be marked as `IsDeterministic = true`.

- You can set `IsPrecise = true` if you are not using any floating point (i.e. `Double` or `Single`) values (i.e. `FLOAT` or `REAL` in T-SQL terms).

The SqlFunction attribute should look as follows:

[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess = DataAccessKind.None,
   DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
   public static SqlString MyUDF(SqlString data)

UPDATE:

- One additional item that is probably required is that the assembly containing this method has a `PERMISSION_SET` of `SAFE`.

- It is probably not required to have the `IsPrecise` property of the `SqlFunction` attribute set to `true` in order to get the UDF to work in a parallel execution plan.

Problem

I have been researching SQL Server CLR UDFs and parallelism for some time. The general consensus seems to be that in SQL Server 2008 and later, a scalar value CLR UDF with `DataAccessKind.None` should allow parallel execution. However, when I use my scalar value UDF in my view in SQL Server 2012, it still kills parallel execution in joins and the like. Is there something special I need to add to my C# code or the T-SQL UDF definition to indicate that it is safe for parallel execution? Thanks.

Original source