Batchmode execution in SQL 2019

Question

Post reply

Batchmode execution in SQL 2019

SqlAdmin001

Mr or Mrs. 500

Points: 540
More actions
June 30, 2025 at 7:34 am

#4615360

Is there a way to force a query to use Batchmode execution, my database is in 2019 compatibility mode.
For some reason, my queries are not using batch mode even when its scanning through millions of rows.
Is there a query hint or in anyway to force it to use Batch mode execution (SQL server 2019 enterprise edition)?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16233 More actions · Answer 1

there is a trick.

create a temp table #dummy - 1 column with the same datatype/size of one of the tables used on your main select.

create a clustered columnstore index on table

then use it as a left outer join on your main query

drop table if exists #dummy;

create table #dummy
(fieldx varchar(100)
)
;
create clustered columnstore index #cci_dummy on #dummy;


select ...
from maintable mt
left outer join #dummy dm
on dm.fieldx = mt.fieldx

SqlAdmin001 Mr or Mrs. 500 Points: 540 More actions · Answer 2

Thankyou Sir for this amazing trick, I have tested and it is working.

Does this work all the time, are there any scenarios where it might not work.

frederico_fonseca SSCoach Points: 16233 More actions · Answer 3

never seen it not work - but batch mode NOT always the best option.

Always test before and after with real production data volumes.

abdalah.mehdoini Ten Centuries Points: 1026 More actions · Answer 4

It is not possible to explicitly force Batch mode once your database is in SQL Server 2019 mode. SQL Server automatically selects the appropriate execution plan based on the number of rows and the query characteristics, either using Batch mode or Rowstore mode. In summary, while there is no explicit option like FORCE_BATCH_MODE, you can structure your queries and indexes in a way that encourages SQL Server to use Batch mode.