Hey guys!
In this article, I will demonstrate to you what a bitwise operation is, available since SQL Server 2005, and how to store several distinct values ​​in the same column, which can even have a combination of values, without the need to create an N:N table to store the combinations.

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

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

Script for creating example tables:

IF (OBJECT_ID('tempdb..#Profissionais') IS NOT NULL) DROP TABLE #Profissionais
CREATE TABLE #Profissionais (
    Id_Pessoa INT IDENTITY(1,1),
    Nome VARCHAR(60)
)

IF (OBJECT_ID('tempdb..#Skills') IS NOT NULL) DROP TABLE #Skills
CREATE TABLE #Skills (
    Id_Habilidade INT IDENTITY(1,1),
    Skill VARCHAR(60)
)

INSERT INTO #Profissionais (Nome)
VALUES
    ('Dirceu'), ('Fabrício'), ('Tiago'), ('Luiz'), ('Alan'),
    ('Rafael'), ('Carol'), ('Leandro'), ('Fabiana'), ('Danyele'),
    ('Diego'), ('???? rs')


INSERT INTO #Skills ( Skill )
VALUES
    ('SQL'), ('C#'), ('PHP'), ('Java'), ('Javascript'),
    ('C'), ('R'), ('Python'), ('GO'), ('HTML'), ('CSS')

Proposed structure:

Scenario 1 – Using N:N table

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

IF (OBJECT_ID('tempdb..#Profissionais_Skills') IS NOT NULL) DROP TABLE #Profissionais_Skills
CREATE TABLE #Profissionais_Skills (
    Id_Pessoa INT,
    Id_Habilidade INT
)

INSERT INTO #Profissionais_Skills ( Id_Pessoa, Id_Habilidade )
VALUES
    (1, 1), (1, 2), (1, 3), (1, 5), (1, 10), (1, 11)

And having the following configuration:

In other words, I related the 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 visualization of the information is very easy to search and filter. Regarding performance, we can create an index to facilitate searches, if necessary.

If I want to identify who are the people who have a certain Skill, just filter by the ID of the Skill I want:

Scenario 2 – Using bitwise operations

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

For this to be 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... lol):

ALTER TABLE #Profissionais ADD Ids_Skill INT

Furthermore, our Skills structure will need some changes, because I need the IDs to work as powers of 2, that is, 1, 2, 4, 8, 16, 32, 64, 128...

ALTER TABLE #Skills ADD Id_Habilidade_Nova AS (POWER(2, Id_Habilidade - 1))

Result:

Using this new column created (Id_Skill_New), Skills will now be associated with people adding the skill IDs and storing the total value in the Ids_Skill column of the #Professionals table.

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

UPDATE #Profissionais
SET Ids_Skill = (1 + 2 + 4 + 16 + 512 + 1024)
WHERE Id_Pessoa = 1 -- Dirceu

-- 1     SQL
-- 2     C#
-- 4     PHP
-- 8     Java
-- 16    Javascript
-- 32    C
-- 64    R
-- 128   Python
-- 256   GO
-- 512   HTML
-- 1024  CSS

Of course, in this bitwise approach, management ends up being much more laborious, but using an application interface, this ends up being transparent to the user.

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

DECLARE @Numero INT = (SELECT Ids_Skill FROM #Profissionais WHERE Nome = 'Dirceu')

IF ((1 & @Numero) = 1) PRINT 'Tem SQL'
IF ((2 & @Numero) = 2) PRINT 'Tem C#'
IF ((4 & @Numero) = 4) PRINT 'Tem PHP'
IF ((8 & @Numero) = 8) PRINT 'Tem Java'
IF ((16 & @Numero) = 16) PRINT 'Tem Javascript'
IF ((32 & @Numero) = 32) PRINT 'Tem C'
IF ((64 & @Numero) = 64) PRINT 'Tem R'
IF ((128 & @Numero) = 128) PRINT 'Tem Python'
IF ((256 & @Numero) = 256) PRINT 'Tem GO'
IF ((512 & @Numero) = 512) PRINT 'Tem HTML'
IF ((1024 & @Numero) = 1024) PRINT 'Tem CSS'

Result:

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

SELECT * 
FROM #Profissionais
WHERE ((4 & Ids_Skill) = 4) -- PHP

Result:

If you want to bring a list of people and skills, just as we did in scenario 1 through simple JOINS, well... the task using the 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 of assigning multiple values ​​between 2 entities, I will also demonstrate the advantages and disadvantages of using bitwise operations in this situation.

Advantages

  • It is not necessary to create another table just to store the relationship between the 2 entities

Disadvantages

  • Much more complex to understand
  • Indexing is not efficient, as 1 calculated column would have to be created for each attribute with the bitwise operation and index each calculated column
  • It is more complex to audit each relationship created between the 2 tables, as the same record is updated several 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 that column needs to be recalculated instead of just deleting 1 record
  • Bringing a relationship between people and skills is a very laborious task
  • The creation of a new Skill in the bank could possibly require some changes to the system

As demonstrated above, this solution of using bitwise ends up bringing many 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 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 see you next time!