SQL Server - What is bitwise operation and how to store multiple values ​​in the same column

Reading Time: 5 minutes

Speak guys!
In this article, I will show you what bitwise operation is available since SQL Server 2005, and how to store multiple distinct values ​​in the same column, which may even have value matching without the need to create a N: N table. to store the combinations.

I've seen on some systems this approach of using bitwise operations to store multiple values ​​in the same column and I always wondered why not create an N: N table for it, because it's much simpler and allows even indexing. After clearing some questions in Telegram about bitwise operations, I decided to write this article to demonstrate these two ways to implement a multivalued relationship between two tables, although not a very common scenario to meet.

In a quick scenario to exemplify an interesting use for this feature, let's imagine a structure for storing which technologies each person mastered, which could be one or several technologies for the same person.

Script for creating sample tables:

Proposed Structure:

Scenario 1 - Using table N: N

When you need an entity to have more than one value, a widely used strategy is to create an N: N table to list the 2 entities:

And getting the following configuration:

That is, I related people's ID to the skill ID. For each desired combination, we will add a record to create this relationship between the 2 entities. For this solution, it was necessary to create a new table for this relationship, but the information visualization is very easy to search and filter. Regarding performance, we can create an index to make searching easier if necessary.

If I want to identify who are the people who own a particular Skill, just filter by the Skill ID I want:

Scenario 2 - Using bitwise operations

Entering the world of bitwise operations, we can create a solution for storing multiple values ​​without having to create an N: N table for it by storing multiple values ​​in the same column (and without the jumble of serialized data).

To make this possible, first of all I need to create a new column in the Professionals table to store these multiple values. And it will be a column of type INT (that's right .. rs):

In addition, our Skills structure will need some changes, because I need the ID's to work in the form of powers of 2, that is, 1, 2, 4, 8, 16, 32, 64, 128 ...

Result:

Using this newly created column (Id_New_Ability), Skills will now be associated with people adding the skills ID's and storing the total value in the Ids_Skill column of the #Professionals table.

Example:
In this example, I will associate the same 1 scenario skills (SQL, C #, PHP, Javascript, HTML, and CSS) to the Dirceu person:

Of course, in this bitwise approach, management gets a lot harder, but using an application interface makes it transparent to the user.

To identify the Skills that this person has, we can use bitwise operations (&), which basically makes the binary comparison between 2 values:

Result:

And to identify the people in the table who have the Skill PHP (Id = 3, New_Ability_ID = 4):

Result:

If you want to bring up a list of people and skills, just like we did in the 1 scenario with simple JOINS, well .. the task using bitwise architecture will be much more complex than that.

Advantages and disadvantages of using bitwise operations in this situation

After demonstrating the most common ways to assign multiple values ​​between 2 entities, I will also demonstrate the advantages and disadvantages of using bitwise operations in this situation.

Advantages

• No need to create one more table just to store the relationship between 2 entities

Disadvantages

• Much more complex to understand
• Indexing is not efficient as it would have to be created 1 calculated column for each attribute with bitwise operation and index each calculated column
• More complex to audit each relationship build between 2 tables, as the same record is updated multiple times. In the N: N scenario, each record could have the creation date and user who created it.
• Whenever a relationship is created / changed / deleted, the value of this column needs to be recalculated instead of just deleting 1 record.
• Bringing a relationship between people and skills is a daunting task
• Creating a new bank skill could possibly require some system change

As demonstrated above, this solution of using bitwise ends up bringing much more disadvantages than advantages (if this “advantage” is significant). I really prefer using an N: N table to store multivalued relationships, both for performance and for practicality.

References:
- https://www.mssqltips.com/sqlservertip/1218/sql-server-bitwise-operators-to-store-multiple-values-in-one-column/
- https://www.sqlservercentral.com/articles/introduction-to-bitmasking-in-sql-server-2005
- https://www.red-gate.com/simple-talk/sql/t-sql-programming/bitwise-operations-in-t-sql/

Well, I hope you enjoyed this article and learned something new for your daily life 🙂
A big hug and until next time!