Don't Understand Error Message (Database)

[Jonathan Wood]

Okay, so we're in similar boats there. It just seemed strange to me that it
would seem strange to you to use a small integer to store one of two
possible values.

 

[Jonathan Wood]

How much memory do you think a bit field takes up in a database? Unless many
bit fields are stored contiguosly, packed into bytes, each bit field would
still take up one byte of storage each. So why is it so important to use a
bit field rather than a byte field?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Hmm... I'm not sure how long you've been programming but it doesn't seem
at all odd to me. At any rate, it's simply used for a couple of
calculations and on a report. There will be no queries based on it.

if the values are 0 and 1 then you should use bit
0 = false
1 = true

entred as you would a int

INSERT INTO dataTypes (aBit)Values(1)

 

[Mark Rae [MVP]]

Okay, so we're in similar boats there. It just seemed strange to me that
it would seem strange to you to use a small integer to store one of two
possible values.

Because it's totally the wrong data type for a person's gender! There are no
possible circumstances where the entity of gender (i.e. male / female) can
be considered to be a *numeric* entity unless, perhaps, you are interested
in the number of Y chromosomes...

A smallint is used for *numeric* values ranging from -32,768 to 32,767 and
takes up two bytes - this means that you're using up twice as much storage
as you need to...

It's a basic design issue, really - of course the way you have designed your
schema will work, but it's still wrong...

Similarly, SQL Server has a datetime and smalldatetime datatypes which are
for storing date values. Now, of course, dates *could* be stored in a
varchar or char field, but that would be equally poor design...

Anyway, this is drifting a bit off-topic...

 

[Jonathan Wood]

Mark,

Because it's totally the wrong data type for a person's gender! There are
no possible circumstances where the entity of gender (i.e. male / female)
can be considered to be a *numeric* entity unless, perhaps, you are
interested in the number of Y chromosomes...

Or get a request to add support for an "unspecified" state.

A smallint is used for *numeric* values ranging from -32,768 to 32,767 and
takes up two bytes - this means that you're using up twice as much storage
as you need to...

I already posted that I changed them all to bytes.

It's a basic design issue, really - of course the way you have designed
your schema will work, but it's still wrong...

Similarly, SQL Server has a datetime and smalldatetime datatypes which are
for storing date values. Now, of course, dates *could* be stored in a
varchar or char field, but that would be equally poor design...

Well, when I consider the following:

1. As near as I can tell, a byte uses the same amount of database storage
that a bit uses.

2. I'm covered if another option was ever requested.

3. More importantly, it simplifies storing the value since I'm getting the
value as an integer (from a CheckBoxList control). I know many will dismiss
this but since I already ran into trouble with errors due to it being a
smallint rather than an int and no one that replied to my original post was
able to correctly identify the reason and I had to resolve it by blinding
trying different things, it's an issue to me.

I'm sticking with a byte and my opinion that it is not wrong or poor design.

You mileage may vary.

 

[ThatsIT.net.au]

Unless the field is used in any query criteria, as bit fields can't be
indexed...

How do you mean?

I just made a query against a bit field

 

[ThatsIT.net.au]

How much memory do you think a bit field takes up in a database?

1 bit uses 1 byte

8 bits uses 1 byte


Bit vs. Char(1) - One bit will take up 1 byte, 8 bits will still take up 1
byte, and a char(1) takes 1 byte. It could be argued that using a char(1) to
store Y/N is more readable, but it is far more efficient to use a bit column
if the table contains more than 1 bit column. You'll also eliminate the
possibility of someone trying to put an A/B/C/etc. in the char(1) field,
leading to incorrect data/functionality. Generally, it is considered better
practice to use the bit data type. Even if the table only contains a 1 bit
column, it will allow you to add additional bit columns in the future. There
will be no need to modify the data type/data on the existing char(1) column
in order to take advantage of the optimal data storage configuration.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1284636,00.html


Unless many

 

[ThatsIT.net.au]

Mark,


Or get a request to add support for an "unspecified" state.

why not change them to bits?

I mean lets be honest, I doubt using a int is going to rob you of all your
memory and cause a problem, but if you are going to change to a byte you may
as well change to a bit

 

[Mark Rae [MVP]]

How do you mean?

The bit datatype is not suitable for indexing, so should not be used for for
fields which are participate in query criteria i.e. WHERE clauses...
http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html

 

[Juan T. Llibre]

re:
!> How do you mean?
!> I just made a query against a bit field

He said "not indexable"...not "not queryable".
You might run into query efficiency problems if the field isn't indexed.

Unless the field is used in any query criteria, as bit fields can't be indexed...

How do you mean?

I just made a query against a bit field

 

[ThatsIT.net.au]

The bit datatype is not suitable for indexing, so should not be used for
for fields which are participate in query criteria i.e. WHERE clauses...
http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html

My understanding is you can still query it, but not index it. And as long as
the table is indexed on at least one column other then the bit performance
will be fine.

 

[ThatsIT.net.au]

re:
!> How do you mean?
!> I just made a query against a bit field

He said "not indexable"...not "not queryable".
You might run into query efficiency problems if the field isn't indexed.

Actualy he said
"Unless the field is used in any query criteria"

but your point is noted.

 

[Jonathan Wood]

ThatsIT.net.au,

why not change them to bits?

I mean lets be honest, I doubt using a int is going to rob you of all your
memory and cause a problem, but if you are going to change to a byte you
may as well change to a bit

For the reasons I gave in the post you're replying to. And, for me, a better
question would be "why change them to bits?"

It's working. It's simple. It requires no conversions. And I see absolutely
no downside to the approach I'm taking. You confirmed in your other post
what I suspected: that a byte uses exactly the same amount of space in the
database as a bit does. I'm open to hear about issues anyone still may have
about this, but they need to be specific about what is gained as I don't see
anything to be gained by changing this.

 

[ThatsIT.net.au]

ThatsIT.net.au,


For the reasons I gave in the post you're replying to. And, for me, a
better question would be "why change them to bits?"

Why change to byte?

It's working. It's simple. It requires no conversions. And I see
absolutely no downside to the approach I'm taking. You confirmed in your
other post what I suspected: that a byte uses exactly the same amount of
space in the database as a bit does. I'm open to hear about issues anyone
still may have about this, but they need to be specific about what is
gained as I don't see anything to be gained by changing this.

No that not correct, a bit uses a bit, 1/8th of a byte. if you have another
Boolean record to keep your ahead. In your case that may not be so, but I
would say that most of the time one would.

As it said the explanation I posted it is still better to use a bit as you
may need to add another bit field later

so knowing that, i would say Why not change to bit?

 

[Mark Rae [MVP]]

My understanding is you can still query it, but not index it.

That's correct - which is what I said...

And as long as the table is indexed on at least one column other then the
bit performance will be fine.

??? That's simply not the way databases work... E.g.

UserID int
FirstName varchar(30)
Surname varchar(30)
Gender bit

If you inded the Surname column, that will not help one bit when you execute
a query like: SELECT * FROM UserTable WHERE Gender = 0

How could it...?

 

[Jonathan Wood]

ThatsIT.net.au,

Why change to byte?

Because that is the smallest integer possible. And it also happens to be the
smallest field size as well.

No that not correct, a bit uses a bit, 1/8th of a byte. if you have
another Boolean record to keep your ahead. In your case that may not be
so, but I would say that most of the time one would.

It would be 1 bit field. That's 8 bits. It's not the same as 1/8th of a
byte.

As it said the explanation I posted it is still better to use a bit as you
may need to add another bit field later

In fact, I could have up to 8 with the same storage. Big deal. With an
integer value stored in a byte, I could have up to 256 different values.

I already explained why using an integer was more straight forward. If you
got what I said, then you know the reason. If you didn't, then we're
probably just wasting time.

 

[Andrew Morton]

Not sure I understand the question. As it is, Male is the first
option so Male == 0 and Female == 1. I tried creating enums for this
(and the other options) but stupid C# has problems with if (i ==
sexesMale). Oh well, I can write code that simply tests the value for
being 0 or 1.

There's an ISO standard for the values:
http://en.wikipedia.org/wiki/ISO_5218

Andrew

 

[Jonathan Wood]

Heh, well there ya go. The ISO standard for storing the sex would appear to
require a small integer. Oh well, I'm not looking to follow any ISO
standard, but looks like my approach is not unprecedented.

 

[Mark Rae [MVP]]

Heh, well there ya go.

I stand corrected.

The ISO standard for storing the sex would appear to require a small
integer.

A byte, not a small integer.

 

[Jonathan Wood]

Mark,

A byte, not a small integer.

After programming for 30 years, I'm sure you are well aware that a byte is
an integer type. In the context of 32 and 64-bit integers, I would consider
an 8-bit type to be a small one. Don't you agree?

 

[George Ter-Saakov]

To go further, everything is an integer in computer world. Even strings are
.

in SQL
int is 32 bit,
smallint is 16 bit,
byte is 8 bit.

PS: Did not know that there are standards for sex .

George.