Does Batch Mode only apply to Agg Functions on ColumnStore Index
Background
Back in July 2017, Niko blogged about a change in SQL Server 2017

...in SQL Server 2017 there is an internal optimisation for the Query Optimiser, that shall apply FULL optimisation to the execution plans with the Columnstore Indexes, avoiding the pitfall of executing them in the Row Execution Mode

Prior to that, you would get row-mode execution for &quot;trivial plans,&quot; even when there were columnstore objects involved.
The execution plan you shared shows that the first query has this problem, which you can confirm in the XML:
StatementOptmLevel=&quot;TRIVIAL&quot; 

Workaround
One way to work around this, get FULL optimization, and thus get a batch mode scan, is to add a subquery that won't affect your results, like this:
SELECT * FROM dbo.TransCS WHERE (SELECT 1) = 1;

The second query gets full optimization already because of the aggregate function.
Why is this happening?
The only things that come to mind for why this might happen are:

your database has a compatability level of 130 or lower (this seems unlikely because I can see in the execution plan that the 140 cardinality estimator is being used)
this is a change / regression in Azure SQL Database specifically

The &quot;build&quot; indicated in the execution plan is 15.0.1100.504, which looks like a SQL Server 2019 build number (which makes sense, since this is Azure).  I don't have a 2019 instance to test on, but the change in behavior might be there as well



You may want to report this to Microsoft on the feedback site.