Logical functions in SELECT clause

Hi,

this ought to be elementary: I want to select the logical inverse of a bit type column. The online help tells me

Syntax [ NOT ] boolean_expression

Arguments

boolean_expression

Is any valid Microsoft? SQL Server? Boolean expression.

Result Value

NOT reverses the value of any Boolean expression.

The way I read this I should be able to then simply SELECT NOT bit_col, yet I get a syntax error when I try to do simply

SELECT (NOT CAST(1 AS BIT))

Arrrrgh! I've tried some syntactical variations on the theme, but it seems the not function just doesn't work in the SELECT clause, only in the WHERE clause.

Of course, I can get the result I want using

select case bit_col when 1 then 0 else 1 end as 'inverted_bit_col';

But, is there really no more elegant and well-performing way to select an inverted bit column than using a CASE?

Hi

I have tried ^ (Bitwise Exclusive OR) with 1 and it worked.

CREATE TABLE #tmp ( bitNumber BIT NOT NULL )

INSERT INTO #TMP VALUES (1)
INSERT INTO #TMP VALUES (0)

SELECT bitNumber, bitNumber ^ 1 FROM #TMP

The values in (bitNumber ^ 1 ) are reversed from bitNumber

NB.

|||Note that the bit field, although commonly used as a boolean field, is a bit field and holds values of 1 and 0, not true and false (although in most programming languages these values are interchangeable). But, as far as I know, a bit field does not evaluate to a boolean expression, and thus cannot be used as such. What you want is to use a bitwise operator as illustrated well above. Someone please correct me if I am wrong in this assumption.|||

But, is there really no more elegant and well-performing way to select an inverted bit column than using a CASE?

Define elegant and well-performing? Are you having performance problems with:

select case bit_col when 1 then 0 else 1 end as 'inverted_bit_col'

I can't see it being a performance question, and truthfully it is as elegant as any other way of doing it (a function would be better, so you might write a CLR function to do it, but it would be overkill. I like this way because it is clear what you are doing. you can also us the modulus function to "flip the bit":

(@.bit + 1) % 2

I tried each way using the following test ant the results were very much the same result.

set nocount on
create table testBit
(
bitCol bit
)
go
insert into testBit
select cast( case when rand() < .5 then 0 else 1 end as bit)
go 400000 --works in 2005. For 2000 you will need to write a loop

DBCC FREEPROCCACHE
declare @.getdate datetime
set @.getdate = getdate()
declare @.test table (bitCol bit)
insert into @.test
select (bitCol + 1) % 2
from testBit
select datediff(ms,@.getdate,getdate()) /1000.0 as [seconds (bitCol + 1) % 2]
go
DBCC FREEPROCCACHE
declare @.getdate datetime
set @.getdate = getdate()
declare @.test table (bitCol bit)
insert into @.test
select (bitCol ^ 1 )
from testBit
select datediff(ms,@.getdate,getdate()) /1000.0 as [seconds (bitCol ^ 1 )]
go
DBCC FREEPROCCACHE
declare @.getdate datetime
set @.getdate = getdate()
declare @.test table (bitCol bit)
insert into @.test
select case bitCol when 1 then 0 else 1 end
from testBit
select datediff(ms,@.getdate,getdate()) /1000.0 as [seconds case bitCol when 1 then 0 else 1 end]
go

Not scientific, but it does show that there is very little differnece between the different styles.

|||XOR does the trick, yes. Thanks!|||

I get it; in SQL Server, bit is not boolean. When I connect using ADO.NET (SqlClient, the native provider) the bit type maps to boolean, and I short-circuited...

How can I test (directly in query analyzer) what the type of the expression

bit_col ^ 1

is, when bit_col is of type bit? I still want the result to map to System.Boolean - I presume I'd have to cast the result as bit, but is there a way to make sql server tell me the type of an expression?

Thanks for the tip!

|||

Thank you for a very informative and complete reply.

I basically just held some prejudice against the case statements. I'm now wondering if that was completely without foundation...

Thanks!

|||

well.. we all know from boolean algebra that XOR is much lighter then ADD and especially division by modulo and processing script's CASE

It is my penny if we are talking about performance, let say we want to process 20 millions records :) I think the difference will be significant.

|||You can invert a bit with the ~ operator.|||I gotta be honest with you. If you are twiddling bits over 20 million rows you have a lot more problems than whether CASE works faster than ~ or modulo, or the other one :)