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

Views: 613
Reading Time: 5 minutes

Hey 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:

1 Scenario - 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:

2 Scenario - 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):

Also, our Skills framework will need some changes, because I need the ID's to work in the form of powers of 2, ie 1, 2, 4, 8, 16, 32, 64…

Result:

Using this newly created column (Id_New_Ability), Skills will now be associated with people adding skill 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 shown above, this bitwise solution ends up with far more disadvantages than advantages (if that “advantage” is significant). I really prefer to use an N: N table to store multivalued relationships, both for performance and 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!