bit fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

bit fields

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-04-20 : 14:48:16

I have a table with multiple bit cols storing different pieces of security info for a given object/user pair (like sql server).

In this case, a user can be a part of multiple groups of which each have their own rights. 0 = deny, 1 = grant. I found out that Max cannot work on bit fields. what option do I have?

thanks.

________________________________________________

Bier is a privelege of the working man (or woman).

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-20 : 15:00:10

You could do

SELECT MAX(CONVERT(int,BitDataColumn))

Andy

Beauty is in the eyes of the beerholder

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-20 : 15:05:22

convert to tinyint before aggregating - Andy's suggestion
altering column defs to tinyint
using bitwise operators instead of max
for details and other options, post your DDL

Be One with the Optimizer
TG

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-04-20 : 15:19:14

thanks for the help.

i did a cast to tinyint and it returned the results correctly.

________________________________________________

Bier is a privelege of the working man (or woman).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-20 : 15:40:05

--I'm not having that problem:


set nocount on
declare @tb table (flag bit)
insert @tb
select null union all
select 1 union all
select 1 union all
select 0 union all
select null

select max( convert(tinyint, flag) ) from @tb

as another wacky idea you could store single tinyint column to hold all combinations

NoPrivs = 0
canView = 1
canDelete = 2
canUpdate = 4
canCreate = 8


declare @priv tinyint
select	@priv = 1 | 2 | 8
Select @priv [@priv]

Select	CanView 	= case when @priv & 1 = 1 then 1 else 0 end
	,CanDelete	= case when @priv & 2 = 2 then 1 else 0 end
	,CanUpdate	= case when @priv & 4 = 4 then 1 else 0 end
	,CanCreate	= case when @priv & 8 = 8 then 1 else 0 end

Be One with the Optimizer
TG

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-04-20 : 15:44:54

quote:
Originally posted by TG

--I'm not having that problem:

It was me, stupid me.
I thought about using Case too. I appreciate the help, I was lost in the fog there for a few minutes (aka entire life)

________________________________________________

Bier is a privelege of the working man (or woman).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources