Azure SQL Database - Standard Edition (S3 Service Tier)
Why does Batch Mode only come into effect when using Aggregate Functions?
DROP TABLE IF EXISTS dbo.TransCS
CREATE TABLE dbo.TransCS (
Col1 INT
,Col2 AS Col1*2
)
CREATE CLUSTERED COLUMNSTORE INDEX CS_TransCS on dbo.TransCS;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
AS n FROM L5)
INSERT INTO dbo.TransCS (Col1)
SELECT TOP (1000000) n FROM Nums ORDER BY n;
Insert 1 Million rows
Row Processing
SELECT * FROM dbo.TransCS
Batch Processing
SELECT Col1, Col2, SUM(Col2)OVER ()
FROM dbo.TransCS
Is there a way to utilise the performance benefits without using Agg functions? Documentation on this is thin.