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.|||
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.
pr
-pr
-t
r
1. A proprietor.2. A group of proprietors.3. Ownership; proprietorship.4. A proprietary medicine.5. One granted ownership of a proprietary colony.
rius, both from Late Latin, of a property owner, from Latinpropriets, ownership. Seeproperty.e·tari·ly —ADVERBNo, 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.
No comments:
Post a Comment