August 6, 2001 at 3:05 pm
Since SQL Server 2000 does not have Boolean as datatype, is there a way for me to define boolean datatype in SQL Server 2000? Thanks in advance.
August 6, 2001 at 9:54 pm
Hi
I personally wouldnt bother even trying, and if it was available I wouldnt use it. "Boolean" is DBMS specific, and migrating between systems will be problematic for you. I typically use char(1) or even smallint (0 or 1) to do the same thing. Perhaps needs a little more storage than "boolean" (however the dbms would implement it as far as storage is concerned)...but the space required if negligible. Add a check constraint on the column also to ensure proper set of values and make a decision now as to the "nullability" of the column.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 7, 2001 at 4:47 am
Take a look at the bit column. SQL represents false as 0 and true as 1 (where VB uses -1 for true).
Chris has some fair points about booleans. One common reason is that even with a boolean you'll commonly need to have a 3rd "never set" value, our friend the null! As I understand how SQL stores bits you can get 8 of them into the same space as a char. Checkboxes in VB are 3 valued, even if they seem like booleans.
If you're sure you'll just need a bit, use them - I do!
Andy
August 7, 2001 at 7:01 am
A neat thing that SQL Server does is also allows you to insert any non-zero value (and non-null) and have it represented as 1. So if you're used to -1, you can insert that and still have SQL Server recognize it as a 1. However, when you go to retrieve, it will be stored as a 1.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
August 9, 2001 at 5:21 pm
Regarding bit columns:
Bit columns can, but won't necessarily save you space. Although SQL Server can fit 8 bit columns into a single byte, my understanding is that if you have less than 8 columns, SQL Server will still reserve a whole byte. What is more, you won't be able to use all of that byte, so it becomes wasted space. In addition, bit columns cannot have indexes on them.
For these reasons, I would tend to suggest that if you're not going to have a full 8-based number of bit columns (e.g. 8 bit columns, 16 bit columns, 32 bit columns, etc.), I would suggest using some sort of integer column instead. For example, if you only have one column that you want to store boolean data in, then I would make it a TINYINT column and convert your boolean values to 0, 1, or whatever else you want. Personally, I'd use bitmasking to mask off a single bit in the column for each particular boolean value I'd like to store in it. This adds a lot of flexibility and expandability as well. This is also similar to what Microsoft uses in its own system tables. A purist might point out that this violates the first normal form; oh well.
If you decide in the future that you want to store additional boolean information, you can just expand the values that are allowed in the column, using bitmasking techniques, as I suggested, to specify that each bit in the column stands for one particular boolean value. Besides the space savings, the integer-type column will be indexable.
Matthew Burr
Edited by - mdburr on 08/09/2001 5:23:46 PM
August 14, 2001 at 3:05 pm
If users are going to update data in your table with Access, and you go with a bit field, it is important to set a default value on the field and not allow nulls. In our scenerio, the user was trying to update a field in the table (with Access97 against SQL 2000). The update failed, with a "another user has updated the data you are trying to update" error. Running a trace on the server showed what was actually being executed; (simplified) sp_executesql 'update table set field1 = 'value' from table where field1 = 'oldvalue' and field2 = 'x' and bitfieldZ = 0x0'
BitfieldZ was causing the problem. Access ASSUMED it was zero because it wasn't true. It was actually null. Since 0x0 (0) <> NULL the row was never found and the update failed.
If anyone knows if this limitation is in Access97 or the ODBC driver involved, please let me know.
Thanks,
John
August 14, 2001 at 5:25 pm
Use a tinyint, no nulls.
If you allow nulls, then you have a 3 values field, not 2.
tinyint is the best choice for the reasons outlined by mdburr above.
Steve Jones
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply