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.
Almost true; as you yourself ponted out, it's easy to use a check constraint on another datatype to restrict it to however many values you want. And a column can be declared NOT NULL, so that only two values happen. So a colum specification something like
MyBool tinyint NOT NULL CHECK(MyBool < 2)
gives you a non-nullable bool value where you can count true values using SUM if you accept the space penalty, while
MyBool bit NOT NULL
gives you a space efficient version where you have to use COUNT with a where clause to count true values.
Of course there may be good reasons for allowing NULLs in a boolean column, and that is obviously easy to by leavig NOT NULL out of either of the above column specifications, and the code needed to count true values doesn't depend on whether NULLs are allowed or not, but normally I include NOT NULL in a column spec unless I have good reasons to leave it out (i.e. unless I think rows will have to be inserted while the value of the column concerned is unknown, or the table's primary key is declared within the CREATE TABLE statement and includes the column).
Tom