A fair test of row mode vs batch mode changes only the execution mode. This post uses a query hint to force row mode at the same compatibility level, checks that both modes return the same rows, and times five runs of each on a three million row table. It also shows a query where batch mode gains nothing.

Batch mode on rowstore lets SQL Server process rows in groups, even when the table has no columnstore index. This post builds a million row table, reads the execution mode of each plan operator, and flips four switches to show when you get batch mode and when you don’t. It also answers why a plan can stay in row mode.