BitMask handling in SQL

anwarmir
anwarmir used Ask the Experts™
on
Hi,
 I have three bit columns colA, colB and colC.  If colA and ColC are both updated and set to 1. How can I automatically update the third Column ColC to 1 via a SQL rule or constraint. Meaning Colc is only set to 1 if ColA and ColB are 1.
Is there a better way to hanlde this design?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
you can change the datatypes  of the first two columns from bit to smallint and can chnage the last column as a computed column of these two.

declare @Tab table (i smallint default 0, j smallint default 0 , k as (i+j))

insert into @tab select 1,0
union all select 1,1

Select * from @tab
AneeshDatabase Consultant
Top Expert 2009

Commented:
there won't be any space/allocation issue between bit and smallint
Leigh PurvisDatabase Developer

Commented:
As Aneesh says - though would that not be
k as (i&j)
to do a bitwise comparison and get 1 in column k (/ColC)?
Database Consultant
Top Expert 2009
Commented:
If you still wanna continue with bit type, then use this

declare @Tab table (i bit default 0, j bit default 0 , k as ~(i&j))

insert into @tab select 1,0
union all select 1,1

Select * from @tab

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial