Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism? Much has been written about the perils of Scalar UDFs 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 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?
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:


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, and for 2016 and 2017) introduced in those fixes, instead apply:


Cumulative Update 1 for SQL Server 2017
Cumulative Update 5 for SQL Server 2016 SP1
Cumulative Update 8 for SQL Server 2016 RTM
Cumulative Update 8 for SQL Server 2014 SP2


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.

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.