Make Scalar UDF with no data access running parallelly
There are different scalar-value UDFs:
- (1) Interpreted T-SQL UDF,
- (2) Natively Complied T-SQL UDF and
- (3a) CLR UDF with DataAccessKind.None, which is default option.
- (3b) CLR UDF with DataAccessKind.Read.
For CLR UDF, if its USERDATAACCESS = 0, then it�s not a parallel plan blocker.
For other type of UDF, even we use WITH SCHEMABINDING option, and USERDATAACCESS = 0, it�s still a parallel plan blocker.
Scalar UDF Inlining is being introduced in SQL Server 2019 and also in Azure SQL Database under compatibility level 150. Where costing is applicable - parallel plans can now be generated with this feature for T-SQL scalar UDFs. For more on this - see https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=azuresqldb-current&viewFallbackFrom=sql-server-2017