This is a purely academic question, in so much that it isn't causing a problem and I'm just interested to hear any explanations for the behaviour.
Take a standard issue Itzik Ben-Gan cross-join CTE tally table:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[TallyTable]
(
@N INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH
E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 1*10^1 or 10 rows
, E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
, E4(N) AS (SELECT 1 FROM E2 a, E2 b) -- 1*10^4 or 10,000 rows
, E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows
SELECT TOP (@N) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E8
)
GO
Issue a query that will create a 1 million row number table:
SELECT
COUNT(N)
FROM
dbo.TallyTable(1000000) tt
Take a look at the parallel execution plan for this query:
Note the 'actual' row count prior to the gather streams operator is 1,004,588. After the gather streams operator the row count is the expected 1,000,000. Stranger still, the value is not consistent and will vary from run to run. The result of the COUNT is always correct.
Issue the query again, forcing non-parallel plan:
SELECT
COUNT(N)
FROM
dbo.TallyTable(1000000) tt
OPTION (MAXDOP 1)
This time all operators show the correct 'actual' row counts.
I've tried this on 2005SP3 and 2008R2 so far, same results on both. Any thoughts as to what might cause this?