Why does SELECT COUNT(1+'A') return 1 in SQL Server

user_6158320 0
Viewed 93

As stated in the question, I am curious why is it on SQL Server when we do:

SELECT 1+'A'

It throws an error:

Conversion failed when converting the varchar value 'A' to data type int.

But when I do:

select count(1+'A')

I get a value of 1?

Why does it not throw an error?

sql-server sql
1 Answers

Apparently, SQL Server is smart enough to know that the count of a constant expression is either the same as COUNT(*) or 0. It can figure out that this is not NULL without actually evaluating it.

There are other cases where expressions are not evaluated. For instance:

where not exists (select 1/0 from . . . )

Actually, I just learned something interesting. These fail with a conversion error:

select count(1+'A'+NULL)
select count(1+'A'+nullif(1,1))

But this doesn't:

select count(1+'A'+nullif(1,0))

Seems like strange behavior to me, too.

user_11440350
Related