Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bit by bit


Bit by bit

Author
Message
Muhammad Haseeb Farhan
Muhammad Haseeb Farhan
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 224
This should worth of 100 points :-)
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3056 Visits: 2716
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 think it is because the BIT type is supposed to represent True (1) or False (0) rather than integers.

Dave
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
Toreador (1/28/2010)
"if a number larger than one is used, it is converted to one."

I thought the actual explanation was that anything other than 0 (false) is treated as true and hence is stored as 1. So a number larger than 1 becomes 1, but so does any number less than 1, apart from 0.


I agree. Any numeric value other than 0. It will also apply to -1.3434. It does not have to be larger than 0.
Bob JH Cullen
Bob JH Cullen
SSC Eights!
SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)

Group: General Forum Members
Points: 938 Visits: 786
Dave62 (1/28/2010)

I think it is because the BIT type is supposed to represent True (1) or False (0) rather than integers.

Dave


Ah! So a BOOLEAN type, then. That's different, and if so should also require that the language provides proper TRUE and FALSE keywords, rather than hijacking numeric values like 0, 1, pi or whatever.
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 8064
Bob Cullen-434885 (1/28/2010)

Ah! So a BOOLEAN type, then. That's different, and if so should also require that the language provides proper TRUE and FALSE keywords, rather than hijacking numeric values like 0, 1, pi or whatever.


True and False are also accepted.

DECLARE @bit BIT
SET @bit = 'false'
PRINT @bit
SET @bit = 'true'
PRINT @bit
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3056 Visits: 2716
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
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
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
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3056 Visits: 2716
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
David Data
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 810
[sorry about the double post]
David Data
David Data
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 810
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search