Jeff Moden (2/28/2015)
kennethrinderhagen (2/27/2015)
Yes/NoA logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fieldsHeh... Six years old or not, there's no datatype for Yes/No in T-SQL. The closest you can come is the BIT datatype, which takes 1s, 0s, and NULL and has some annoyances like not being able to SUM() a BIT column directly.
Those conditions are not that hard to work around:
Make the BIT column NOT NULL.
Cast the BIT column to an INT before using SUM:
select
MyBitColumnSum1 = sum(convert(int,a.MyBitColumn)),
MyBitColumnSum2 = sum(case a.MyBitColumn when 1 then 1 else 0 end)
from
MyTable a
I prefer using the BIT datatype instead of CHAR(1) because there are so many more ways to mess up CHAR(1) with Y, N, T, F, etc. unless you constrain the column.
Although BIT is not a true logical column, many applications recognize it as one. And as a practical matter, it doesn't have that many other common uses.