November 21, 2003 at 9:54 am
I have read in several places the advice that, for a number of reasons, one shouldn't use the bit datatype. I'm happy to accept that advice but I'd like to know for what purpose the datatype was created and if there are any situations where its use can be recommended.
November 21, 2003 at 10:50 am
Maybe I'm not using it wisely then. I use it when I need to have a 'flag' variable, that I am sure will never need to determine two different values.
That being said, I just recently ripped out 2 bit fields in a table i was setting up, turned it into a 'XXXTypeID' and gave it a descriptor table with a foreign key.
Not sure about current versions, but when i was just beginning in SQL I tried to index on a bit field and it wouldn't let me. After i looked into it, i decided the reason they allowed such a thing to be the case was that a bit field would have such low selectivity that it's a bad index candidate.
Guess I don't really have any answers, but your question has me curious also. Could you give me a reference to see some of those docs you are referring to? Be interested to know if i am habitually doing something silly.
November 21, 2003 at 11:15 am
Well I can't give any exact reasons, but.
DASD is cheap today, years ago,(many for some of us) saving a byte on a million or 2 rows was big $$$$. So I figure it's just a carry over from past things.
That said, I am strongly against their use. Much more difficult to view in a select, no way the optimizer can handle them except table scans etc.
So unless you have a bunch, and will NEVER need anything except T/F, or are dealing with Billions of rows why bother.
How about the developer here that talked about the space he saved using one bit versus a byte. Sounded really good until I told him it still took one byte !!! And the query was have all kinds of problems !!
KlK, MCSE
KlK
November 21, 2003 at 11:28 am
Looking at my DB im working on atm, I see two bit fields; XPOUsable and BoxUsable ( whether a shipping carrier allows APO/XPO and box addresses). Not really sure what to do with those, since I'm not sure if they can be turned into one type reference. Very low number of records in that table, so i definitely don't have a space usage excuse for using them.
What does the acronym DASD stand for? <newb question im sure>
November 24, 2003 at 12:54 am
Good question, why it was created!
Bit information represents more of of a state than of an attribute. So quite something logical.
I wouldn't use a bit column, although when upgrading from Access the Upsizing Wizard creates such. I tend to use the tinyint datatype. It leaves you with more flexibility for expansion.
Consider a column 'Membership'
Could be Deactivated=0, Activated=1 and what about Paused... or Suspended...
No chance with a bit column to model this information.
There might be some reason in space saving.
A bit column uses 1 bit (how suprising) leaving the remaining 7 bit in that byte unused, if you have only one bit column in that table as all other columns require byte boundaries. Now if you have more than one bit columns, I'm pretty sure SQL Server will 'pack' those into the same byte, although I don't know for certain.
Another reason might be compatibility and portability as the bit data type as proprietary and might not transfer properly to other platforms.
Frank
Sorry, forget the conclusion. Was mentally ahead...
Edited by - Frank kalis on 11/24/2003 12:58:09 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 24, 2003 at 6:23 am
I had started using them one time in a table and then I realized I couldn't do a 'group by' on a bit field... so I changed them to tinyint. There may have been a workaround.
John
November 24, 2003 at 6:31 am
Yes there is a workaround
CREATE TABLE MyTable
(
a INT,
b BIT
)
INSERT INTO MyTable VALUES (1, 1)
INSERT INTO MyTable VALUES (2, 1)
INSERT INTO MyTable VALUES (3, 1)
SELECT
MAX(a), CAST(b AS INT)
FROM
MyTable
GROUP BY CAST(b AS INT)
DROP TABLE MyTable
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 25, 2003 at 6:52 am
What DASD is isn't a newbie question, it's a young one question. It stands for Direct Access Storage Device, and in non-jargon, means disk (as opposed to tape, for example).
November 25, 2003 at 7:06 am
I don't like bit values because I can never remember what the values stand for (or is it I can never remember what the values stand for because I never use bit values?).
Anyway, I've seen too many examples where there is no consistency with bit values - sometimes it is Yes/No other times it is No/Yes. I've even seen a True/False turn into a Yes/No half way thorugh a program.
I prefer char(1) fields to tinyint as with tinyint you still need a look up table to tell you what value = 1 means. A single character Y means yes and N means no.
Jeremy
Edited by - Jeremy Kemp on 11/25/2003 07:06:47 AM
November 25, 2003 at 7:33 am
Bit of course is a simple handling of question. The reason most folks say not to use bit is the fact tinyint is the same size but provides room for growth of conditions. However my personnal opinion is to use bit whenever I am doing 3 answer logic, true/false/unknown, yes/no/unknown and that is it. It doesn't do anything major in difference except keep me from writing a constraint for the column where asnwers are in (0,1,null). Others will suggest NULL is no efficient and thus you should represent unknown with a default value (1 = true, 2 = false, 0 = unknown) but I don't like building logic for simplistic tasks when they can be covered much simpler. It all boils down to your personal preference. But as for those who say it is not as efficient as tinyint only win the case when there is potential for the number of answers to increase, everything else is perfect for a 2 or 3 answer possiblity and should not be overlooked.
November 25, 2003 at 11:10 am
There's another reason for bit - legacy code. Even if tinyint will work, it's good to leave as bit to keep a reminder that the code won't handle other numbers.
In programming I use boolean variables often enough, mostly for clarity, but there seems to be less use in dbs. It does limit expandability and can add complexity with data type conversions. It does tell everyone there can be only 2 (with null, 3) choices. However, a constraint or User defined type will do the same.
As Antares suggests, whether to use bit is a personal preference.
Data: Easy to spill, hard to clean up!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply