yes/no

  • Thread starter Thread starter John
  • Start date Start date
J

John

If i want to add a checkbox and store the answer in my SQL Express database,
do i just ad it as an integer to the database and check the value say 0 for
no 1 for yes? or is there an easier way?
 
Y = Yes
N = No

varchar(1)

Doing in this way will allow for expansion of types and keeps data looking
like english
 
Use a Bit field. It is a bit in size, and holds either 1 or 0.

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

Orange you bland I stopped splaying bananas?
 
Hi John,

Generally speaking, we use bit type in SQL Server to represent boolean
value. The value 1 stands for True and 0 stands for False.

When we drag&drop a table with a bit field from the Server Explorer onto
the designer of a DataSet, VS IDE generates a corresponding DataTable and a
column of type System.Boolean for the bit field in the database table.

Then we could access this boolean field in the DataTable easily and needn't
convert a number to a boolean value.

Hope this helps.
If you have anything unclear, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
sounds good.
thanks
Click to expand...

The downside of using bits is that they are not ANSI standard and thus
make it more difficult to port the database in the future. That's why
I prefer to use TINYINT or SMALLINT. Also, you can't SUM/GROUP by a
BIT. SMALLINTs will allow negatives whereas TINYINTs will not.
 
Kevin said:
Use a Bit field. It is a bit in size, and holds either 1 or 0.
Click to expand...

Is it really so? Bit column is of a byte size, but it is
optimized when you have more than one bit columns (up to 8)
to still fit in one byte.
 
Is it really so? Bit column is of a byte size, but it is
optimized when you have more than one bit columns (up to 8)
to still fit in one byte.
Click to expand...

That is just because the smallest unit worked with is a byte in size.
Essentially, regardless of the storage used, the bit value is one bit in
size.

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

Orange you bland I stopped splaying bananas?
 
Kevin said:
That is just because the smallest unit worked with is a byte in size.
Click to expand...

I know. I just wanted to say that your statement may be misleading.
Essentially, regardless of the storage used, the bit value is one bit in
size.
Click to expand...

I am not sure I follow. Looks like truism. Can you please rephrase?
 
Essentially, regardless of the storage used, the bit value is one bit in
I am not sure I follow. Looks like truism. Can you please rephrase?
Click to expand...

Sure:

"Without going into unnecessary technical details, regardless of the size of
the storage that a Bit value is being stored in, each Bit value is one bit
in size."

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

Orange you bland I stopped splaying bananas?
 
I know. I just wanted to say that your statement may be misleading.

Actually, I think it's misleading to give all the technical storage details,
considering the OP's question. A Bit field is one bit in size. If you have
less than 9 rows, the total amount of storage space for all the Bits
together is one byte. If you have 9, it is 2. And so on. However, this is
not relevant to the issue of the size of a Bit field in a SQL Server
database, or why I recommended it. For all practical purposes, it is one bit
in size. Regardless, it is the smallest possible storage of data that SQL
Server, or any other database, provides.

Going into the irrelevant (to the OP's question) technical details might be
misleading, in that the listener might get the impression that 8 Bit fields
occupy 8 bytes.

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

Orange you bland I stopped splaying bananas?
 
Kevin said:
Actually, I think it's misleading to give all the technical storage details,
considering the OP's question. A Bit field is one bit in size. If you have
less than 9 rows, the total amount of storage space for all the Bits
together is one byte. If you have 9, it is 2. And so on. However, this is
not relevant to the issue of the size of a Bit field in a SQL Server
database, or why I recommended it. For all practical purposes, it is one bit
in size. Regardless, it is the smallest possible storage of data that SQL
Server, or any other database, provides.
Click to expand...

Can you give an example of such practical purpose? I mean, when we
would care about the size of an integer type other than the storage
space it occupies.
Going into the irrelevant (to the OP's question) technical details might be
misleading, in that the listener might get the impression that 8 Bit fields
occupy 8 bytes.
Click to expand...

Technical details you are talking about is just a couple of lines
of explanation. It is not something awfully difficult to understand.
And I still believe that this particular case does require going into
such details.
For example, from what you originally said, one can quickly assume
that he can always save storage space eight-fold choosing a bit column
over, say, char(1) column for a logical boolean value, which is not true.
 
For example, from what you originally said, one can quickly assume
that he can always save storage space eight-fold choosing a bit column
over, say, char(1) column for a logical boolean value, which is not true.
Click to expand...

A char column would require 8 bytes to store 8 records; a Bit column would
consume 1 byte for anything up to 8 records. Therefore, at 8 records, the
byte column is consuming 8 times as much storage space. At 2 records it is
consuming twice as much. At 3 it is consuming 3 times as much. At 9 records,
it is back to 4.5 times as much. At 17, it is 5.6666. At 25, it is 6.25. And
so on. It very swiftly approaches an average of 8 times as much storage
space as the number of records increases.

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

Orange you bland I stopped splaying bananas?
 
Kevin said:
A char column would require 8 bytes to store 8 records; a Bit column would
consume 1 byte for anything up to 8 records. Therefore, at 8 records, the
byte column is consuming 8 times as much storage space. At 2 records it is
consuming twice as much. At 3 it is consuming 3 times as much. At 9 records,
it is back to 4.5 times as much. At 17, it is 5.6666. At 25, it is 6.25. And
so on. It very swiftly approaches an average of 8 times as much storage
space as the number of records increases.
Click to expand...

I agree with what you say. There is no need to explain this over
and over again, as both you and me knew exactly what happens from
the very beginning. The OP's question was about adding one column
holding logical yes/no value, and I found your response to that question
potentially misleading. If you don't think it was, let it be so.
 
Back
Top