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: Today @ 8:04 AM
Points: 2,012, Visits: 2,014
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: Today @ 7:45 AM
Points: 3,879, Visits: 3,622
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:41 AM
Points: 616, Visits: 481
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 @ 8:40 AM
Points: 1,715, Visits: 6,265
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: Today @ 8:04 AM
Points: 2,012, Visits: 2,014
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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: Today @ 8:04 AM
Points: 2,012, Visits: 2,014
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: Friday, July 25, 2014 8:39 AM
Points: 115, Visits: 745
[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: Friday, July 25, 2014 8:39 AM
Points: 115, Visits: 745
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