Few more numbers (500K rows), queries in the same order as before.

Take note of the high CPU cost of the LEAD function!

😎

Heap, no index,

[font="Courier New"]

(100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 9611 ms, elapsed time = 3544 ms.

(100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1340 ms, elapsed time = 1526 ms.

Warning: Null value is eliminated by an aggregate or other SET operation.

(100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1483 ms, elapsed time = 1528 ms.

Warning: Null value is eliminated by an aggregate or other SET operation.[/font]

Clustered index, no POC

[font="Courier New"](100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 3203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 10202 ms, elapsed time = 3662 ms.

(100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 3203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1248 ms, elapsed time = 1367 ms.

Warning: Null value is eliminated by an aggregate or other SET operation.

(100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 3203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1529 ms, elapsed time = 1533 ms.

Warning: Null value is eliminated by an aggregate or other SET operation.[/font]