SQL Server – O que é operação bitwise e como armazenar vários valores na mesma coluna

Hey guys!
Nesse artigo, vou demonstrar a vocês o que é operação bitwise, disponível desde o SQL Server 2005, e como armazenar vários valores distintos na mesma coluna, que podem até ter combinação de valores, sem a necessidade de ter que criar uma tabela N:N para armazenar as combinações.

Já vi em alguns sistemas essa abordagem de utilizar operações bitwise para armazenar vários valores na mesma coluna e sempre me perguntei porque não criar uma tabela N:N para isso, pois é bem mais simples e permite indexação ainda. Após tirar algumas dúvidas no Telegram sobre operações bitwise, resolvi escrever esse artigo para demonstrar essas duas formas de implementar um relacionamento multivalorado entre duas tabelas, embora não seja um cenário muito comum de se encontrar.

Num cenário rápido para exemplificar uma utilização interessante para esse recurso, vamos imaginar uma estrutura para armazenar quais tecnologias cada pessoa domina, podendo ser uma só ou várias tecnologias para mesma pessoa.

Script para criação das tabelas de exemplo:

Estrutura proposta:

Cenário 1 – Utilizando tabela N:N

Quando você tem a necessidade de uma entidade possuir mais de um valor, uma estratégia muito utilizada é criar uma tabela N:N para relacionar as 2 entidades:

E ficando com a seguinte configuração:

Ou seja, relacionei o ID das pessoas com o ID da habilidade. Para cada combinação desejada, iremos adicionar um registro para criar essa relação entre as 2 entidades. Para essa solução, foi necessário criar uma nova tabela para esse relacionamento, mas a visualização da informação fica bem fácil de buscar e filtrar. No que tange a performance, podemos criar um índice para facilitar as buscas, caso necessário.

Se eu quiser identificar quem são as pessoas que possuem uma determinada Skill, basta filtrar pelo ID da Skill que eu quero:

Cenário 2 – Utilizando operações bitwise

Entrando no mundo das operações bitwise, podemos criar uma solução para armazenar vários valores sem a necessidade de criar uma tabela de N:N para isso, armazenando os múltiplos valores na mesma coluna (e sem a gambiarra de dados serializados).

Para que isso seja possível, antes de mais nada, preciso criar uma nova coluna na tabela de Profissionais, para guardar esses múltiplos valores. E será uma coluna do tipo INT (isso mesmo.. rs):

Além disso, a nossa estrutura de Skills vai precisar de algumas alterações, porque eu preciso que os ID’s funcionem na forma potências de 2, ou seja, 1, 2, 4, 8, 16, 32, 64, 128…

Result:

Utilizando essa nova coluna criada (Id_Habilidade_Nova), agora as Skills serão associadas às pessoas somando os ID’s das habilidades e armazenando o valor total na coluna Ids_Skill da tabela #Profissionais.

Exemplo:
Neste exemplo, vou associar as mesmas Skills do cenário 1 (SQL, C#, PHP, Javascript, HTML e CSS) para a pessoa Dirceu:

É claro que nessa abordagem de bitwise, o gerenciamento acaba ficando bem mais trabalhoso, mas utilizando uma interface de uma aplicação, isso acaba ficando transparente para o usuário.

Para identificar as Skills que essa pessoa possui, podemos utilizar operações bitwise (&), que basicamente faz a comparação binária entre 2 valores:

Result:

E para identificar as pessoas na tabela que possuem a Skill PHP (Id = 3, Id_Habilidade_Nova = 4):

Result:

Caso você queira trazer uma lista com as pessoas e as skills, assim como fizemos no cenário 1 através de simples JOINS, bem.. a tarefa utilizando a arquitetura bitwise será bem mais complexa que isso..

Vantagens e desvantagens da utilização de operações bitwise nessa situação

Após demonstrar as formas mais comuns de atribuir vários valores entre 2 entidades, vou demonstrar também as vantagens e desvantagens da utilização de operações bitwise nessa situação.

Vantagens

• Não é necessário criar mais uma tabela só para armazenar o relacionamento entre as 2 entidades

Desvantagens

• Muito mais complexo de entendimento
• Indexação não é eficiente, pois teria que ser criada 1 coluna calculada para cada atributo com a operação bitwise e indexar cada coluna calculada
• Mais complexo auditar cada criação de relacionamento entre as 2 tabelas, já que o mesmo registro é atualizado várias vezes. No cenário N:N, cada registro poderia ter a data de criação e usuário que criou
• Sempre que um relacionamento for criado/alterado/excluído, o valor dessa coluna precisa ser recalculado ao invés de apenas apagar 1 registro
• Trazer uma relação entre pessoas e skills é uma tarefa bem trabalhosa
• A criação de uma nova Skill no banco possivelmente poderia exigir alguma alteração no sistema

Como demonstrado acima, essa solução de utilizar bitwise acaba trazendo muito mais desvantagens que vantagens (se é que essa “vantagem” é significativa). Eu realmente prefiro a utilização de uma tabela N:N para armazenar relacionamentos multivalorados, tanto por performance quanto por praticidade.

Referências:
– 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/

Bom, espero que tenham gostado desse artigo e tenham aprendido mais uma coisa nova para o dia a dia de vocês 🙂
Um grande abraço e até a próxima!