Booleans, SqlServer, and VB

Hi,

The three above seem to form an evil combination. ;-)

For now, I use varchar(5) in my SqlServer 2000 db and fill it with "True" or "False", which VB understands as boolean values in my .aspx/.aspx.vb pages.

Is there no way of using Bit instead in SqlServer, or even 0/1, and easily bind it in VB? How do others do it? (I've only seen other people struggling with this, and not any solution).

I can't understand why a language such as VB cannot be allowed to understand both True/False, 1/0, and Y/N!

Muchos gracias in advance,

Pettrer

I do not know what about Y/N but 0/1 in VB is a little opposite because is 0/-1 so -1 is true in VB and 1 is true in SQL Bit field.

Thanks

|||

Thanks, but the problem is that VB only writes "True" and "False" to the database. Of course i might be able to change it in the INSERT/UPDATE statements, but then I read somewhere that booleans are not indexed in SqlServer, so I guess that it's bo point using it anyway. Am i wrong in this assumption? Please help.

Pettrer, very puzzled ;-)

|||

If your field is bit field is stored on SQL as 0 or 1 but in gridView in VB is be displayed as True/False by default (or as check box if you ask for this )check this code :

select

cast(-10asbit) true,cast(0asbit) false,cast(1asbit) true,cast(10asbit) true

As you could see from this example I was a little wrong in SQL looks like false is 0 and everything else is true

Thanks

|||

Yes, you're right - everything that is not 0 is true; absint - not absinth! ;-) - comes to mind...

However, my questions are (forgive me if I misunderstood your reply):

1. How do I get from the checkbox to boolean in the db (the other way becomes correct automatically )?

2. Is this a behaviour I want (ie having booleans in the db), as I read somewhere that booleans are not indexed - which sounds really strange to me, as that would affect server performance)? I could of course convert everything to 0/1 as integers, but it seems rather stupid to do that. What do people do? Is no one using VB with SqlServer?

Pettrer, puzzled! :-)

|||Use bit data type which is Ture/False in the database and you can set this column to required and with a default if you want.|||

Limno,

Thanks for replying. You're right of course, but the problem is not the SqlServer db per se, but how badly VB works together with it. I took a look in the db and realised that if I do this the boolean way, it's still one byte per entry, as they can't be divided into the bits. If I use the strings True and False, I get two bytes instead. It's really not a big deal, then (my advice to other VB developers must be to use the textual representation in the db). But it's really, really strange!

Pettrer

Boolean value

How can I return boolean value to the client.
This doesn't work:
SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
If I return :
SELECT column1=case when exists (select ...) then 1 else 0 end,...
then client gets the int32 value
regards,S> How can I return boolean value to the client.
You can't. There is no Boolean datatype in TSQL. There is a bit datatype, wh
ich is a integer-class
datatype restricted to the values 0, 1 and NULL. Some libraries (ADO etc) wi
ll interpret 1 as TRUE
and 0 as FALSE. If you want to return but, you have to CAST. for example
SELECT case when exists (select ...) then CAST(1 AS bit) else CAST(0 AS bit)
end AS column1
Or you can do the CAST outside the CASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:O3fYef9oFHA.3256@.tk2msftngp13.p
hx.gbl...
> How can I return boolean value to the client.
> This doesn't work:
> SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
> If I return :
> SELECT column1=case when exists (select ...) then 1 else 0 end,...
> then client gets the int32 value
> regards,S
>|||There's no boolean data type in SQL. I'd suggest using the bit data type.
Like this:
ELECT column1=case when exists (select ...) then cast(1 as bit) else cast(0
as bit) end
ML

Boolean Report Parameters

Hi, I have created a report parameter using a Boolean data type.

When I preview my Report I can select either True or False for this parameter. Is there any way of changing the 'True' label to say something else and again the same with the 'False' label?

Click on Report-Parameters

Select your parameter and go down to Available Values

Type in a label and the value

ex.

Open - True

Closed - False

Simone

|||This brings the values up in a list box, I was hoping to keep the selection as tick boxes but amend 'True' to say 'Open' for example.|||

I ran into the same issue today when I tried to change "String" parameter to "Boolean", and hoping to have radiobox instead of listbox dropdown.

No good, it'd be boolean type with dropdown, which is the same as "String" type essentially

It'd be nice to able to change label for "True" and "False"

boolean programming

Is bit the closest to the boolean (true or false) datatype in tsql? Even
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).

> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530

> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?

Boolean parameters

Hello,
I'm creating a boolean parameter in Report Designer and set {Label = "Yes",
Value = True} and {Label = "No", Value = False}. Then on Preview Page I see
to radio buttons, the first one is labeled "true" and the other one is
"false". Is this a bug? Can I set the labels I want to this radio buttons?This is a known limitation and scheduled to be addressed in a future
release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>|||Changing this behavior, for Booleans, is on our wish list for inclusion in a
future release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>

Boolean in SQL Server 2005

What is the boolean equivalent for TRUE and FALSE in SQL Server 2005?

In Access true = -1 and false = 0
What should I use in SQL Server bit values?0 = false; 1 = true|||Can I write statements like Match='TRUE' instead of Match=1?|||

No, you need to use 0 and 1. SQL Server has no knowledge of true and false. This is only possible by creating custom data type via .NET.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Hi,

but this is not recommended, you should really use the internal used data types for this simple operations. Your application should transfer the value to refelect the appropiate SQL Statements and present the data types the way you want the users to see them.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||why you want to do this. Do you have requirment to write a one code for both SQL Server and Access

Boolean formatting in report

I have a data source with a column that is a boolean data type. When
I render this data on the report the value of 0 displays as "False"
and 1 displays as "True". I would like 0 to display as "No" and 1 to
display as "Yes". I am assuming I have to set up an IIF statement
that evaluates the value and changes the display based on that. I was
wondering if anyone knew of a better way, such as a Custom Format
value that would be a better route?
Thanks in advance for any assistance!On Sep 20, 2:03 pm, midas <mcneeley1...@.yahoo.com> wrote:
> I have a data source with a column that is a boolean data type. When
> I render this data on the report the value of 0 displays as "False"
> and 1 displays as "True". I would like 0 to display as "No" and 1 to
> display as "Yes". I am assuming I have to set up an IIF statement
> that evaluates the value and changes the display based on that. I was
> wondering if anyone knew of a better way, such as a Custom Format
> value that would be a better route?
> Thanks in advance for any assistance!
The way you mentioned is quick and the effort is negligible.
=iif(Fields!SomeField.Value = True, "Yes", "No") -or- =iif(Fields!
SomeField.Value = 1, "Yes", "No")
You could also try using a case statement in the query/stored
procedure that is sourcing the report.
select case when SomeField = 'True' then 'Yes' else 'No' end as
SomeField, ... from table_x ...
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, I went with the case. I was just trying to be lazy. ;)|||On Sep 21, 2:57 pm, midas <mcneeley1...@.yahoo.com> wrote:
> Thanks, I went with the case. I was just trying to be lazy. ;)
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql

boolean fields displayed as true or false

I have a bit field in my data source that displays as true/false on my
report. I can't see how to set this so that it displays as 1 or 0. In
Visual Studio, can someone help?=iif (Fields!yourData.value = "True", 1, 0)
"beeyule" wrote:
> I have a bit field in my data source that displays as true/false on my
> report. I can't see how to set this so that it displays as 1 or 0. In
> Visual Studio, can someone help?

Boolean bit field Which is True False

I have a field that I need to query for true and false.
The field is a bit.
Is True 0 or 1.
I can't open Books Online and the download instructions based on my version
SQL 2000, are not clear and I don't know what detailed version I have nor
where to find it.
--
MichaelMI guess it depends on how your app inserts the data...
create table foo (hidden bit)
insert into foo values ('Y')
select * from foo
error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Y' to a column of data type bit.
Keith Kratochvil
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
>I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
> version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Michael Miller wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM
A bit column is neither True nor False because bit is a numeric
datatype not a boolean. Usually 1 is understood to mean True and 0 is
understood to mean False. Of course it's always possible that the
person who designed your database may not have respected that
convention at all. One reason I dislike using the bit type is precisely
because of this ambiguity. A meaningful code makes it much clearer just
what the designer's intention is. On the other hand proper
documentation ought to answer your question in any case :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||They are what you want them to be; SQL server only stores the 0 or 1. In
Access, true is numerically represented by -1, and false is 0.
You could say that 1 is true and 0 is false, and extract data from the
column using "true" and "false" with a CASE expression:
SELECT
CASE
WHEN Column1 = 1 THEN 'True'
ELSE 'False'
END
FROM [Your Table]
"Michael Miller" wrote:

> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||I think .Net automatically converts bit columns to 0=False and 1=True.
Outside of .Net it would be application specific. If you are going to use a
bit column, then I would make it a point to code around values of 0 and 1,
rather than True or False. You also need to take into account NULL values,
if this column allows them. BOL does not have much to say about this
datatype. Bottom line, bit is an integer value, and not a boolean value.
It is up to your code to determine how to use it. Here are some quotes from
BOL.
Converting bit Data
Converting to bit promotes any nonzero value to 1.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
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.
Special Data
Special data consists of data that does not fit any of the categories of
data such as binary data, character data, Unicode data, date and time data,
numeric data and monetary data.
Microsoft SQL ServerT 2000 includes four types of special data:
bit
Consists of either a 1 or a 0. Use the bit data type when representing TRUE
or FALSE, or YES or NO. For example, a client questionnaire that asks if
this is the client's first visit can be stored in a bit column.
Constants
A constant, also known as a literal or a scalar value, is a symbol that
represents a specific data value. The format of a constant depends on the
data type of the value it represents.
bit constants
bit constants are represented by the numbers zero or one, and are not
enclosed in quotation marks. If a number larger than one is used, it is
converted to one.
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||bit is an integer data type, and can be used to store 0, 1, or NULL.
You will need to define whether a value of 1 stands for true (or otherwise)
throughout your application.
- Data Types (SS2000 books online)
http://msdn.microsoft.com/library/d...br />
7msw.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> bl
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com g...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Thank you ALL.
I'll investigate and see what the intention of the creator was.
I'll start with 1=true/yes and 0=false/no
--
MichaelM
"Michael Miller" wrote:

> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||>> I have a field [sic] that I need to query for true and false.<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Totally different concepts!
You are missing another basic point here. There is no BOOLEAN data
type in SQL and it is a sign of bad programming to write with lower
level bit flags in a high-level language.
Get a book on RDBMS and read about three-valued logic and NULLs to
learn why this cannot be implemented in the SQL model.
Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?|||Understood. I am querying and reporting and needed to know how they are
using a particular field, which is bit. I didn't set up the database and
have differed with the contractor on other design matters as well. For
example, we have two types of tables, one called SAG and the other called
TRG. Instead of making a 3 char field, he set up one fieldname that called
TRG, and then he says whether it is true or not (the bit field). Since he
has 0's and 1's, I was trying to figure out which was which.
I would have named the field GridType, and entered either SAG or TRG, for
readability.
I understand about the education. I have no formal education in SQL Server,
other than some OJT working on a VB6 project with SQL, for about 3 years.
Added to that is 5 years in Access and 15 years in Paradox (DOS 1.0 -->Win
8.0).
Thanks for your input.
--
MichaelM
"--CELKO--" wrote:

> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Totally different concepts!
>
> You are missing another basic point here. There is no BOOLEAN data
> type in SQL and it is a sign of bad programming to write with lower
> level bit flags in a high-level language.
> Get a book on RDBMS and read about three-valued logic and NULLs to
> learn why this cannot be implemented in the SQL model.
> Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?
>

Bit field true/false

When I code my SQL statement and want to check equal condition on a bit
field, should I use True/False or 1/0?
David1/0
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||I would use the numeric one for many pratical reasons (Converting from
client Applications, Case Expressions, the handle of the numeric type, etc.)
Just my practical issues,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David C" <dlchase@.lifetimeinc.com> schrieb im Newsbeitrag
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Before CELKO come in and get you a severe tongue-lashing about using bits
and about using the word 'fields' instead of 'columns'...
There is no boolean data type in SQL server.
So use 1 or 0 to check equality.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Y/N ?
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Maybe you should use CHAR(1) with a check constraint IN ('T', 'F').
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Not withstanding Don Celko and his tilting against Bit data types and the wo
rds
"field" and "record", with SQL Server I'll either use the Bit datatype or a
TinyInt with a Check constraint of In(0,1). I'll use Char(1) when the value
means something other than a boolean concept (e.g. true/false, on/off, yes/n
o)
like a Gender column with a check constraint of In('M', 'F').
Thomas
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit fi
eld,
> should I use True/False or 1/0?
> David
>|||Agree w/all above, but would add that Bit fields *CANNOT be indexed, and
since 1 to 8 bit fields will t ake up a full byte of storage anyway, I feel
it's generally better to use tinyints, with COnstraint In (0,1) as Thomas
suggested.
* The restriction on not being able to use a bit column in an index, may not
be relevant, if you never use the bit column in a order by, or Where clause,
or anywhere else where it might affect which rows are output or the order in
which they are output, then it doesn't matter... Also, even if you are using
such a query, if the values are close to evenly (50-50) distributed and not
skewed a lot (say, 95% true, 5% false) the query generally won't use an inde
x
anyway.
"David C" wrote:

> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>
>|||> Agree w/all above, but would add that Bit fields *CANNOT be indexed
This is not true. It is only Enterprise Manager which prevents you from
doing so, but it is certainly valid (and valid doesn't necessarily mean
useful). For more info, see http://www.aspfaq.com/2530|||R U sure? in SQL 2000, at least, the BOL says they can't...
bit data type, described...
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft? SQL Server? optimizes the storage used for bit columns. If the
re
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.
... etc...
I'm checking out the reference you posted...
"Aaron [SQL Server MVP]" wrote:

> This is not true. It is only Enterprise Manager which prevents you from
> doing so, but it is certainly valid (and valid doesn't necessarily mean
> useful). For more info, see http://www.aspfaq.com/2530
>
>

bit field

HI,

while saving in bit field sometimes it is saved as true or false. sometimes it is saved as 0 or 1.

how to make it true or false when i needed.

if i set default value to 0 will it save 1 or 0 always.

with default 0 i need to store truee or false. how it is possible this way.

thanks

venp

Hi,

the bit data type always stores either 0 (which is false) or 1 (which is true). A default value is used if no value is explictly passed through.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

HI jens,

sometimes it stores the word True or False(bit field) thats why i'm confused.

i need to know when it stores 0 or False and 1 or True.

it is confusing me a lot.

venp

|||

Hi,

no the BIT data type is a INT type with the constraint of holding only 0 or 1. The true or false is a client presentation you are seeing, Excel will for example use this presentation for the boolean type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, 00	True	False
01	True	False
02	True	False
210	True	False
220	True	False
250	True	False
03	False	False

the above values i copied from table. true ,false fields are bit datatype.

venp--

|||Where did you copied the output from ?|||

from sqlserver table .i opened the table in sql server and copied.

venp--

|||Do the same query in the query window of SSMS and you will see that this is just a question of presentation. SSMS will display BIT as true / false but SQL Server stores 1/0.

-Jens.|||

you are right

venp--