Boolean vs. Byte data types

[Guest]

I have 4 columns in a sql database which contain the value 0 or 1. From a
speed/storage standpoint, does it make any difference whether I treat these
columns as bit or tinyint. Don't both take a byte for storage and
transmittal.

By the same token, in net programming, is a system.boolean any more efficent
in storage size and transmittal rate than a system.byte.

Fred Herring

 

[Patrice]

IMO always use the appropriate datatype. This is really the kind of thing
that won't give any noticeable performance. Try first to have a good db
model and to build the appropriate indexes. It will do much more for speed
than these kind of (uggly if you ask me) things that should never be done
anyway...

Patrice

 

[Greg Low [MVP]]

Hi Patrice,

I'll differ here. Our internal standard is we use tinyint. A bit needs to be
extracted from a byte. Plus, we see the following advantages:

1. You can't index a bit. I know lots of the MS materials say "why would you
want an index on something that only has 2 values" but they always assume an
even distribution of values (eg male vs female). If you have a column (like
"finalized") where less than 1% have a certain value and you regularly need
to find them, you'll appreciate having an index
2. Some development tools use 1 for true, others use -1. Tinyint lets us
store values from either. We always just treat 0 = false, anything else is
true. All our user controls do that too.

HTH,

 

[William \(Bill\) Vaughn]

IIRC TinyInt can be NULL but Bit cannot.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Patrice]

Was previously true. With SQL Server 2000 (perhaps 7 ?), you can now have
NULL values...

Patrice

 

[Patrice]

Our approach is still a bit different :
- 1) Surprisingly it looks to be possible in SQL Server 2000. I though also
indexing bit columns was not possible (but this is of course not the general
case). It may be something new to SQL Server 2000 compared with the previous
releases.
- 2) Our approach is rather to use a boolean value. We don't care if the
underlying value is 0, 1 or -1. For us this is just true or false (or
eventually null which we use rarely).

Overall I'm not a "do that or die" person. Your points are IMO more valid
than the only point of performances (which is unlikely to be that visible if
any). You may want to check though if point 1 is still valid.

Patrice

 

[Cowboy \(Gregory A. Beamer\)]

from the standpoint of SQL Server, the bit will take up less space. It also
inherently converts to a Boolean with the .NET Boolean structure, which is a
very fast conversion. Overall, I would aim this direction, except in a few
situations. For example:

1. You are unsure if the comparison will always be boolean. If there is ever
a possibility of the yes/no adding a third choice, do not use a bit.

2. You need to index on the column now represented as a bit.

I am sure there are other very good reasons to move away from the bit
datatype, as well.

NOTE: If you are not running SQL 2000, bit fields cannot be NULL, forcing an
absolutely straight forward yes/no. SQL 2000 has opened this up so bits are
nullable.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************