How to handle a lot of flags for a SQL record

Question

Hello,

I need advice on how to handle relatively large set of flags in my SQL2k8 table.

Two question, bear with me please :)

Let's say I have 20 flags I'd like to store for one record.

For example:

CanRead = 0x1 CanWrite = 0x2 CanModify = 0x4 ... and so on to the final flag 2^20

Now, if i set the following combination of one record: Permissions = CanRead | CanWrite

I can easily check whether that record has required permission by doing WHERE (Permissions & CanRead) = CanRead

That works.

But, I would also like to retrieve all records that can either write OR modify.

If I issue WHERE (Permissions & ( CanWrite | CanModify )) = (CanWrite | CanModify) i obviously won't get my record that has permissions set to CanRead | CanWrite

In other words, how can I find records that match ANY of the flags in my mask that i'm sending to the procedure?

Second question, how performant is in in SQL 2008? Would it actually be better to create 20 bit fields?

Thanks for your help

Answer 1

What about

WHERE (Permissions & CanWrite) = CanWrite 
OR (Permissions & CanModify) = CanModify

?

Answer 2

WHERE (Permissions & CanWrite) = CanWrite OR (Permissions & CanModify) = CanModify

I think

Answer 3

It'd be considerably better to have a different permissions model.

20 flags would indicate to me that a rethink is required, most filing systems can get by with 12 basic flags and ACLS - maybe having a separate table that merely grants permissions, or grouping objects or accessors to allow different control.

I would expect a select to be quicker to have 20 separate fields - but I wouldn't add 20 fields for performance either.

--update--

the original query written as

 WHERE (Permissions & ( CanWrite | CanModify )) > 0

would suffice, however it sounds to be as though what you have in the database is a set of attributes that an entity can have. In which case the only sensible (in database terms) way to do this is with a one-to-many relationship to an attribute table.

Answer 4

Nope, that won't work.

I'm sending just one mask to the procedure

Something like @filter which in C# i fill with @filter = CanModify | CanWrite

So, the procedure gets the OR-ed value as a filter.

Oh and by the way, it is NOT a permission model, I'm using that just as an example.

I really have around 20 unique flags that my object can have.

Answer 5

Isn't it as simple as ...

WHERE (Permissions & ( CanWrite | CanModify )) > 0

...as any 'bit' being set to 1 will result in a non-zero value for the '&' operator.

It's late in the day, and I'm about to go home, so my brain could be working inefficiently.

Answer 6

Don't to that. It's like saving a CSV string into a memo field and defeating the purpose of a database.

Use a boolean (bit) value for every flag. In this specific sample you're finding everything that can read and can write or modify:

WHERE CanRead AND (CanWrite OR CanModify)

Simple pure SQL with no clever hacks. The extra 7 bit's you're wasting for every flag aren't worth the headache.

Answer 7

I assume your Permissions column is an Int. If it is, I encourage you to play around with the sample code I provide below. This should give you a clear indication of how the functionality works.

Declare @Temp Table(Permission Int, PermissionType VarChar(20))

Declare @CanRead Int
Declare @CanWrite Int
Declare @CanModify Int

Select @CanRead = 1, @CanWrite = 2, @CanModify = 4

Insert Into @Temp Values(@CanRead | @CanWrite, 'Read,write')
Insert Into @Temp Values(@CanRead, 'Read')
Insert Into @Temp Values(@CanWrite, 'Write')
Insert Into @Temp Values(@CanModify | @CanWrite, 'Modify, write')
Insert Into @Temp Values(@CanModify, 'Modify')

Select * 
From   @Temp 
Where  Permission & (@CanRead | @CanWrite) > 0

Select * 
From   @Temp 
Where  Permission & (@CanRead | @CanModify) > 0

When you use logical and, you will get a number with the 1's set appropriately based on your condition. If nothing matches, the result will be 0. If 1 or more condition matches, the result will be greater than 0.

Let me show you an example.

Suppose CanRead = 1, CanWrite = 2, and CanModify = 4. The valid combinations are:

Modify Write Read Permissions
------ ----- ---- -----------
  0       0    0   Nothing
  0       0    1   Read
  0       1    0   Write
  0       1    1   Read, Write
  1       0    0   Modify
  1       0    1   Modify, Read
  1       1    0   Modify, Write
  1       1    1   Modify, Write, Read

Now, suppose you want to test for Read or Modify. From your app, you would pass in (CanRead | CanModify). This would be 101 (in binary).

First, let's test this against a row in the table the ONLY has read.

   001 (Row from table)
&  101 (Permissions to test)
------
   001 (result is greater than 0)

Now, let's test against a row that only has Write.

   010 (Row from table)
&  101 (Permission to test)
------
   000 (result = 0)

Now test it against row that has all 3 permissions.

   111 (Row from table)
&  101 (Permission to test)
------
   101 (result is greater than 0)

I hope you can see that if the result of the AND operation results in a value = 0, then none of the tested permissions apply to that row. If the value is greater than 0, then at least one row is present.

Answer 8

Do this only if you are also querying by some other key.

Don't do this if you are querying by flag combinations. An index against this column will not help you in general. You'll be restricted to table-scans.