Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Bit by bit Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 4:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 7, 2013 4:37 AM
Points: 256, Visits: 224
This should worth of 100 points
Post #855071
Posted Thursday, January 28, 2010 6:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 2,150, Visits: 2,152
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
Post #855133
Posted Thursday, January 28, 2010 7:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
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.
Post #855247
Posted Thursday, January 28, 2010 7:58 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:13 AM
Points: 678, Visits: 531
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.
Post #855271
Posted Thursday, January 28, 2010 8:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 1,812, Visits: 6,584
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
Post #855288
Posted Thursday, January 28, 2010 8:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 2,150, Visits: 2,152
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
Post #855289
Posted Thursday, January 28, 2010 10:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.
Post #855435
Posted Thursday, January 28, 2010 11:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 2,150, Visits: 2,152
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
Post #855526
Posted Thursday, January 28, 2010 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
[sorry about the double post]
Post #855644
Posted Thursday, January 28, 2010 2:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
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.
Post #855645
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse