SQL SERVER 2000 DataType

  • Hi Good Guys, 😛

    I am back and need your help. Please help me.

    SQL SERVER 2000 Table.

    What is the meaning of DataType BIT and also what's the meaning of value 0 and 1.

    I feel so helpless about it. Please Help me :w00t:

    Thank you.

    Cheers,

    Lennie

  • A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/11/2010)


    A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.

    😎

    I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and

    Does value 1 (one) means NO or FALSE ?

    These are my question that I asked in my earlier posting. 😛

  • Lennie (9/11/2010)


    GilaMonster (9/11/2010)


    A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.

    😎

    I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and

    Does value 1 (one) means NO or FALSE ?

    Up to you.

    Intrinsically in SQL 0 means just 0 and 1 means just 1. It's a bit column, not a boolean. You can assign true/false meanings to the values as you like. It's not MS-Access where the boolean column has such meanings (0 true and 1 false)

    Typically, if people do assign such logical meanings to the 1 and 0, 1 is true and 0 false, but it's totally up to you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It's not up to me. I am just a logical users.

    It's up to the creator of the SQL SERVER and it's datatype defination.

    I am just a logically progammer and user. 😛

  • There's no need to shout at me, and doing so is not going to get you the response that you apparently want! Yelling at someone because you don't like or don't agree with what they're saying is rude at best.

    Now, as I said before, there is NO special meaning to 0 or 1 in the bit type. There is no intrinsic definition of the two in the definition of the data type. If you want to declare a column called Colour as BIT and have 1 mean 'red' and 0 mean 'blue', that is totally up to you.

    The only meanings of 0 and 1 in the BIT data type are the mathematical ones.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • A helper is supposed to assist by providing sample coding instead of saying it's UP TO YOU and try not to help by providing intense information of proposed suggestion on using CASE without defining what sort of category it belongs to

  • I don't understand what you want me to give you.

    When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.

    By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Lennie (9/11/2010)


    I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE? These are my question that I asked in my earlier posting.

    The bit data type is an integer type which can hold the values 0, 1, or NULL. See http://msdn.microsoft.com/en-us/library/aa225961(v=SQL.80).aspx

    The bit data type is 'special' in a number of ways:

    1. If you assign any non-zero number to it, it is implicitly converted to 1.

    2. Storage is optimized: up to eight bit columns can be stored in a single byte of physical storage.

    3. You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0. (2005 and later only)

    By convention, a bit value of 1 is associated with a boolean 'true', and 0 with a boolean 'false'. As Gail points out, this isn't enforced by SQL Server (except by implication as noted in the string assignments above), but it is extremely common.

    Most people would see a bit value of 1 as implying 'true', 'on', 'yes' or some other equally 'positive' interpretation. A bit value of 0 is seen as implying 'false', 'off', or 'no'.

    Fundamentally, though, the definition of bit is that it holds integer values - which may be either 0 or 1.

    Here's an example to illustrate some of the behaviours of the bit data type:

    CREATE TABLE #Temp (b BIT NOT NULL);

    INSERT #Temp VALUES (1);

    --INSERT #Temp VALUES ('True'); -- 2005 onward

    INSERT #Temp VALUES (456);

    -- Success

    SELECT *

    FROM #Temp

    WHERE b = 1;

    -- No rows

    -- 456 is interpreted as an integer

    -- Integer has a higher precedence than BIT

    -- So the BIT column value is converted to an integer

    -- to make the comparison

    SELECT *

    FROM #Temp

    WHERE b = 456;

    -- Success

    SELECT *

    FROM #Temp

    WHERE b = CONVERT(BIT, 456);

    DROP TABLE #Temp;

    I'm afraid I don't have 2000 installed any more, so I have only been able to test the above on SQL Server 2005 and 2008.

    Paul

    edit: updated to reflect Ron's confirmation of behaviour in SQL 2000

  • Paul White NZ (9/12/2010)


    3. You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0.

    I believe this feature was introduced in SQL Server 2005.

    Nicely explained Paul and Gail:-)

  • Very nicely explained by Gail and Paul.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Lennie (9/12/2010)


    A helper is supposed to assist by ...

    Odd to instruct others on how you must respond in a forum, when you shout in the forums as you did with the following post:

    Lennie (9/12/2010)


    It's not up to me. I am just a logical users.

    It's up to the creator of the SQL SERVER and it's datatype defination.

    I am just a logically progammer and user. 😛

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • steve-893342 (9/12/2010)


    I believe this feature was introduced in SQL Server 2005.

    Thanks Steve - I couldn't remember whether it worked in SQL Server 2000 or not. If anyone has a 2000 install around to test this I'd be grateful - I have a nagging doubt that it worked in 2000 but was undocumented. I may well be wrong about that.

  • Paul White - tried this in Query Analyzer - yup still have SQL 2000 on an older desktop - Ran the following 3 times .. same result each time

    CREATE TABLE #Temp (b BIT NOT NULL);

    INSERT #Temp VALUES (1);

    INSERT #Temp VALUES ('True');

    INSERT #Temp VALUES (456);

    Result:

    (1 row(s) affected)

    Server: Msg 245, Level 16, State 1, Line 2

    Syntax error converting the varchar value 'True' to a column of data type bit.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks very much Ron! 🙂

Viewing 15 posts - 1 through 15 (of 42 total)

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