Links for learning more
What can use batch mode?
Learn which operators can use batch mode by SQL Server version here. (Scroll down to “Batch Mode Execution”)
Why is using a batch so much more efficient sometimes?
Hugo Kornelis gives an excellent explanation of why batch mode can make a huge performance difference in his article here. (Scroll down to “Inside the operator” for the example. He also gives terrific observations on batch size.)
Want to use batch mode without really using columnstore?
Check out my article and video here for an example query where batch mode makes a huge performance difference and example hacks.