yes/no

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?

Jared
Tue Aug 15 17:51:02 CDT 2006

Y = Yes
N = No

varchar(1)

Doing in this way will allow for expansion of types and keeps data looking
like english

"John" <Johna@nospam.nospam> wrote in message
news:%2377IRNKwGHA.1956@TK2MSFTNGP02.phx.gbl...
>
> 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?
>
>

Kevin
Tue Aug 15 17:59:42 CDT 2006

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?


"John" <Johna@nospam.nospam> wrote in message
news:%2377IRNKwGHA.1956@TK2MSFTNGP02.phx.gbl...
>
> 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?
>
>

v-lliu
Tue Aug 15 21:23:29 CDT 2006

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.

John
Wed Aug 16 05:46:44 CDT 2006

sounds good.
thanks


"Linda Liu [MSFT]" <v-lliu@online.microsoft.com> wrote in message
news:jdBiJsNwGHA.5696@TK2MSFTNGXA01.phx.gbl...
> 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.
>

Phil
Wed Aug 16 08:07:18 CDT 2006

On Wed, 16 Aug 2006 06:46:44 -0400, "John" <Johna@nospam.nospam>
wrote:

>sounds good.
>thanks
>
>
>"Linda Liu [MSFT]" <v-lliu@online.microsoft.com> wrote in message
>news:jdBiJsNwGHA.5696@TK2MSFTNGXA01.phx.gbl...
>> 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.

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.

--
Phil

Sericinus
Wed Aug 16 09:23:57 CDT 2006

Kevin Spencer wrote:

> Use a Bit field. It is a bit in size, and holds either 1 or 0.

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.

Kevin
Wed Aug 16 09:39:27 CDT 2006

> 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.

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?


"Sericinus hunter" <serhunt@flash.net> wrote in message
news:%23NTwf%23TwGHA.5056@TK2MSFTNGP06.phx.gbl...
> Kevin Spencer wrote:
>
>> Use a Bit field. It is a bit in size, and holds either 1 or 0.
>
> 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.

Sericinus
Wed Aug 16 10:18:09 CDT 2006

Kevin Spencer wrote:
>> 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.
>
> That is just because the smallest unit worked with is a byte in size.

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.

I am not sure I follow. Looks like truism. Can you please rephrase?

Kevin
Wed Aug 16 12:37:44 CDT 2006

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

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?


"Sericinus hunter" <serhunt@flash.net> wrote in message
news:uC%23ixcUwGHA.3364@TK2MSFTNGP02.phx.gbl...
> Kevin Spencer wrote:
>>> 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.
>>
>> That is just because the smallest unit worked with is a byte in size.
>
> 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.
>
> I am not sure I follow. Looks like truism. Can you please rephrase?

Kevin
Wed Aug 16 12:47:58 CDT 2006

> 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?


"Sericinus hunter" <serhunt@flash.net> wrote in message
news:uC%23ixcUwGHA.3364@TK2MSFTNGP02.phx.gbl...
> Kevin Spencer wrote:
>>> 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.
>>
>> That is just because the smallest unit worked with is a byte in size.
>
> 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.
>
> I am not sure I follow. Looks like truism. Can you please rephrase?

Sericinus
Wed Aug 16 13:17:17 CDT 2006

Kevin Spencer wrote:
>> 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.

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.

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.

Kevin
Wed Aug 16 14:14:01 CDT 2006

> 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.

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?


"Sericinus hunter" <serhunt@flash.net> wrote in message
news:%2346i4AWwGHA.4460@TK2MSFTNGP04.phx.gbl...
> Kevin Spencer wrote:
>>> 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.
>
> 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.
>
> 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.

Sericinus
Wed Aug 16 14:22:39 CDT 2006

Kevin Spencer wrote:
>> 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.
>
> 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.

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.