SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Tuesday, May 09, 2006

SQL 2005 Server Boolean value

SQL Apprentice Question
I am trying to implement a boolean field in my SQL 2005 database. I've read
some newsgroups saying that there is no boolean type but instead a bit typeis
used which can be 0 or 1.


Does this mean on my .NET side i shouldn't use the boolean type but another
data type which I set to 0 or 1, maybe a byte?


I've also noticed people using char(1) and using Y/N or T/F.


Can anyone give me advice on which is the best method?

Celko Answers

>> am trying to implement a boolean field in my SQL 2005 database. I've read some newsgroups saying that there is no boolean type but instead a bit type is used which can be 0 or 1. <<


That is a kludge. SQL does not have a BOOLEAM data type on purpose and
BIT is a proprietary Numeric data type.


>> Does this mean on my .NET side i shouldn't use the boolean type but another data type which I set to 0 or 1, maybe a byte? <<


But did you want to use 1, 0 , or -1 on the NET, depending on whether
you are using VB or C#. There is no agreement about the bit level
mapping to BOOLEANs.


>> I've also noticed people using char(1) and using Y/N or T/F.<<


This is the best of a bad design situation. What you will find is that
you wanted a status code of some kind and that you will keep adding
values to it -- a questionaire with "yes" and "no" then needs to add
"Not Applicable", "Not Answered" and so forth.

No comments: