Boolean to Bit?

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