On Youtube I saw a demo of “Brian Dill DuckDB 101” that was impressive, you can install DuckDB from here (An analytical SQL database management system – DuckDB), I am Dutch, it was developped at the CWI (DuckDB: a high-performance analytical database management system) .
“DuckDB is developed by the Database Architectures group of the CWI. It is designed to be fast, reliable and easy to use. DuckDB provides a rich SQL dialect, with support far beyond basic SQL. Also it supports arbitrary and nested correlated subqueries, window functions, collations, complex types (arrays, structs), and more.”
You can postition it, to give you an idea where it stands.
I am a big fan of SQL Server Columnstore indexes, lets compare them and learn something after loading the Backblaze main drivedays dataset of 559.474.515 rows to be precise. (Henk van der Valk, it was raining sunday).
Loaded the same dataset in SQL Server 2025.
Ran Query 1 and checked the execution plan:
SELECT YEAR(date) AS "year", COUNT(*) AS N
FROM backblaze.dbo.drive_days_cs
GROUP BY YEAR(date)
ORDER BY 1;
Ran Query 2 and checked the execution plan:
SELECT YEAR(date) AS "year", SUM(N) AS N
FROM (SELECT date, COUNT_BIG(*) AS N
FROM backblaze.dbo.drive_days_cs
GROUP BY date) d
GROUP BY YEAR(date)
ORDER BY 1;The actual execution plans show exactly where the difference comes from. The key is how many rows leave the columnstore scan.
Direct translation, GROUP BY YEAR(date): 4.7 seconds
| Plan step | Rows out | Time |
| Columnstore scan (all 535 row groups) | 559,474,515 | 0.08 s |
| Compute Scalar: YEAR(date) per row | 559,474,515 | 3.8 s |
| Hash Match (group by year) | 11 | 0.9 s |
The scan itself is fast. But because you group on an expression, YEAR(date), SQL Server has to send every row out of the scan, calculate the year for each of the 559 million rows, and only then group them. Most of the time goes into that per-row calculation.
Faster version, grouped by date first: 0.058 seconds
| Plan step | Rows out | Time |
| Columnstore scan, with 559,441,830 rows counted inside the scan | 32,685 | 0.05 s |
| Hash Match (group by date) | 4,014 | 0.01 s |
| Compute Scalar: YEAR(date) | 4,014 | 0 s |
| Hash Match (group by year) | 11 | 0 s |
-- 2) per-operator counters from the last actual plan
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
last_plan AS (
-- most recent execution of a statement on drive_days_cs that groups by date (excluding this DMV query itself)
SELECT TOP (1) qps.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qps
WHERE st.text LIKE N'%dbo.drive[_]days[_]cs%GROUP BY date%'
AND st.text NOT LIKE N'%dm[_]exec[_]%'
ORDER BY qs.last_execution_time DESC
),
counters AS (
SELECT
op.value('@NodeId', 'int') AS node_id,
op.value('@PhysicalOp', 'nvarchar(60)') AS physical_op,
op.value('@LogicalOp', 'nvarchar(60)') AS logical_op,
op.exist('IndexScan[@Storage="ColumnStore"]') AS is_columnstore_scan,
t.value('@ActualExecutionMode', 'nvarchar(10)') AS mode,
t.value('@ActualRows', 'bigint') AS actual_rows
FROM last_plan
CROSS APPLY query_plan.nodes('//RelOp') r(op)
CROSS APPLY op.nodes('RunTimeInformation/RunTimeCountersPerThread') c(t)
),
per_operator AS (
SELECT node_id, physical_op, logical_op, MAX(mode) AS mode,
CAST(MAX(CAST(is_columnstore_scan AS int)) AS bit) AS is_columnstore_scan,
SUM(actual_rows) AS rows_out_of_operator
FROM counters
GROUP BY node_id, physical_op, logical_op
),
table_rows AS (
SELECT SUM(total_rows - deleted_rows) AS rows_in_columnstore
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.drive_days_cs')
AND state_desc = 'COMPRESSED'
)
SELECT p.node_id, p.physical_op, p.logical_op, p.mode,
p.rows_out_of_operator,
CASE WHEN p.is_columnstore_scan = 1 THEN tr.rows_in_columnstore - p.rows_out_of_operator END
AS rows_counted_inside_scan
FROM per_operator p
CROSS JOIN table_rows tr
ORDER BY p.node_id DESC;
In the set are 4.014 unique dates, so the YEAR function is executed 4.014 times.
RULE: you want fast queries, think, think how can I minimize the total executions of a function.
Grouping on a plain column lets SQL Server use aggregate pushdown: the COUNT happens inside the columnstore scan. That’s the plan’s ActualLocallyAggregatedRows counter: 559,441,830 rows were counted there and never left the scan. In practice this means:
- Each row group stores date as compressed values with a dictionary. SQL Server can count “how many times each date appears” straight from that compressed data, without building rows.
- Only 32,685 partial counts leave the scan. The next step combines them into 4,014 dates, one per day with data.
- YEAR(date) then runs on 4,014 rows instead of 559 million, which costs essentially nothing.
Why is DuckDB fast with the direct version? DuckDB processes data in vectors and computes year(date) very cheaply for a whole block of values at once. SQL Server’s batch mode does work in blocks too, but a function in GROUP BY stops aggregate pushdown, so the rows still have to leave the scan.
Rule of thumb for columnstore in SQL Server: group on the bare column first in an inner query, then apply functions like YEAR(), MONTH() or CAST() in the outer query. This pays off when the column has relatively few distinct values, like date here (4,014 values in 559 million rows).
Is this nice or very nice!The Ring on the left side DuckDB on the right side we have SQL Server Columnstore..
Some additional infromation:
The Backblaze data trap: two Excel-saved files with CR line endings that BULK INSERT loaded as 0 rows without an error, while DuckDB loaded them fine. That’s useful for any DBA who bulk-loads CSVs.
DuckDB’s time: the same query takes 0.17–0.24 s, so readers can see that DuckDB gets there with the direct version.
Size: columnstore 1.47 GB, DuckDB 5.72 GB, rowstore heap 27.71 GB.