Bit or character data type?

SQL2K
SP4
Im creating a DB that has several Bit coulmns. The data is coming from a
mainframe. When one stored proc is run, the result set needs to have a "y",
an "n", or a " '' " (space) as thats the way current systems work. Another
proc needs to return "Eligible" or "Not eligible". I had originally thought
of using a Bit fields, but now I'm wondering why. It seems to me that if I
use Bit, I then need to convert it to display one of the things just listed.
Whereas if I use character coulmns and add check constraints to the to make
sure only certain values can be added, that conversion doesnt need to occur
when the data is being retrieved from the DB. Id have to imagine Im not the
first one to think of this as no app will probably ever return a 1 or a 0, so
Im wondering what others thoughts on the matter are?A couple of things to consider, a bit column by itself takes up one byte.
If eight of them are together in a table you can save some space. To put
"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
has a special behavior that says any integer other than 0 is 1. In the end
I like to format results in the reporting tool so I would generally choose a
tinyint with a check constraint. But in the case of 'y', 'n', or space
char(1) would be pretty effective.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
> SQL2K
> SP4
> Im creating a DB that has several Bit coulmns. The data is coming from a
> mainframe. When one stored proc is run, the result set needs to have a
> "y",
> an "n", or a " '' " (space) as thats the way current systems work. Another
> proc needs to return "Eligible" or "Not eligible". I had originally
> thought
> of using a Bit fields, but now I'm wondering why. It seems to me that if I
> use Bit, I then need to convert it to display one of the things just
> listed.
> Whereas if I use character coulmns and add check constraints to the to
> make
> sure only certain values can be added, that conversion doesnt need to
> occur
> when the data is being retrieved from the DB. Id have to imagine Im not
> the
> first one to think of this as no app will probably ever return a 1 or a 0,
> so
> Im wondering what others thoughts on the matter are?|||Chris,
Eight or less bit columns willl occupy only one byte, therefore the bit type
should be the prefered choice if you have multiple such columns in the same
table. From SQL Server books on-line:
"Microsoft® SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
Leo
"Danny" <nomailbox@.nowhere.com> wrote in message
news:Uiqfg.13100$lN5.1551@.trnddc04...
>A couple of things to consider, a bit column by itself takes up one byte.
>If eight of them are together in a table you can save some space. To put
>"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
>has a special behavior that says any integer other than 0 is 1. In the end
>I like to format results in the reporting tool so I would generally choose
>a tinyint with a check constraint. But in the case of 'y', 'n', or space
>char(1) would be pretty effective.
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
>> SQL2K
>> SP4
>> Im creating a DB that has several Bit coulmns. The data is coming from a
>> mainframe. When one stored proc is run, the result set needs to have a
>> "y",
>> an "n", or a " '' " (space) as thats the way current systems work.
>> Another
>> proc needs to return "Eligible" or "Not eligible". I had originally
>> thought
>> of using a Bit fields, but now I'm wondering why. It seems to me that if
>> I
>> use Bit, I then need to convert it to display one of the things just
>> listed.
>> Whereas if I use character coulmns and add check constraints to the to
>> make
>> sure only certain values can be added, that conversion doesnt need to
>> occur
>> when the data is being retrieved from the DB. Id have to imagine Im not
>> the
>> first one to think of this as no app will probably ever return a 1 or a
>> 0, so
>> Im wondering what others thoughts on the matter are?
>