Bit by bit

Question

Post reply

Bit by bit

Viewing 15 posts - 16 through 29 (of 29 total)

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

Dave62 SSCertifiable Points: 7459 More actions · Answer 1

A little more information about the BIT type. The bit type has 3 states: NULL, 0, and 1.

When first declared and unset a NULL will be returned.

Only a 0 or the string 'False' will return a 0.

Any positive or negative number or the string 'True' will return a 1.

Any string other than 'True' or 'False' will return an error.

The following script illustrates the points above:

Set NOCOUNT On

Declare @blnFlag bit

Select @blnFlag As [Unset Returns]

Set @blnFlag = 0

Select @blnFlag As [Set to 0 Returns]

Set @blnFlag = 144

Select @blnFlag As [Set to 144 Returns]

Set @blnFlag = -144

Select @blnFlag As [Set to -144 Returns]

Set @blnFlag = 'False'

Select @blnFlag As [Set to 'False']

Set @blnFlag = 'True'

Select @blnFlag As [Set to 'True']

Set @blnFlag = 'Dave'

Select @blnFlag

The following resuts are returned:

Unset Returns

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

NULL

Set to 0 Returns

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

0

Set to 144 Returns

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

1

Set to -144 Returns

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

1

Set to 'False'

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

0

Set to 'True'

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

1

Msg 245, Level 16, State 1, Line 22

Conversion failed when converting the varchar value 'Dave' to data type bit.

Dave

john.arnott SSChampion Points: 11882 More actions · Answer 2

Dave62 (1/28/2010)
A little more information about the BIT type. The bit type has 3 states: NULL, 0, and 1. . . .

I don't think that's quite right. It's not that a BIT datatype has three states -- wouldn't that be a "trit"? -- but that ANY data element may be designated as nullable or not, and nullable is the default case for columns and the only choice for variables. If a column is nullable, there's an internal switch maintained to designate the field as null.

Dave62 SSCertifiable Points: 7459 More actions · Answer 3

john.arnott (1/28/2010)
...It's not that a BIT datatype has three states -- wouldn't that be a "trit"? -- but that ANY data element may be designated as nullable or not, and nullable is the default case for columns and the only choice for variables. If a column is nullable, there's an internal switch maintained to designate the field as null.

All true but the end result is 3 states because the database will return a NULL if the field is nullable, has no default setting, and has not been set. Some applications are handling all 3 states now by displaying a checkbox as checked, unchecked, or "highlighted" if the database returns 1, 0, or NULL respectively.

Dave

David Data SSCrazy Points: 2965 More actions · Answer 4

David Data

SSCrazy

Points: 2965

January 28, 2010 at 2:22 pm

#1110364

[sorry about the double post]

David Data SSCrazy Points: 2965 More actions · Answer 5

I agree with Bob. What's the point of strong typing if some types aren't?

Setting a Bit value to 500 OUGHT to generate an error. Can the resent behaviour be deprecated, and eventually changed?

Treating 500 as 'True' ought to be allowed in an explicit CONVERT, but not on a simple assignment.

bhakti-sonu Mr or Mrs. 500 Points: 556 More actions · Answer 6

I try this out with @bit = 0 it gives message "no"

if @bit = -500 ,it gives message "yes",

if @bit = -1 then also it gives "yes".

The Explanation Given for Answer : Bit constants are represented by the numbers 0 or 1, if a number larger than one is used, it is converted to one.

but what about the values less than 1, still they consider as 1

Please explain me more about the negative values used for bit datataype.

alpeshgediya Old Hand Points: 322 More actions · Answer 7

for bit datatype, any value which in non-zero is converted to bit 1.

try this you will get better understading,

declare @bit bit

set @bit = -0

select @bit

declare @bit bit

set @bit = +0

select @bit

as mathematical there is no concept of +/- 0 ..is for only understading purpose.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 8

why is'nt BIT treated like other datatypes?

"Keep Trying"

Marry Krissmess SSC Veteran Points: 262 More actions · Answer 9

Bob Cullen-434885 (1/28/2010)
OK. I am in the 17% who got it wrong - I replied "Error". And now I know. But will somebody kindly explain why BIT is treated any differently to TINYINT, SMALLINT, INT, etc. It is, after all, only a type that has a range of valid values. If the others report an overflow error when I try to assign values outside their permitted range, why not BIT also?

I am exactly feeling the same. I think a database should not be so forgiving. In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an error.

Toreador SSChampion Points: 11365 More actions · Answer 10

Marry Krissmess (2/5/2010)
In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an error.

Why? The Bit datatype is a boolean, so why is it so surprising that it is treated as such?

Paul White SSC Guru Points: 150467 More actions · Answer 11

Marry Krissmess (2/5/2010)
I am exactly feeling the same. I think a database should not be so forgiving. In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an error.

Try it with the strings 'true' and 'false' - that will really upset you!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

ChiragNS (1/31/2010)
why is'nt BIT treated like other datatypes?

It is. The value supplied is implicitly converted to a BIT, according to the conversion rules.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

shini2007 Valued Member Points: 55 More actions · Answer 13

I think":

" Converting to bit promotes any nonzero value to 1" not greater than zero only Because

DECLARE @bit BIT

SET @bit = -14

IF @bit = 1PRINT 'yes'ELSE PRINT 'no'

is also give yes.

Paul White SSC Guru Points: 150467 More actions · Answer 14

shini2007 (9/16/2010)
I think":" Converting to bit promotes any nonzero value to 1" not greater than zero only Because
DECLARE @bit BIT
SET @bit = -14
IF @bit = 1PRINT 'yes'ELSE PRINT 'no'
is also give yes.

Non-zero does not mean greater than zero, it means not zero.

-14 is not zero, so it is converted to 1, as I said.

Does that make sense?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kapil_kk SSC-Insane Points: 21316 More actions · Answer 15

Dave62 (1/28/2010)
A little more information about the BIT type. The bit type has 3 states: NULL, 0, and 1.
When first declared and unset a NULL will be returned.
Only a 0 or the string 'False' will return a 0.
Any positive or negative number or the string 'True' will return a 1.
Any string other than 'True' or 'False' will return an error.
The following script illustrates the points above:
Set NOCOUNT On
Declare @blnFlag bit
Select @blnFlag As [Unset Returns]
Set @blnFlag = 0
Select @blnFlag As [Set to 0 Returns]
Set @blnFlag = 144
Select @blnFlag As [Set to 144 Returns]
Set @blnFlag = -144
Select @blnFlag As [Set to -144 Returns]
Set @blnFlag = 'False'
Select @blnFlag As [Set to 'False']
Set @blnFlag = 'True'
Select @blnFlag As [Set to 'True']
Set @blnFlag = 'Dave'
Select @blnFlag
The following resuts are returned:
Unset Returns
-------------
NULL
Set to 0 Returns
----------------
0
Set to 144 Returns
------------------
1
Set to -144 Returns
-------------------
1
Set to 'False'
--------------
0
Set to 'True'
------------
1
Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the varchar value 'Dave' to data type bit.
Dave

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/