Don't Understand Error Message (Database)

J

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

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

ThatsIT.net.au said:
Jonathan Wood said:
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)
 
M

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

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. :)
 
T

ThatsIT.net.au

Mark Rae said:
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
 
T

ThatsIT.net.au

Jonathan Wood said:
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
 
T

ThatsIT.net.au

Jonathan Wood said:
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
 
J

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.





ThatsIT.net.au said:
Mark Rae said:
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
 
T

ThatsIT.net.au

Juan T. Llibre said:
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.




 
J

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

ThatsIT.net.au

Jonathan Wood said:
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?
 
M

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

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

Andrew Morton

Jonathan said:
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
 
J

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top