bit vs tinyint vs char(1)
-
I'm setting up a new database and am wondering if I should use bit, tinyint or char for a field that will indicate if a building has a basement. It would need to store 0,1,Null (Null if they are unsure if it has a basement). In C#, it would be nice to be able to have the IF statement look something like "IF BASEMENT" instead of "IF BASEMENT = Y".
I understand that "bit" can't be used in an index, which might be a problem (I'm not sure why this would be a problem yet, but one never knows).
Also, which data type would map better to a windows form radio button.
-
Hey,
I would use a bit, because it maps nicer over to a boolean value than any other value. For a radiobutton, I would think the same because you base it off of the checked boolean property. If you are taking radiobuttonlist, then the selectedvalue is of type char, but using bit only requires a simple ctype().
However, null always causes problems and must be checked for, which is an extra step no matter what solution you choose.
Brian
-
Bits can be in an index but they generally shouldn't be. The reason bit shouldn't be in an index is you only have 2 values (3 including null). For an index to be effective, the range of values should be larger than that and there should be a good deal of diversity in the values. According to the Microsoft SQL Server 2000 Performance Tuning Guide, the general rule is if a query accesses more than 20% of the rows for a given table, it's better to do a table scan than use an index. Given with equal distribution you're looking at 33.3% per value (I'm including the null... otherwise we're looking at 50%), that'd be a table scan. Hence the reason bits don't generally get put in indexes. It's possible, just typically not very useful.
As for space, SQL Server will automatically allocate 1 byte for the first bit. It'll fill in the rest of the byte with other bit columns as needed, up to 8. Then it'll start on the next byte if there are more bit columns. If space is a concern, take that into account (but it usually isn't). Therefore if you have more than 1 column of this sort, you will see a space savings over char(1) and tinyint, both of which will take up 1 byte per column.
K. Brian Kelley
@kbriankelley -
Space isn't an issue, and that makes sense about the index. I noticed that enterprise manager doesn't let me select a "bit" field for an index. So that leaves which is easier from the view point of someone quering the data, and more importantly, which method is easier for the programmer doing the windows forms and the coding.
-
bit is the way to go. It works just fine with windows controls (although "null" can be trickier) and other coding.
Signature is NULL
-
this is all very true, but I have run into this problem quite often with various clients, where the scope of the table or field is set in stone at the start of the project
"no, we are never going to want to store additional information"
and then is altered to "well, is it possible to store this, this and this"
Because of this, I tend to store most fields, that really should be a bit into a tiny int field, allowing 255 various alterations to the scope of that. Of couse, you could just add an additional lookup table, and an additional field following the usual Normalization rules.
Imaging you are storing whehter a property has a basement, firstly you specify whehter it has, hasnt or unknown - 3 states 0,1,NULL. But then some hotshot project manager rolls in and wants to determine from that whether, its 'Basement, Slab, Crawlspace, Multilevel, etc' Now you are going to have to add an additional table and field for the lookup - whils if it was stored a tinyint field, you could simply add new values to the original lookup table. Job Done.
But this is all assuming you dont have any space limitations or legacy systems, expecting it to be a bit field.
Just My 2Cents (but with the exchange rate, thats 0.117 GBP)
-- Alex
-
Hey,
True, if your field is not a true/false type answer, then I wouldn't recommend bit; however, if a change is being requested to change to a multi-option answer, then that is going to require an application change anyway, plus a possible lookup table add. At that point, you have to revisit the database anyway...
Brian
-
In C#, Boolean values are 0/1 for False/True
VB.NET has boolean values of 0/-1 for False/True
Just in case you wanted to know. When integrating C# and VB.NET code, the CLR automatically converts the value to the other appropriate value.
Brian
-
Thanks for all the input. I agree with the high level language argument. Now I need to decide if I should use Y,N,U (yes, no, unsure) and disallow Nulls, or use Y,N,Null.
The numeric and date fields need to allow Null if the value is unknown, but should the character fields allow Null to be consistent with the numeric and date fields, or should they use a special value to indicate Unknown or N/A? What is more correct from a database design view? Also, is it better to set up a table with the valid values of Y,N and make it relational to all the columns that use Y,N, or is it better to set up a user defined datatype that has a Y,N constraint?
This is all being done from scratch, so I have no legacy issues.
-
Hello,
I would use a primary/foreign key in my design, but that is because user defined data types are discouraged where I work. For character fields, I typically use null, but from a design point of view, I look at NULL as an absense of a value. So if you want a value of N/A, it wouldn't hurt. Although it would require some effort, as using NULL is the easier solution.
Brian
-
Joe, your point is quite applicable when handling more complex functionality like vectors.
But, sometimes there is only one question with one yes/no answer. The only way I know to explicitely enforce this in SQL is to use a non-nullable bit datatype.
As you say, though, it's not portable. But, honestly, I'd have to re-learn SQL to make my code portable. This has never been something I thought about before...
Portability is something you bring up quite a bit, in your articles and books and in this forum. Is there an article that could put me on the right track? Or is just a matter of reading the standards and sticking with them? Maybe you should write an article for SQLServerCentral!
cl
Signature is NULL
-
>>I had two advantages over you;<<
Quite a few more than that, I imagine!
Thanks for the input; I will look for these "portability guides" you speak of.I really enjoyed "SQL for Smarties"; looking forward to the new one...
Signature is NULL
-
No, haven't read it.
I've used a variation of your "nested set" model to build a navigation tree for our company website (I do webdev as well as DBdev). It worked pretty well, but was rather complex. I also handled this using a loop, and although this was simpler it wasn't set based and didn't perform well for large datasets. Recently I've explored using XML data instead of relational data for this particular problem.
The contents posted on Amazon look interesting, though; looks like you've figured out some new tricks. I may need to pick it up (as I can get my company to pay for it anyway). Although XML does have it's place, I prefer relational data any day.
cl
Signature is NULL
-
I tend to reserve the use of nulls for things that havent been asnwered at all, rather than for an unknown, as technically when a user answers unknown, she/he is actually filling in information.
IMO, Nulls should be reserved for things that havent been answered.
-- Alex
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply