Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have many queries in my application where in the having clause, I have comparison of count aggregate function with int variable. In the query plans, I can see an implicit_convert before the comparison. I want to know why this happens because as per sql server documentation, the return type of count function is int. So why should there be an implicit conversion for comparison of two int values?

Following is a part of one such query plan where @IdCount is defined as an int variable.

|--Filter(WHERE:([Expr1022]=[@IdCount]))    
 |--Compute Scalar(DEFINE:([Expr1022]=CONVERT_IMPLICIT(int,[Expr1028],0))) 
  |--Stream Aggregate(GROUP BY:([MOCK_DB].[dbo].[Scope].[ScopeID]) DEFINE:([Expr1028]=Count(*)))
share|improve this question

migrated from stackoverflow.com Feb 18 '13 at 10:44

This question came from our site for professional and enthusiast programmers.

    
Did you try COUNT(id) which means id column as your primary int field index column? –  YvesR Feb 15 '13 at 10:17
    
@meraj Your query is between two difference databases or two different collation? –  Viswanathan Iyer Feb 15 '13 at 10:20
    
@YvesR No haven't tried that..but why should it make any difference? –  meraj Feb 15 '13 at 10:24
    
@ViswanathanIyer they're in the same database and collation. –  meraj Feb 15 '13 at 10:25
4  
@C.A.McCann - Well it's open again now. Hopefully if it gets closed as off topic again the close voters will opt to migrate to the DBA site (questions about execution plans are definitely on topic there) –  Martin Smith Feb 17 '13 at 18:01

1 Answer 1

The fact that you are comparing it against an integer variable is irrelevant.

The plan for COUNT always has an CONVERT_IMPLICIT(int,[ExprNNNN],0)) where ExprNNNN is the label for the expression representing the result of the COUNT.

My assumption has always been that the code for COUNT just ends up calling the same code as COUNT_BIG and the cast is necessary to convert the bigint result of that back down to int.

In fact COUNT_BIG(*) isn't even distinguished in the query plan from COUNT(*). Both show up as Scalar Operator(Count(*)).

COUNT_BIG(nullable_column) does get distinguished in the execution plan from COUNT(nullable_column) but the latter still gets an implicit cast back down to int.

Some evidence that this is the case is below.

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
, E16(N) AS (SELECT 1 FROM E8 a, E8 b)  -- 1*10^16 or 10,000,000,000,000,000 rows
, T(N) AS (SELECT TOP (2150000000) 
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E16)
SELECT COUNT(CASE WHEN N < 2150000000 THEN 1 END)
FROM T 
OPTION (MAXDOP 1)

This takes about 7 minutes to run on my desktop and returns the following

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

Which indicates that the COUNT must have continued on after an int would have overflowed (at 2147483647) and the last row (2150000000) was processed by the COUNT operator leading to the message about NULL being returned.

By way of comparison replacing the COUNT expression with SUM(CASE WHEN N < 2150000000 THEN 1 END) returns

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

with no ANSI warning about NULL. From which I conclude the overflow happened in this case during the aggregation itself before row 2,150,000,000 was reached.

share|improve this answer
2  
If you look in the XML show plan, you should see <Aggregate AggType="COUNT_BIG" Distinct="false"> in the Stream Aggregate for all three COUNT, COUNT_BIG and SUM plans. The execution engine does have different intrinsics for some data types internally, e.g. you will occasionally see row_number_i4 in a Sequence Project returning an int instead of the normal bigint, but there is no T-SQL syntax for this. Anyway, the answer's right. –  Paul White Feb 18 '13 at 11:43
    
@PaulWhite - Thanks. I should have looked at the XML. I was looking at the ScalarOperator value shown in the SSMS properties window. –  Martin Smith Feb 18 '13 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.