IT seems to me that I can't write something like this:
DECLARE @.Exists bit
SELECT @.Exists=(UserId Is NOT NULL)
FROM User
WHERE FirstName="Some name" And LastNAme="Other"
I tries this and got an error. I am using Case to get around it. Just wanted
to know that I am correct. Is there other way doing the same thing?
Thanks,
Shimon.There is no boolean datatype in SQL Server. An alternative to a CASE
expression is an IF statement:
DECLARE @.Exists bit
IF EXISTS
(
SELECT *
FROM User
WHERE FirstName="Some name" AND
LastName="Other" AND
UserId IS NOT NULL
)
SET @.Exists = 1
ELSE
SET @.Exists = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Boolean expressions are valid in a WHERE clause, in CASE expressions
and in other places but can't be assigned directly to a variable
because there is no explicit boolean datatype in SQL Server. Use CASE
to convert a boolean expression to a valid datatype.
David Portas
SQL Server MVP
--|||You need a good SQL book.
1) The double quotes in Standard SQL are used to mark an identifier;
strings have always used single quotes.
2) There are no BOOLEAN or BIT data types in SQL. They would be a
total screw up. Why? Because machine level things like a BIT or BYTE
datatype have no place in a high level language like SQL. SQL is a
high level language; it is abstract and defined without regard to
PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
You have a major learning curve in front of you.
I usually tell peopel it takes at least a year of full time programming
to UN-learn their origianl language.|||
--CELKO-- wrote:
>You need a good SQL book.
>1) The double quotes in Standard SQL are used to mark an identifier;
>strings have always used single quotes.
>2) There are no BOOLEAN or BIT data types in SQL. They would be a
>total screw up. Why? Because machine level things like a BIT or BYTE
>datatype have no place in a high level language like SQL. SQL is a
>high level language; it is abstract and defined without regard to
>PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
You are mistaken. Both types appear in the ISO/IEC SQL standards.
BOOLEANs were introduced in SQL-99 (they correspond to PL/I's BIT(1) type)
and there was a BIT type in SQL-92 and early drafts of SQL-99, but it's gone
now, I believe.
>SQL is a high level language; it is abstract and defined without regard
>to PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
Well-said!
>Bits and Bytes are the <i>lowest<i> units of hardware-specific,
>physical implementation you can get. Are you on a high-end or low-end
>machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
>complement or ones complement math? Hey, the standards allow decimal
>machines, so bits do not exist at all! What about NULLs? To be a SQL
>datatype, you have to have NULLs, so what is a NULL bit? By definition
>a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
>how are the bit-wise operations defined? Oh what a tangled web we
>weave when first we mix logical and physical :)
>
There is no confusion in SQL Server. The confusion is yours, in
confounding the
abstract type [bit] of SQL Server with the use of the word bit in
describing hardware.
They aren't the same thing at all. SQL Server [bit] behavior is
consistent, documented and
fully hardware-independent. [bit] values are 0 and 1, and it's possible for
a bit column or variable to be NULL. All the operations valid for [bit]
values
are well-defined and documented. If you think the documentation gets
any of them
wrong, please let us know.
>What does the implementation of the host languages do with bits? Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
>VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
>proprietary languages from the same vendor. That means <i>all<i> the
>host languages -- present, future and not-yet-defined -- can be
>different. Surely, no good programmer would ever write non-portable
>code by getting to such a low level as bit fiddling!!
>
There is no BOOLEAN type in SQL Server, and there are libraries to help
anyone using T-SQL with other languages bridge the gulf between different
type systems, which are expected with any pair of languages.
>There are usually two situations in practice. Either the bits are
>individual attributes or they are used as a vector to represent a
>single attribute. In the case of a single attribute, the encoding is
>limited to two values, which do not port to host languages or other
>SQLs, cannot be easily understood by an end user, and which cannot be
>expanded.
>
The bit type is generally non-portable, although like many other
common features not part of the standard, like SIGN, bit is not hard
to port when needed. It might be good to avoid one too-proprietary quirk,
in my opinion, that CAST(x as bit) evaluates to 1 when x is a non-zero
value of any numeric type. If that is avoided, it should port well to a
system
without a matching type if it is converted to the smallest available
integer type.
>In the second case what some Newbies, who are still thinking in terms
>of second and third generation programming languages or even punch
>cards, do is build a vector for a series of "yes/no" status codes,
>failing to see the status vector as a single attribute. Did you ever
>play the children's game "20 Questions" when you were young?
>Imagine you have six components for a loan approval, so you allocate
>bits in your second generation model of the world. You have 64 possible
>vectors, but only 5 of them are valid (i.e. you cannot be rejected for
>bankruptcy and still have good credit). For your data integrity, you
>can:
>
This is an interesting problem that has nothing to do with the original
post in this thread. It also does not have a single out-of-context answer.
>1) Ignore the problem. This is actually what <i>most<i> newbies do.
>2) Write elaborate CHECK() constraints with user defined functions or
>proprietary bit level library functions that cannot port and that run
>like cold glue.
>Now we add a 7-th condition to the vector -- which end does it go on?
>Why? How did you get it in the right place on all the possible
>hardware that it will ever use? Did all the code that references a bit
>in a word by its position do it right after the change?
>
Fortunately, [bit], like all SQL Server types, are hardware-independent.
There is no need to worry about this. Columns are referred to by their
names, and the underlying implementation is not exposed to the user.
>You need to sit down and think about how to design an encoding of the
>data that is high level, general enough to expand, abstract and
>portable. For example, is that loan approval a hierarchical code?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!
>
>
It's preposterous and presumptions, in my opinion, to be telling the poster
how to design something irrelevant to the question asked.
>You have a major learning curve in front of you.
>I usually tell peopel it takes at least a year of full time programming
>to UN-learn their origianl language.
>
And it takes just a little bit of listening and research to divest
oneself of erroneous beliefs, like yours, that the SQL Server
[bit] type exposes hardware details and depends on
endianness and other nonsense. You have to be interested
in learning and growing up, that's all.
Steve Kass
Drew University|||Well, I don't know the answer to your specific question (and I'm sorry for
that), but I'm glad you posted it and I'm sure you will get an answer soon
as there are plenty of competent dbas in this group. In the mean time, I'd
like to take a moment to affirm you as an aspiring dba. Given the fact that
you are asking for help assures me that you are striving to become a better
person in at least one small way. And for that, you are in inspiration to
all of us and a huge asset to your employer. God bless, and have a great
day!"
Celko-2
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Joe --
I read through your customary belittling comments about Shimon's total lack
of
intelligence/skill/knowledge/etc. and something just jumped out at me --
You completely forgot the derogatory comment about the poster's command of t
he English
language!
From a previous Celko post --
Rows and records are TOTALLY different concepts. Since you did not use
plurals correctly in your English, can I assume you are Asian and
speak/write a language without them?
Now, because there were relatively few errors in Shimon's question (it was a
short post),
that may have kept the message below the acceptable threshhold for being com
mented on. If
not, this was a serious oversight on your part; one I feel needs to be recti
fied as soon
as possible (i.e., your next posting).
Thanks in advance,
Carl
Thursday, March 29, 2012
Boolean serach on MS SQL database
Anyone know how to do a bollean search on a database?
It would need to accept 'OR', 'AND' conditions.
Would it be a good idea to maybe create stored procedures and call these somehow?
Thank you in advance.
Michael O'Connorselect * from table where boolfield = 1 or boolfield = 0 and boolfield = 1
heh funny. anyways, what exactly do you mean by boolean search? Can you be more specific?sql
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 report parameter with Yes/No choice list
SSRS 2005
In the Report Designer you can specify a Boolean report parameter with a
list of values. When the report is viewed on the Preview tab, the dropdown
list is rendered for the parameter instead of the usual True/False radio
buttons.
However, when the report is run on the Report Server, the same report
parameter is rendered with the usual True/False radio buttons and the defined
list is ignored.
Is this a bug in Report Designer, Report Server or both? Are there plans to
fix one of these so at least the behavior is consistent?
--
Chris, SSSIHello Chris,
I have reproduced this issue.
I am contacting the product team to check whether there is any fix or
workaround for this issue. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
Any update on a fix or workaround for this?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> I have reproduced this issue.
> I am contacting the product team to check whether there is any fix or
> workaround for this issue. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
The product team is still look into this. I will update any information
ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
After a long discussion with internal product team, this issue has a
workaround:
Use a string parameter with a Valid Values list instead.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
In the Report Designer you can specify a Boolean report parameter with a
list of values. When the report is viewed on the Preview tab, the dropdown
list is rendered for the parameter instead of the usual True/False radio
buttons.
However, when the report is run on the Report Server, the same report
parameter is rendered with the usual True/False radio buttons and the defined
list is ignored.
Is this a bug in Report Designer, Report Server or both? Are there plans to
fix one of these so at least the behavior is consistent?
--
Chris, SSSIHello Chris,
I have reproduced this issue.
I am contacting the product team to check whether there is any fix or
workaround for this issue. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
Any update on a fix or workaround for this?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> I have reproduced this issue.
> I am contacting the product team to check whether there is any fix or
> workaround for this issue. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
The product team is still look into this. I will update any information
ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
After a long discussion with internal product team, this issue has a
workaround:
Use a string parameter with a Valid Values list instead.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
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?
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?
>
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 parameter label
Hi all..
I have a parameter of type Boolean. The available values are non-queried
with labels 'YES' and 'NO' and the values are obviously True and False,
respectively. When the report renders, I get the two option buttons, but the
labels are 'true' and 'false'. What did I miss?
--
Michael White
Programmer/Analyst
Marion County, ORIIRC available values and labels are not applicable in case of boolean
parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <xxx.xxx.xxx> wrote in message
news:%2311IFe5ZFHA.3224@.TK2MSFTNGP10.phx.gbl...
> Hi all..
> I have a parameter of type Boolean. The available values are non-queried
> with labels 'YES' and 'NO' and the values are obviously True and False,
> respectively. When the report renders, I get the two option buttons, but
> the
> labels are 'true' and 'false'. What did I miss?
> --
> Michael White
> Programmer/Analyst
> Marion County, OR
>sql
I have a parameter of type Boolean. The available values are non-queried
with labels 'YES' and 'NO' and the values are obviously True and False,
respectively. When the report renders, I get the two option buttons, but the
labels are 'true' and 'false'. What did I miss?
--
Michael White
Programmer/Analyst
Marion County, ORIIRC available values and labels are not applicable in case of boolean
parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <xxx.xxx.xxx> wrote in message
news:%2311IFe5ZFHA.3224@.TK2MSFTNGP10.phx.gbl...
> Hi all..
> I have a parameter of type Boolean. The available values are non-queried
> with labels 'YES' and 'NO' and the values are obviously True and False,
> respectively. When the report renders, I get the two option buttons, but
> the
> labels are 'true' and 'false'. What did I miss?
> --
> Michael White
> Programmer/Analyst
> Marion County, OR
>sql
Subscribe to:
Posts (Atom)