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
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:
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.