0

How do you suggest to enforce a constraint on a column that maps over the values of C# flagged enum?

A nice solution for managing bitwise operations is here: SQL Server Bitwise behave like C# Enum Flags

Additionally, I would like to restrict the entered values in this column. The enum contains about 10 members now and it might raise in the future.

Community
  • 1
  • 1
bjnr
  • 3,353
  • 1
  • 18
  • 32

2 Answers2

2

If you have a bitwise enum with 10 numbers, then they'll have values 1 through 512, at powers of 2. The range can therefore be 0 to 1023. Add a constraint like this:

CREATE TABLE sample
   (
   id       INT PRIMARY KEY,
   flags    INT,
   CONSTRAINT chk_flgs CHECK (flags BETWEEN 0 and 1023)
   )

However, I would strongly caution against this. Relational databases are meant to allow accessing data in various ways and easily running queries against the data. Bitwise operators are not efficient in SQL. Think of a situation where you want to list all the matching flags? It'd be more normalized to create a lookup table with the enumeration values and add a cross reference table between your source and the lookup table.

The above sample would become this:

CREATE TABLE Flags
    (
    FlagID  INT PRIMARY KEY, -- When inserted, match enum value
    FlagName    nvarchar(50)
    );

CREATE TABLE SampleS
    (
    SampleID    INT PRIMARY KEY
    );

CREATE TABLE Xref_Sample_Flags
    (
    SampleID INT,
    FlagID INT,
    CONSTRAINT FK_SampleID FOREIGN KEY (SampleID) REFERENCES Sample (SampleID),
    CONSTRAINT FK_FlagID FOREIGN KEY (FlagID) REFERENCES Flags (FlagID)
    );

More tables, but in long run easier to maintain and query against.

Samuel Neff
  • 73,278
  • 17
  • 138
  • 182
  • Why do you feel that they are not efficient? I haven't seen any issues with that. I believe the greatest advantage of using bitwise operators begins with design. I have a table of users who are in several groups. I would have to design a one to many table relationship between the groups and users tables, and select all those rows just to figure out all the groups for each user. Whereas with bitwise, I need only select a single field in users with the bitwise operation logic. – Gregory Bologna Sep 29 '21 at 12:50
  • @GregoryBologna and how do you search for users within a particular group? You need to loop through every row and can't use an index. If you haven't seen any issues it's either because you're not filtering or joining on that field or you have a negligible amount of data. Doing anything wacky to avoid creating tables in a relational database is really bad practice. Relational databases are supposed to have tables and those tables should follow normalization standards. If this is not what you want, then you're probably using the wrong kind of database. – Samuel Neff Sep 29 '21 at 20:37
  • This is not "wacky". You use a field on your groups with each group having a bit value, like 2. 4. 8, 16 etc. Then you perform bitwise math on your user table, so if user A belongs to group 2 and 8 then the value for the user_group_id would be 10. Then using bitwise math on the join will return group rows for 2 and 8. join `users u on(10 & u.user_group_id) = 10 or on the where 10 = (10 & u.user_group_id). This is a very good video about doing this. https://wildermuth.com/2021/03/28/Coding-Shorts-Demystifying-Bitwise-Operators-in-C – Gregory Bologna Sep 29 '21 at 21:04
  • @GregoryBologna Of course you're welcome to your opinion. Mine is... The video is talking about bitwise fields in C#. There's nothing wrong with that. Doing it in SQL is what's nuts. Have you used a bitwise join on two tables with tens of millions of rows? What does the query plan look like? It'll be a full table scan. Every time. You can keep your opinion if you want, but if it was someone on my team, they wouldn't be on my team much longer. It demonstrates a fundamental misunderstanding of how relational databases work and how to optimize them. – Samuel Neff Sep 30 '21 at 01:44
  • 1
    You have the opinion, not me. I would not have this discussion if using bitwise operations in SQL was wrong. And to be clear, my example doesn't discuss using it on a table of millions of rows. Maybe that's where we're bumping heads? One of the bullets of being a competent software engineer is problem solving skills. Bitwise operators are fundamental to core computing processes which can be adapted to solving problems like returning users that exist within a group instead of creating redundant one-to-many tables. Tables still support ref integ. when it has a field for binary operations. – Gregory Bologna Sep 30 '21 at 13:30
  • @GregoryBologna As I said, whether or not using bitwise is ok in a database is an opinion. You're clear on yours. I think I've been clear on mine. No need to reconcile, we have different opinions. – Samuel Neff Sep 30 '21 at 16:07
1

I wouldn't constrain a bitwise field, no point. If its based on values from an enum and user's don't manually enter / edit this data, then there is no point to constrain it.

As for performance, a colleague and I went about testing the different approaches, him doing it conventionally and I bitwise. Result was that the bitwise operation outperformed the conventional way on a large chunk of data. Conventional way returned the result in something like 16.7 seconds showing quite a large query plan, where as the bitwise operation did it in 0.6 seconds with a much smaller plan. @Samuel Neff - it doesn't involve a table scan if you put an index on it.

Toby Kolk
  • 56
  • 5