What exactly can run in batch mode as of SQL Server 2014?
SQL Server 2014 adds the following to the original list of batch mode operators:
- Hash Outer join (including full join)
- Hash Semi Join
- Hash Anti Semi Join
- Union All (Concatenation only)
- Scalar hash aggregate (no group by)
- Batch Hash Table Build removed
It seems that data can transition into batch mode even if it does not originate from a columnstore index.
SQL Server 2012 was very limited in its use of batch operators. Batch mode plans had a fixed shape, relied on heuristics, and could not restart batch mode once a transition to row-mode processing had been made.
SQL Server 2014 adds the execution mode (batch or row) to the query optimizer's general property framework, meaning it can consider transitioning into and out of batch mode at any point in the plan. Transitions are implemented by invisible execution mode adapters in the plan. These adapters have a cost associated with them to limit the number of transitions introduced during optimization. This new flexible model is known as Mixed Mode Execution.
The execution mode adapters can be seen in the optimizer's output (though sadly not in user-visible execution plans) with undocumented TF 8607. For example, the following was captured for a query counting rows in a row store:
Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode?
It is today, yes. One possible reason for this restriction is that it naturally constrains batch mode processing to Enterprise Edition.
Could we maybe add a zero row dummy table with a columnstore index to induce batch mode?
Yes, this works. I have also seen people cross-joining with a single-row clustered columnstore index for just this reason. The suggestion you made in the comments to left join to a dummy columnstore table on false is terrific.
-- Demo the technique (no performance advantage in this case)
--
-- Row mode everywhere
SELECT COUNT_BIG(*) FROM dbo.FactOnlineSales AS FOS;
GO
-- Dummy columnstore table
CREATE TABLE dbo.Dummy (c1 int NULL);
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.Dummy;
GO
-- Batch mode for the partial aggregate
SELECT COUNT_BIG(*)
FROM dbo.FactOnlineSales AS FOS
LEFT OUTER JOIN dbo.Dummy AS D ON 0 = 1;
Plan with dummy left outer join:
Documentation is thin
True.
The best official sources of information are Columnstore Indexes Described and SQL Server Columnstore Performance Tuning.
SQL Server MVP Niko Neugebauer has a terrific series on columnstore in general here.
There are some good technical details about the 2014 changes in the Microsoft Research paper, Enhancements to SQL Server Column Stores (pdf) though this is not official product documentation.
Best Answer
SQL Server attempts to convert both strings to the correct type (according to the data type precedence rules) for comparison with the integer
Skeycolumn during the Constant Folding phase of query compilation. This activity occurs very early in the process, well before even the simplest of query plans is considered.When constant folding is successful, the input tree contains the derived literal value (as the correct type) and optimization continues, just as if the query writer had used a constant rather than an expression.
When constant folding is unsuccessful (for example because the conversion would throw an error) the tree contains a conversion function. It would not be correct to throw an error at compilation time; an error should only occur when the query is executed, and the problematic expression is actually evaluated (if at all).
So, in your case, '201701' is constant-folded to integer 201701, but '201702A' becomes
CONVERT_IMPLICIT(int,'201702A',0).Constant folding is much more powerful and complete than the above simple example would suggest. For example:
is constant-folded to:
In SQL Server 2012 and later, even deterministic SQLCLR scalar functions can be constant-folded.