Bit vs. Varchar?

Saw a friend of mine using a varchar field like a bit field (only storing 1
character). I told him that it would be better to change that to a bit
field, and he said, Why? What is the difference between Varchar(1) and Bit?
Thanks,
DrewDrew wrote:
> Saw a friend of mine using a varchar field like a bit field (only
> storing 1 character). I told him that it would be better to change
> that to a bit field, and he said, Why? What is the difference
> between Varchar(1) and Bit?
> Thanks,
> Drew
There is no ANSI standard for a bit column. It's a SQL Server invention.
Bit columns only take up 1 bit of storage (giving you the ability to
store as many as 8 bit columns in a single byte). I personally don't
mind them, but there are arguments for using a char(1) with a check
contraints for Y/N, 0/1, etc.
I would not use a varchar because of the added overhead. Use a char(1)
or a bit.
David Gugick
Imceda Software
www.imceda.com|||A bit can store only 0 or 1.
If storing just a single character and it will never be null best to
use char(1) instead of varchar(1). The reason is less data space is
used. Same reason applies to bit.
Tim S|||Correction bit can also store NULL.|||I prefer to use a TINYINT for this. It only takes up one byte just like a
bit or char(1) but an hold up to 255 values even if you only want to use 0
or 1. It's cleaner than a bit and more versatile.
Andrew J. Kelly SQL MVP
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
> Saw a friend of mine using a varchar field like a bit field (only storing
> 1 character). I told him that it would be better to change that to a bit
> field, and he said, Why? What is the difference between Varchar(1) and
> Bit?
> Thanks,
> Drew
>|||Thanks for everyone's reply.
Thanks,
Drew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OKaeVLGPFHA.2348@.tk2msftngp13.phx.gbl...
>I prefer to use a TINYINT for this. It only takes up one byte just like a
>bit or char(1) but an hold up to 255 values even if you only want to use 0
>or 1. It's cleaner than a bit and more versatile.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
>|||>> Saw a friend of mine using a varchar field [sic] like a bit field
[sic] (only storing 1
character). I told him that it would be better to change that to a bit
field [sic] , and he said, Why? What is the difference between
Varchar(1) and Bit? <<
Rows are not records; columns are not fields and BIT is not a Standard
SQL datatype. in fact, it makes no sense in a high level language.
That is what assembly language programmers use in the hardware and not
what SQL programmers have in an abstract darta model. VARCHAR(1) also
makes no sense; use CHAR(1) instead.
What a good SQL porgrammers does is design encoding schemes. We do not
write code which depends on flags. And it is hard to design encoding
schemes.|||>> Correction bit can also store NULL. <<
No, bit can be only one or zero; look it up in any computer science
book. On the other hand BIT, the proprietary datatype in SQL Server,
can store 1, 0 or NULL. Kinda screws up portability, don't it?|||>> I prefer to use a TINYINT for this. <<
Another proprietary datatype, but at least it has room for more codes
and can port to INTEGER.|||I think I have my answer. The spec simply calls for smallint
to be smaller than int. I guess I still don't see why they
didn't add a further designation that stated that tinyint
was smaller than smallint. It's already the case that
without knowing the given implementation's bounds on int and
smallint, you can't logically decide when to use one over
the other.
Thomas
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:utGU6aHPFHA.2132@.TK2MSFTNGP14.phx.gbl...
> That's curious. What is the logic behind having a 2-byte
> integer (smallint) and a 4-byte integer (integer) but not
> a 1-byte integer (SQL's tinyint)?
> Thomas
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1112987344.909087.50090@.g14g2000cwa.googlegroups.com...
>