Boolean in sqlserver 2000 and sqlserver 2005

Hi ,
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
--
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>

Boolean in sqlserver 2000 and sqlserver 2005

Hi ,
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>

Bit Field vs. Integer with Nulls

If I have a field where 99.9% of the time the answer is going to be "No", would I be better, in terms of disk space, using:

A bit field

OR

A tiny int field, with a NULL for the 99.9% that are "NO", and a 1 for those that are "YES".

I'm using SQL Server 7.0.

My application developer has no preference.

Thanks.How much data are we talking about?

Also What is the field for?

Do you have to display the data?

I'd go with...

CREATE TABLE myTable99(Col1 char(3) DEFAULT('NO') CHECK(Col1 IN ('NO','YES')))|||Roughly 8000 records a month. The field is used to identify those records that are going to be non-billable, which is a very small percentage of the time.

The field itself will never appear on any report; it will determine if the particular record should appear on the standard report, or if it will be moved to a special report.|||Brett dude! You'd seriously store "No" and "Yes" in the database?

Excesive verbosity!

"Y" or "N", or 1 or 0 thank you!

I don't believe that a single bit field is going to take any less space than a smallint. I think you have to have several bit fields grouped in your table to realize any space savings.|||Yo,

Blind dude

You'd seriously prefer to perform conversions for display purposes for only 8k rows a month?

SELECT CASE WHEN Col1 = 1 THEN 'YES' WHEN Col1 = 0 THEN 'NO' END

This is like using a surrogate...no thanks

So, you're saving 16k a month....let's through a party...