I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table) are all distribution aligned.
Why is this? Obviously I would like it to run in batch mode if possible.
Causes row mode execution:
create table mytable1 with ( distribution = hash(c1) ) as
with filterData as ( select a ,b ,c ,d ,DateStamp from [DW_reporting].[table1] where num > 0 ) select p.[DateStamp] ,p.[a1] ,p.b1 ,p.c1 ,p.d1 ,e.a ,e.b ,case when e.d is not null then 1 else 0 end as Flag from SOmeOtherTable p left join filterData e on e.a = p.a1 and e.c = p.c1 and e.DateStamp < p.DateStamp