2

Let's suppose I have a table like this: t1(c1, c2, ..., cn)

Let's further suppose that I want to update c1 to 1 if c2 equals 'v' and to 0 otherwise. This is how I tried:

update t1
set c1 = (c2 = 'v');

My intention was to see whether c2 equals 'v' and assign the logical value to c1. This had a syntax error according to SSMS 2008, so I tried the following:

select c2 = 'v'
from t1;

It selected all the rows and showed 'v' instead of the real value of c2. As you can see, I am confused partly due to the ambivalency of the = operator, partly because of its behavior in the select. Off course, the effective problems have been solved, but I wonder whether there is a clear, logical explanation to the behavior of the = operator experienced as described.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Did the syntax error have more text? Or just "syntax error near somethingsomething"? – Lasse V. Karlsen Jul 15 '16 at 16:50
  • 1
    There is no boolean type in T-SQL and there is no arbitrary "return 1 or 0" mechanism. (`select token = value` tries to alias value to token) – Alex K. Jul 15 '16 at 16:55
  • Try `SELECT (c2 = 'v') AS x FROM t1` and tell us what happened. – Lasse V. Karlsen Jul 15 '16 at 16:55
  • 3
    `c2 = 'v'` is equivalent to `'v' as c2`. That is an alternate syntax for declaring a column alias. – Martin Smith Jul 15 '16 at 16:59
  • @MartinSmith, this is true, but doesn't answer the question... I must admit, that I did not think about this to much before, but after testing around to place my answer below I got the feeling, that SQL Server does not support a `boolean` at all... The `BIT` seems to be a *reduced INT*... Even a scalar function returning `BIT` must be queried with `=0` or `=1`. So I think the answer is: The comparison as above simply does not have a data type which could be used elsewhere... – Shnugo Jul 15 '16 at 17:28
  • @Shnugo - If it was submitted as an answer then "not answering the question" would be a valid complaint. It wasn't. And yes SQL Server does not have a boolean datatype. – Martin Smith Jul 15 '16 at 17:33
  • @MartinSmith, I'm not complaining :-) Far away from complaining... Sorry, if you got it that way... – Shnugo Jul 15 '16 at 17:40
  • @Shnugo - That's fine! I was just pointing out that the "but doesn't answer the question" bit is unnecessary for comments as if they do they should be answers. – Martin Smith Jul 15 '16 at 17:45

1 Answers1

1

The answer is simple:

There is no real boolean data type in SQL Server. There is BIT, but this is just an integer value reduced to 0 and 1 and you always have to compare this with your target value.

This would not work:

DECLARE @bit BIT=1;
IF @bit DoSomething;

You'd have to use 

IF @bit=1 DoSomething;

Same with CASE WHEN @bit=1 THEN ...

But there is a boolean actually, like we know this from EXISTS:

IF EXISTS(SELECT 1 FROM SomeWhere) DoSomething;

But this is not a data type one could use directly... Even a boolean scalar function you'd declare with RETURNS BIT and you'd have to use the return value with a comparison against 0 or 1.

Specialty 1

This will not return false but rather fill xyz into @tst

DECLARE @tst VARCHAR(100)='test';
SELECT @tst='xyz';
SELECT @tst;

With paranthesis SQL Server tries to take the value, but the comparison has no value...

SELECT (@tst='xyz'); --error

Specialty 2

This will use the left side as alias

SELECT test=Column FROM Table;

Solution with CASE

This will update just the second row

DECLARE @tbl TABLE(c1 int,c2 int, c3 int);
INSERT INTO @tbl VALUES(1,1,1),(2,2,3),(4,4,4);
UPDATE @tbl SET c1=CASE WHEN c2<>c3 THEN 99 ELSE c1 END
SELECT * FROM @tbl

More Examples:

These are forbidden

SET @bit=EXISTS(SELECT 1 FROM @tbl WHERE c1=50);
SET @bit=(EXISTS(SELECT 1 FROM @tbl WHERE c1=50));
SET @bit=(SELECT EXISTS(SELECT 1 FROM @tbl WHERE c1=50));

But this is allowed

SET @bit=CASE WHEN EXISTS(SELECT 1 FROM @tbl WHERE c1=50) THEN 1 ELSE 0 END;

There is a related question, you might to read further: This related quesiton covers this aspect too: Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • The problem is I do not understand the cause of the behavior I have experienced. – Lajos Arpad Jul 15 '16 at 17:01
  • @LajosArpad please see my update. Other as in many languages the comparison does not return a value of data type `BIT` which you could assign somewhere. BIT returning functions must be questioned with `dbo.function()=0`. The boolean result is - but I cannot really explain this - something special... – Shnugo Jul 15 '16 at 17:09
  • @LajosArpad One more thought: I think, that the `BIT` is rather handled as `INT`, but reduced to `0` and `1`, that's why a bit-function must be queried with `=0` or `=1` even if one might expect, the result is the boolean value already. The *real boolean* value comes from the comparison but is not a data type one could use directly... – Shnugo Jul 15 '16 at 17:24
  • Very nice update! SQL Server does not have a boolean type then, since BIT is just a single-digit binary number. – Lajos Arpad Jul 16 '16 at 12:46
  • @LajosArpad, just for your info: I re-wrote my answer to put all the comments and thoughts into one... – Shnugo Jul 16 '16 at 16:15