Much has been written about the [perils of Scalar UDFs][1] in SQL Server. A casual search will return oodles of results.
There are some places where a Scalar UDF is the only option, though.
As an example: when dealing with XML: XQuery can't be used as a computed column definition. One option documented by Microsoft is to use a [Scalar UDF][2] to encapsulate your XQuery in a Scalar UDF, and then use it in a computed column.
This has various effects, and some workarounds.
- Executes row by row when the table is queried
- Forces all queries against the table to run serially
You can get around the row-by-row execution by schemabinding the function, and either persisting the computed column, or indexing it. Neither of those methods can prevent the forced serialization of queries hitting the table, even when the scalar UDF isn't referenced.
Is there a known way to do that?
[1]: https://dba.stackexchange.com/questions/72330/is-support-for-parallel-scalar-udf-a-reasonable-feature-request
[2]: https://docs.microsoft.com/en-us/sql/relational-databases/xml/promote-frequently-used-xml-values-with-computed-columns
**Yes** if you:
* are running SQL Server 2014 or later; and
* are able to run the query with **trace flag 176** active; and
* the computed column is `PERSISTED`
Specifically, at least the following versions [are required][1]:
* Cumulative Update 2 for SQL Server 2016 SP1
* Cumulative Update 4 for SQL Server 2016 RTM
* Cumulative Update 6 for SQL Server 2014 SP2
**BUT** to avoid a bug (ref for [2014][2], and for [2016 and 2017][3]) introduced in those fixes, instead apply:
* [Cumulative Update 1 for SQL Server 2017][4]
* [Cumulative Update 5 for SQL Server 2016 SP1][5]
* [Cumulative Update 8 for SQL Server 2016 RTM][6]
* [Cumulative Update 8 for SQL Server 2014 SP2][7]
The trace flag is effective as a start-up `–T` option, at both global and session scope using `DBCC TRACEON`, and per query with `OPTION (QUERYTRACEON)` or a plan guide.
Trace flag 176 prevents persisted computed column expansion.
The initial metadata load performed when compiling a query brings in all columns, not just those directly referenced. This makes all computed column definitions available for matching, which is generally a good thing.
As an unfortunate side-effect, if one of the loaded (computed) columns uses a scalar user-defined function, its presence **disables parallelism** for the whole query, **even when** the computed column is **not actually used**.
Trace flag 176 helps with this, if the column is persisted, by not loading the definition (since expansion is skipped). This way, a scalar user-defined function is never present in the compiling query tree, so parallelism is not disabled.
The main drawback of trace flag 176 (aside from being only lightly documented) is that it also prevents query expression matching to persisted computed columns: If the query contains an expression matching a persisted computed column, trace flag 176 will prevent the expression being replaced by a reference to the computed column.
For more details, see my SQLPerformance.com article, [Properly Persisted Computed Columns][8].
Since the question mentions XML, as an alternative to promoting values using a computed column and scalar function, you could also look at using a Selective XML Index, as you wrote about in [Selective XML Indexes: Not Bad At All][9].
[1]: https://support.microsoft.com/en-nz/help/3213683
[2]: https://support.microsoft.com/en-us/help/4043951/fix-warning-and-incorrect-computed-column-results-after-applying-hotfi
[3]: https://support.microsoft.com/en-us/help/4040533/fix-returns-incorrect-results-when-computed-column-is-queried-after-in
[4]: https://support.microsoft.com/en-us/help/4038634
[5]: https://support.microsoft.com/en-us/help/4040714
[6]: https://support.microsoft.com/en-us/help/4040713
[7]: https://support.microsoft.com/en-us/help/4037356
[8]: https://sqlperformance.com/2017/05/sql-plan/properly-persisted-computed-columns
[9]: https://www.brentozar.com/archive/2016/12/selective-xml-indexes-not-bad/
