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?