What is the equivalent to Yes/No data type in SQL?

Question

What is the equivalent to Yes/No data type in SQL?

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 1

I hit the same dilemma converting MS Access tables with boolean columns. Since SQL Server has no boolean data type, my first inclination was to use bit.

But being a fan of "Iverson Notation" (Where 1 = true, and 0 = false), I found I was unable to report a count of rows matching some true/false condition. For example, to report how many with "isActive" = true, I typically use sum(isActive), but that wont work with bits. So I took the cowards way out and just converted them to int. I'm sure TINYINT would have been more conservative, but decided, at any rate, that the bit datatype was to be avoided.

ed-1075072 Mr or Mrs. 500 Points: 596 More actions · Answer 2

I realize that now that I am dealing with the bit data type that I can't write a query such as: SELECT COUNT(Active) AS Expr1

FROM Employees

where active=true

I must change the active = 1 in order for this query to work. I can work/adjust to that, time will tell me if I made the right choice for the data type I selected.

Thanks again

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 3

ed (6/12/2009)
I realize that now that I am dealing with the bit data type that I can't write a query such as: SELECT COUNT(Active) AS Expr1 FROM Employees
where active=true
I must change the active = 1 in order for this query to work. I can work/adjust to that, time will tell me if I made the right choice for the data type I selected.
Thanks again

Or, with Active as an int or tinyint, you dont need the where clause:

Select SUM(Active) as Expr1

FROM Employees

kennethrinderhagen SSC Enthusiast Points: 112 More actions · Answer 4

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 fields

pietlinden SSC Guru Points: 63503 More actions · Answer 5

pietlinden

SSC Guru

Points: 63503

February 27, 2015 at 10:51 pm

#1780855

also note that this thread is almost six years old?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

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 fields

Heh... 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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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 fields
Heh... 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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

True enough, Tom, and apologies for being entirely too brief on my previous response. You can limit the BIT "field" (cough, cough, gasp! 🙂 )by adding a NOT NULL constraint, as you say. I just wanted to make sure that newbies that might read this understand that doesn't change the fact that there is no Yes/No only data type in SQL Server nor will it change the fact that, nothing else provided, the BIT data type can be NULL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

This works with TRUE or FALSE >

DECLARE @mybit bit;

SET @mybit = 'true';

SELECT @mybit

Though yes/no , on/off do not. Just want to add to this post.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

CELKO (3/3/2015)
We do no use assembly language bit flags in RDBMS. Please read:
https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/
You do not think in predicates yet and probably do not understand 3VL.

While what you say is absolutely true, there are so many nice ways to say this and so many ways to make this useful and you managed to avoid all of them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

CELKO (3/3/2015)
While what you say is absolutely true, there are so many nice ways to say this and so many ways to make this useful and you managed to avoid all of them.
I gave link to a detailed article; that seems useful to me. I do not think it is useful to lie to people or not cry out a warning when they are in dangerous territory. :satisfied:

Understood but you always seem to put the really hard edge on things. Folks might be more enticed to read your articles if you led them into it. That particular article is a good one, indeed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

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 fields
Heh... 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.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Jeff Moden (3/3/2015)
CELKO (3/3/2015)
While what you say is absolutely true, there are so many nice ways to say this and so many ways to make this useful and you managed to avoid all of them.
I gave link to a detailed article; that seems useful to me. I do not think it is useful to lie to people or not cry out a warning when they are in dangerous territory. :satisfied:
Understood but you always seem to put the really hard edge on things. Folks might be more enticed to read your articles if you led them into it. That particular article is a good one, indeed.

No it's not a good one, it's a piss-poor one.

On top of that, the article is pretty well irrelevant to this topic. It points out that bitmask columns are not a good thing when treated as multiple separate bit values , but a single bit is not a bitmask. (Besides not all uses of bitmasks violate atomicity, but who cares, bitmasks are irrelevant to this discussion anyway?).

It's full of nonsense. It claims that a BIT is always nullable, although I have no difficulty whatsoever in declaring a bit column to be not nullable. Then it claims that the idea of a NULL bit is hard to understand, and suggests perhaps in indicates a quantum state - but what a NULL means is very clear, a NULL bit is simply a bit whose value is not known, just as a NULL character is a character whose value is not known and a null date is a date whose value is not known. It puts up an example of BIT being unsuitable for a particular purpose (Hanebutt's problem), and pretends that there's somethig special about SQL that makes unsuitable there, but in fact I am not aware of any computer language in which it would be sensible to use BIT as described there. It claims that there is something wrong with the expression "CAST ('1' AS BIT)& CAST (NULL AS BIT)" evaluating to NULL instead of generating an error message, as if this was something special arising from the use of BIT, but "CAST ('abc' as varchar(6)) + CAST (NULL as varchar(4))" also evaluates to NULL rather than generatig an error message, as do similar expressions using other types, so there is nothing special about BIT here, this is a general property of operators (other than special cases like COALESCE, which still don't generate error messages because it sees a NULL) acting on NULL.

I can't find a single thing in the article which tells me why, when I have a domain for which I won't need nulls that contains exactly two values and the binary operators on the domain are the 16 boolean operators, I can't use a BIT NOT NULL column to represent an attribute which falls in that domain. Or if I have that domain but need to allow NULLs, why I can't use BIT with the usual extension of the 16 operators so that if either or both argument is/are null so is the result. So the article doesn't in any way at all support the argument that it is claimed to support.

It's a horribly flawed article, and utterly irrelevant in context. The only redeeming feature is that it argues against using bitmask types as non-atomic columns, and even that is not particularly good because it doesn't support the article's own thesis, its opening statement "The BIT data type is an awkward fit for a SQL database".

Tom

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

I did like the article basically promoting thinking things out at design time to see if the state you want to represent with a BIT data type is truly a yes/no type (no gray areas or future adjustments with more BIT fields).

----------------------------------------------------

Sigerson Hall of Fame Points: 3697 More actions · Answer 15

MMartin1 (3/2/2015)
This works with TRUE or FALSE >
DECLARE @mybit bit;
SET @mybit = 'true';
SELECT @mybit
Though yes/no , on/off do not. Just want to add to this post.

This is interesting. 'True' or 'true' equates to a BIT field value of 1. Similarly, 'False' or 'false' gives a Bit value of 0. But this trick does NOT work with just 'T' or 'F'

Sigerson

"No pressure, no diamonds." - Thomas Carlyle