Int Column - Blank behavior

Transact-SQL
1

I'm curious as to why SQL server interprets a "zero" (0) as a blank in SQL Server. Understandably, if you have an int column, it should not be blank. I'm not referring to nulls...I understand how a null would be handled. But if you have a physical value of zero in an int column, it's strange to me that when testing with a case expression or through the where clause, that SQL Server would interpret a zero as blank. Here's an example that I set up:

declare @status table (intvalue int)
insert into @status (intvalue)
values
(0)
,('')
,(null)

SELECT intvalue,
case when intvalue='' then 'I''m Blank' end,
case when intvalue=0 then 'I''m Zero' end,
case when intvalue is null then 'I''m Null' end
FROM @status

I'd expect that when testing the "0" it would only return a result as "0". Instead, it is interpreting 0's as blank or zero and likewise, interpreting blanks as blank or zero. (3 row(s) affected) intvalue ----------- --------- -------- -------- 0 I'm Blank I'm Zero NULL 0 I'm Blank I'm Zero NULL NULL NULL NULL I'm Null (3 row(s) affected) Is this an option setting or is there some other explanation? Thanks!
2

Implicit conversion:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

You can't really store an empty string in an int column, so SQL converts it in your insert statement.

3

G...thanks! I guess my confusion lies with the fact that it interprets a 0 as a blank. If you look at the case expressions, in the "case when intvalue='' then 'I''m blank' end" expression, it interprets the "0" as a blank on row 1 of column 2 in the SELECT.

4

that's not exactly what happens. Actually another implicit conversion takes place, this time from int to char, in the CASE expression. So the int is converted to a char, then the char is compared.

5

Aha! That makes sense...was sort of thinking along those lines, but wanted to be sure. Thanks a lot for your help!