Enable batch mode for all queries
As of SQL 2014 batch mode is tied to whether the query reference any columnstore index in any way or not. It is not tied to actually retrieving data out of a columnstore index. For example you can make any query at all use batch mode by joining to a zero-row columnstore dummy table:
LEFT JOIN ZeroRowsCsDummyTable on 0=1
The optimizer deletes that join and there is zero performance impact. Real speedups can be observed in many query by mechanically slapping this onto queries.
So apparently the engine supports batch mode in all queries and it is profitable to use it.
Please enable batch mode for all queries. Don't require such a hack to be used.
There are some queries for which this hack can't be used. For example queries used to construct foreign keys or index builds feeding off of index intersections.
We have rolled out the public preview of batch mode on rowstore starting with SQL Server 2019 CTP 2.0 and coming soon to Azure SQL Database. We still won’t have this run for * all * queries given that it isn’t appropriate for all workload types, but we will expand the query optimization search space based on heuristics. Described here: https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-on-rowstore