Boolean ?

There is no Boolean datatype.
Most people use a BIT (0 or 1).
There are arguments for and against using CHAR(1) values with a check
constraint ('Y', 'N'), ('T', 'F').
You're better off with either of those than a (var)char(5) allowing 'TRUE'
and 'FALSE' as strings.
Oh, and you will never be able to shorthand WHERE column = 1 for WHERE
column, or WHERE column = 0 for WHERE NOT column. Even though the datatype
you choose (along with the cnstraints if necessary) may seem like a valid
boolean expression to you, SQL Server does not consider them as such.
A
"Gabriel" <nospam@.nospam.com> wrote in message
news:uw2bPk2$GHA.3380@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Is there a way to define a boolean with SQL Server 2005 (Express)
> Thanks,
>
>> Btw, did you see the new error message 2005 gives you when you try to say[vbcol=seagreen]
Hehe, apparently there is some context that expects boolean type in SQL
Server !!!
Anith
|||>>> Btw, did you see the new error message 2005 gives you when you try to
> Hehe, apparently there is some context that expects boolean type in SQL
> Server !!!
Well, there are boolean expressions (not types), sort of, e.g.
WHERE EXISTS
(
...
)
|||Sorry, just wanted to clarify that I do get the joke. It's been a long day
here today.
|||>> It's been a long day here today.
Long Fridays, tell me about it :-)
Anith

bitwise AND with Char column

based on BOL, bitwise AND operator operates only on integer data type
category (int, smallint, tinyint). but i have a char(4) column which
stores integers (disregard the db design issue) which produces the
same/correct results after using bitwise AND with another integer.
does this mean SQL server does the conversion itself?SQL Server will convert a value stored in the CHAR datatype into an INTEGER
because an INTEGER has a high
Data Type Precedence
"joeycalisay" <joeycalisay@.gmail.com> wrote in message
news:1131435508.269803.57870@.o13g2000cwo.googlegroups.com...
> based on BOL, bitwise AND operator operates only on integer data type
> category (int, smallint, tinyint). but i have a char(4) column which
> stores integers (disregard the db design issue) which produces the
> same/correct results after using bitwise AND with another integer.
> does this mean SQL server does the conversion itself?
>|||thanks a lot sir!|||Implicit conversion is at work here and that's potentially bad news.
Implicit conversions are to be avoided where possible because of their
potential to break unexpectedly under schema or code changes or even as
a result of service packs or version upgrades. Obviously a poor design
is at fault in your example but if you must accommodate it then I would
use CASE expressions:
CASE WHEN col1 = '1' AND col2 = '1' THEN 1 ELSE 0 END
David Portas
SQL Server MVP
--|||thanks for the input sir david particularly on the point of it being
breakable. poor design - yeah, i hated it when i saw it, wish i had
the time to revise it but they are at code freeze

Bit or Char?

Sql Server uses bit field for boolean while Access uses Y/N. What are the pros and cons of either way? Thanks.

Generally, unless you have a number of bits in a tables, either would be OK. I often use Y/N and haven't had issues yet. Have a look at the following.

http://www.codecomments.com/archive352-2005-11-712750.html

|||There is a major difference when you are using BIT because it is Boolean in SQL Server and from SQL Server 7.0 and above BIT is three valued in SQL Server because Boolean in ANSI SQL is three valued True/False/NULL(unknown). So you cannot compare the use of BIT to Char in SQL Server 7.0 and above. Hope this helps.|||

If the value you are storing is truly a true/false or yes/no, then use a bit.

Bits are smaller (1/8th of a byte, and when combined with multiple bits in a row, this can add up significantly).

They are also faster (Although probably insignificantly faster).

Using a bit also restricts the values to 0 or 1 (or null if nullable).

Let's assume that you have a table in which you are storing whether an item has been sold. The column is named IsSold, and you want to mark a particular item as being sold. What do you put in the IsSold column?

What if it is defined as a bit? The answer is 1.

What if it is defined as char? Hmm...do I put '1'? Maybe 'Y', or 'T', oh... Maybe 'y' or 't'? What happens if I put in 'Q'?

|||For boolean-type values I always prefer to use Bit datatype. One advantage is that you can directly convert an SqlServer bit datatype to a .NET Boolean using Convert.ToBoolean()

eg.

Boolean isApproved = Convert.ToBoolean(myReader["IsApproved"]);

Obviously you have to make sure your column has a not null constraint for this to be guaranteed to work!|||

Motley wrote:

If the value you are storing is truly a true/false or yes/no, then use a bit.

Bits are smaller (1/8th of a byte, and when combined with multiple bits in a row, this can add up significantly).

They are also faster (Although probably insignificantly faster).

Using a bit also restricts the values to 0 or 1 (or null if nullable).

Let's assume that you have a table in which you are storing whether an item has been sold. The column is named IsSold, and you want to mark a particular item as being sold. What do you put in the IsSold column?

What if it is defined as a bit? The answer is 1.

What if it is defined as char? Hmm...do I put '1'? Maybe 'Y', or 'T', oh... Maybe 'y' or 't'? What happens if I put in 'Q'?

No BIT is Microsoft proprietry implementation of ANSI SQL Boolean but it is defined as INT which means it is four bytes. This is because Boolean in ANSI SQL is three valued so ANSI SQL will not define a data type for it. Hope this helps.

|||

If i use a Y/N field, I set a rule that these are the only options.

Another valid point is that Y or N might more closely relate to the business data definition, and be more welcome by business data users who might have to work closely with the database.

Most databases are set up (the default) as case-independant, so case is not usually an issue - but if have already set Y or N in the rule - it's covered.

There's no right or wrong here, its all a matter of taste

|||If Binary Sort is enabled SQL Server must be case sensitive. Hope this helps.|||

Caddre wrote:

Motley wrote:

If the value you are storing is truly a true/false or yes/no, then use a bit.

Bits are smaller (1/8th of a byte, and when combined with multiple bits in a row, this can add up significantly).

They are also faster (Although probably insignificantly faster).

Using a bit also restricts the values to 0 or 1 (or null if nullable).

Let's assume that you have a table in which you are storing whether an item has been sold. The column is named IsSold, and you want to mark a particular item as being sold. What do you put in the IsSold column?

What if it is defined as a bit? The answer is 1.

What if it is defined as char? Hmm...do I put '1'? Maybe 'Y', or 'T', oh... Maybe 'y' or 't'? What happens if I put in 'Q'?

No BIT is Microsoft proprietry implementation of ANSI SQL Boolean but it is defined as INT which means it is four bytes. This is because Boolean in ANSI SQL is three valued so ANSI SQL will not define a data type for it. Hope this helps.

proprietary

SYLLABICATION:pro·pri·e·tar·yPRONUNCIATION: pr-pr-trADJECTIVE:1. Of, relating to, or suggestive of a proprietor or to proprietors as a group:had proprietary rights; behaved with a proprietary air in his friend's house.2. Exclusively owned; private:a proprietary hospital.3. Owned by a private individual or corporation under a trademark or patent:a proprietary drug.NOUN:Inflected forms: pl.pro·pri·e·tar·ies
1. A proprietor.2. A group of proprietors.3. Ownership; proprietorship.4. A proprietary medicine.5. One granted ownership of a proprietary colony.ETYMOLOGY:From Middle Englishproprietarie, owner of property, from Old Frenchproprietaire and from Medieval Latinproprietrius, both from Late Latin, of a property owner, from Latinpropriets, ownership. Seeproperty.OTHER FORMS:pro·prie·tari·ly —ADVERB
BIT is not proprietary to Microsoft. Bit is a common computer concept that was developed long before Microsoft was ever formed. Microsoft does not exclusively own the concept of a bit, nor do they own any patent or trademark on the bit. In addition, you are incorrect, the bit type is not stored as an int. It is stored as 1/8th of a byte (or char) within the database. But because the smallest increment of storage that the database can allocate is one byte (char), a whole byte is used. However, if your table has more than one such field in it, it will combine the fields into a single bit, such that a table with 8 columns of the bit type will take only 1 byte (char) of storage per row. In addition starting with MS SQL 7.0 or later, the bit type may be three valued as well. In prior versions it could only be 1 or 0.

|||You can also tie a bit column to a checkbox's checked property directly, which you can't do with a char defined as Y/N only. Some controls (grids) may do this automatically for you as well (Displaying a checkbox instead of a textbox with a Y/N in it).|||SQL Server BIT is proprietry implementation of ANSI SQL Boolean, BIT maybe a general computer concept but in SQL Server it is proprietry.|||

No, it is not.

ISO/ANSI defined the bit datatype in SQL-99. See ISO/IEC document 9075-1:1999.

The majority of the large RDMSs support the bit datatype. Off the top of my head, that'd be MSSQL, MySQL, Postgress, Mimer, Frontbase, and SyBase.

Maybe you think the word "proprietry" (not spelled correctly by the way) means cool or useful, in that case I'd agree.

|||

EDIT

Try the link below for answer from Joe Celko about BIT and Joe Celko is a member of ANSI SQL and wrote to date the best ANSI SQL book. And BIT in SQL Server is not a data type because it is defined as INT. There is no data type defined for Boolean in ANSI SQL because it is three valued and I have ANSI SQL 2003.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=213719#bm214191

Sorry permissions required to access the link, the text below was posted by Joe Celko.

(There is no Boolean data type in SQL because of the 3VL. BIT is a horrible, proprietary, non-relational thing that you should avoid. Instead define a location type code that can hold all of the various kinds of locations you will need. Make 0= "fiscal" in the code.

CREATE TABLE ClientLocations
(location_id
REFERENCES Locations(location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
client_id INTEGER NOT NULL
REFERENCES Clients(client_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
location_type INTEGER DEFAULT 0 NOT NULL
CHECK (location_type >= 0),
PRIMARY KEY (location_id, client_id, location_type));)

|||

I think I'm done here. There really isn't anything else I can do but keep repeating what I have already stated.

BIT is not defined as INT - As proven, and provable by anyone else here with MS SQL 2000. BIT fields do not take 4 bytes of data storage as a INT would. They act like I said.

The BIT datatype is defined in SQL-99, however you are correct, it appears to be removed in SQL-2003.

The BIT datatype is not proprietary, and your (or others) claiming it is, simply does not make it so.

Just on a side note, Joe Celko may have written a good book, I don't know, I didn't read it, however, it is apparent that he tends to make claims that are based on his opinion and state them as fact when they are not facts. A bit is not a "horrible, proprietary, non-relational thing". That statement is absurd. A bit is more relational, and more normalized than a CHAR field with checks. And I've already disproven the proprietary part, so all we are left with is the truth. Joe thinks bits are horrible. Great, that's his OPINION, one that most people well versed in database design, structure, and standardization would disagree with. However, it is just an opinion.

|||

If you are through with the thread you will not post again and your opinion compared to one of the most brilliant minds in ANSI SQL?

( A bit is more relational, and more normalized than a CHAR field with checks)

(So you cannot compare the use of BIT to Char in SQL Server 7.0 and above)

I covered that a long time ago.