Professional Web Applications Themes

Create Table and Boolean Data Type - Microsoft SQL / MS SQL Server

Byron Hopp (com) writes:  There is no boolean data type in T-SQL. Most people probably use bit, which can take the values 1 or 0. Some people prefer to have a char(1) column which only permits two values, for instance 'T'/'F' or 'Y'/'N'. Personally I don't like this, because there is more risk for confusion. It is also subject to localization issues. -- Erland Sommarskog, SQL Server MVP, se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp...

  1. #1

    Default Re: Create Table and Boolean Data Type

    Byron Hopp (com) writes: 

    There is no boolean data type in T-SQL. Most people probably use bit,
    which can take the values 1 or 0.

    Some people prefer to have a char(1) column which only permits two
    values, for instance 'T'/'F' or 'Y'/'N'. Personally I don't like this,
    because there is more risk for confusion. It is also subject to
    localization issues.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  2. #2

    Default Re: Create Table and Boolean Data Type

    >> How do you create a column with the data type of boolean <<

    You don't; BOOLEAN datatypes do not exist in SQL.

    The reasons are complex, but the easiest to explain to a newbie is that
    we have three valued logic (TRUE, FALSE, and UNKNOWN) and that all
    datatypes in must commodate a NULL. Such columns would then be an
    awkward four valued logic which is inconsistent. It does not work at a
    mathematical level.

    Using the proprietary BIT datatype means that your code will not port.
    Look at the different ways host languages use BIT values for Boolean --
    nobody agrees at that low level.

    Columns in a relational model are atributes; Booleans are states, not
    attributes. Boolean columns are usually the result of a bad data model;
    usually a file model where you are recording a state for later use, like
    we did with punch cards.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  3. #3

    Default Re: Create Table and Boolean Data Type

    Joe,

    Why can't an attribute have a domain of {true,false} (with
    unknown a possibility or not, reflected by defining the
    column as NULL or NOT NULL)? Have you never
    designed or managed a database with attributes like "Active"
    or "Completed" ? Can you explain why such an attribute
    is bad design, and what it has to do with punch cards? The
    keypunch machines I used never had TRUE and FALSE
    keys, but maybe you had fancier ones at your disposal.

    By the way, I hope you like the GUID below. I'm
    using it because groups.google.com unwisely chose
    the natural "key" (newsgroup, thread title), which is not
    unique and causes unrelated threads to be lumped
    together - the GUID will be a simple way for me to
    find my posts despite Google's bad design.

    -- Steve Kass
    -- Drew University
    -- Ref: D5F1D5DD-3B71-4CD1-B216-F12F63F5116A



    Joe Celko wrote:
     [/ref]
    >
    >You don't; BOOLEAN datatypes do not exist in SQL.
    >
    >The reasons are complex, but the easiest to explain to a newbie is that
    >we have three valued logic (TRUE, FALSE, and UNKNOWN) and that all
    >datatypes in must commodate a NULL. Such columns would then be an
    >awkward four valued logic which is inconsistent. It does not work at a
    >mathematical level.
    >
    >Using the proprietary BIT datatype means that your code will not port.
    >Look at the different ways host languages use BIT values for Boolean --
    >nobody agrees at that low level.
    >
    >Columns in a relational model are atributes; Booleans are states, not
    >attributes. Boolean columns are usually the result of a bad data model;
    >usually a file model where you are recording a state for later use, like
    >we did with punch cards.
    >
    >--CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    >constraints, Declarative Referential Integrity, datatypes, etc. in your
    >schema are.
    >
    >*** Sent via Developersdex http://www.developersdex.com ***
    >Don't just participate in USENET...get rewarded for it!
    >
    >[/ref]

    Steve Guest

Similar Threads

  1. Filter table by column data type
    By Brandon Taylor in forum Dreamweaver AppDev
    Replies: 3
    Last Post: August 9th, 05:24 AM
  2. #25734 [Opn->Bgs]: string false type-casts into boolean TRUE
    By elmicha@php.net in forum PHP Development
    Replies: 1
    Last Post: October 2nd, 08:06 PM
  3. Replies: 2
    Last Post: August 12th, 07:55 AM
  4. insert memo type data into a table
    By erdo in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 02:24 PM
  5. Table data type - thoughts?
    By Dave Slinn in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 7th, 11:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139