Utilizar tipos de tabelas especializadas

  • {númeroDeMinutos} minutos

O SQL Server suporta tipos de tabelas especializadas, concebidos para cenários e cargas de trabalho específicas para além das tabelas padrão baseadas em disco. Estes tipos de tabelas, incluindo em memória, temporal, externa, LEDGER e GRAPH, resolvem desafios específicos de desempenho, conformidade ou arquitetura que as tabelas padrão não conseguem resolver de forma eficiente.

Compreender quando e como utilizar estes tipos de tabelas especializadas é crucial para conceber soluções eficazes de bases de dados que respondam aos requisitos da sua aplicação.

Utilizar tabelas otimizadas em memória

Tabelas tradicionais baseadas em disco apresentam latência devido à I/O do disco, mesmo com cache. Para cenários que exigem alta velocidade, como milhares de transações por segundo com tempos de resposta em milissegundos, a latência do disco torna-se o gargalo. As tabelas em memória eliminam isto ao manter os dados inteiramente na RAM com concorrência otimista e sem bloqueios.

Compreender quando usar tabelas em memória

Tabelas otimizadas em memória proporcionam benefícios significativos de desempenho para cargas de trabalho específicas:

  • Armazenamento em estado de sessão - Aplicações web com milhões de sessões simultâneas
  • Análise em tempo real - Sistemas de negociação financeira que requerem latência de microssegundos
  • OLTP de alta frequência - Sistemas de processamento de encomendas que lidam com 10.000+ transações/segundo
  • Camada de cache - Dados de referência frequentemente acedidos (catálogos de produtos, configurações)
  • Tabelas de preparação - processos ETL com operações intensivas de inserção/atualização

Por exemplo, um site de comércio eletrónico usava tabelas em memória para dados de carrinhos de compras, lidando com 50.000 carrinhos simultâneos com tempos de resposta submilissegundos, reduzindo a latência de checkout em 80%.

Considere as compensações

As tabelas em memória armazenam os dados reais da tabela na RAM para um acesso mais rápido, enquanto as tabelas tradicionais armazenam os dados no disco. No entanto, o tamanho dos dados é limitado pela RAM disponível, e estas tabelas não suportam tipos grandes de objetos como VARCHAR(MAX), NVARCHAR(MAX), ou VARBINARY(MAX).

Apesar de os dados das tabelas estarem na memória, o SQL Server continua a escrever registos de transações no disco para garantir a durabilidade. Isto significa que não perderás transações comprometidas se o servidor reiniciar — os dados são recuperados do registo de transações de volta para a memória.

Pode criar uma tabela otimizada em memória usando a MEMORY_OPTIMIZED = ON opção. Eis um exemplo:

-- Create in-memory optimized table
CREATE TABLE dbo.OrderCache (
    OrderID INT PRIMARY KEY NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME2,
    Amount DECIMAL(10,2),
    INDEX IX_CustomerID NONCLUSTERED (CustomerID)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Utilizar tabelas temporais

As tabelas temporais registam automaticamente o histórico completo das alterações de dados. Quando atualizas ou apagas uma linha, o SQL Server armazena automaticamente a versão anterior numa tabela de histórico ligada com carimbos temporais que indicam quando essa versão foi válida. Isto acontece de forma transparente — modifica-se os dados usando as instruções normais INSERT, UPDATE, and DELETE e o motor da base de dados trata da versionação.

O principal benefício é consultar dados tal como existiam em qualquer momento. Pode perguntar "qual era o salário deste funcionário a 1 de janeiro de 2025?" ou "mostre-me todos os produtos que estavam em stock no último trimestre" sem manter tabelas de auditoria complexas ou escrever uma lógica de versão personalizada.

As tabelas temporais servem necessidades de conformidade, resolução de problemas e análise:

  • Conformidade e auditoria - Registos financeiros que exigem histórico completo de alterações
  • Resolução de problemas - Investigar saldos de contas no momento em que ocorreram transações contestadas
  • Análise de tendências - Análise de como os preços dos produtos mudaram ao longo dos trimestres
  • Recuperação de dados - Reverter atualizações acidentais sem restaurar backups
  • Dimensões - Dimensões de armazém de dados Tipo 2 automatizadas

Cenários empresariais comuns incluem aplicações que acompanham alterações salariais e promoções, gestão de inventário que analisa tendências de stock, manutenção de históricos de registos de pacientes para conformidade no setor de saúde, e acompanhamento de alterações na cobertura das apólices para resolução de litígios.

Considere os benefícios da tabela temporal

As tabelas temporais não requerem alterações no código da aplicação e oferecem um rastreio de histórico transparente. As consultas pontuais utilizam uma sintaxe simples, e a limpeza automática gere automaticamente os dados de histórico antigo. No entanto, as tabelas temporais aproximadamente duplicam os requisitos de armazenamento.

As tabelas temporais mantêm automaticamente um histórico completo das alterações de dados para auditoria e análise pontual no tempo.

Pode criar uma tabela temporal usando a SYSTEM_VERSIONING = ON opção. As tabelas temporais requerem duas colunas extra DATETIME2 para acompanhar o período de validade de cada versão da linha e uma PERIOD FOR SYSTEM_TIME cláusula para definir quais as colunas que registam esses carimbos temporais. Eis um exemplo:

-- Create temporal table with automatic history tracking
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Department NVARCHAR(50),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2026-01-01' 
WHERE EmployeeID = 1;

Quando cria uma tabela temporal, o SQL Server cria automaticamente uma tabela de histórico para armazenar versões anteriores das linhas e gere ambas as tabelas de forma transparente.

Utilizar tabelas externas

As arquiteturas de dados modernas frequentemente têm dados dispersos entre datalakes, armazenamento de blobs e múltiplos sistemas. Tradicionalmente, teria de realizar ETL (extrair, transformar, carregar) de todos os dados na sua base de dados antes de os consultar. Tabelas externas permitem a virtualização de dados para consultar os dados onde estão sem os mover, poupando custos de armazenamento e complexidade ETL.

Compreenda quando usar tabelas externas

As tabelas externas são excelentes na consulta de dados em sistemas de armazenamento distribuído:

  • Integração de data lake - Consultar arquivos Parquet/CSV no Azure Data Lake Storage sem necessidade de importar
  • Exploração de dados - Analise dados brutos antes de decidir o que importar
  • Otimização de custos - Evite duplicar dados que já estão armazenados noutro local
  • Consultas federadas - Juntar tabelas de base de dados com ficheiros em sistemas externos
  • Armazenamento arquivístico - Aceder a dados históricos armazenados em armazenamento blob mais barato

Cenários comuns incluem consultar anos de ficheiros de registo em data lakes juntamente com dados transacionais, combinar registos de base de dados em tempo real com dados arquivados de armazenamento de blobs, aceder a dados legados sem migração completa e consultar milhões de ficheiros JSON de sensores IoT sem importação.

Considere as limitações de desempenho

Tabelas externas permitem consultas unificadas entre fontes, mas têm limitações:

  • Sem movimentação de dados ou duplicação de armazenamento
  • Frequentemente mais lentas do que as tabelas nativas devido à latência da rede e à análise de ficheiros
  • Só leitura (não é possível atualizar/eliminar na maioria dos cenários)
  • Indexação e otimização limitadas

Pode criar uma tabela externa usando a CREATE EXTERNAL TABLE instrução com uma fonte de dados e formato de ficheiro. Eis um exemplo:

-- Create external table pointing to data lake
CREATE EXTERNAL TABLE dbo.ExternalSalesData (
    OrderID INT,
    CustomerID INT,
    OrderAmount DECIMAL(10,2),
    OrderDate DATE
) WITH (
    LOCATION = '/raw/sales/',
    DATA_SOURCE = DataLakeSource,
    FILE_FORMAT = ParquetFormat
);

Utilizar tabelas de registo

Em indústrias reguladas, provar que os dados não foram alterados é importante. As bases de dados tradicionais podem ter dados modificados por administradores, alterações retroativas feitas ou registos de auditoria eliminados. As tabelas de registo utilizam verificação criptográfica inspirada na tecnologia blockchain para criar registos à prova de adulteração que podem ser verificados de forma independente, fornecendo provas criptográficas da integridade dos dados.

Compreenda quando usar tabelas de registo

As tabelas de registo servem necessidades de conformidade regulatória e auditoria forense:

  • Transações financeiras - Banca, processamento de pagamentos, exchanges de criptomoedas
  • Cadeia de abastecimento - Rastreio da origem, custódia e autenticidade do produto
  • Registos legais - Contratos, acordos, documentos legais que exigem imutabilidade
  • Cuidados de saúde - Registos de prescrições, formulários de consentimento do paciente
  • Governo - Registos de votação, registos de terras, emissão de licenças

Por exemplo, um banco pode usar tabelas de livro-razão para armazenar registos de transações, permitindo aos auditores verificar que nenhuma transação foi alterada após a publicação. Uma empresa da cadeia de abastecimento pode acompanhar a proveniência do produto através de tabelas de registo, fornecendo aos clientes provas de autenticidade.

Escolha entre livros-razão atualizáveis e apenas adicionáveis

As tabelas de registo existem em dois tipos. As tabelas de registo atualizáveis permitem INSERT, UPDATE, e DELETE operações enquanto acompanham todas as alterações criptograficamente. O sistema armazena automaticamente as versões anteriores numa tabela de histórico, semelhante às tabelas temporais, mas com o benefício adicional de verificação à prova de adulterações. As tabelas de livro-razão apenas adicionáveis permitem INSERT operações, criando registos verdadeiramente imutáveis para cenários que requerem integridade absoluta dos dados.

Pode combinar ambas as tecnologias criando tabelas que são ao mesmo tempo tabelas de registo atualizáveis e tabelas temporais, ganhando verificação criptográfica juntamente com capacidades de consulta pontual no tempo.

Por exemplo, uma empresa farmacêutica utiliza tabelas de registo somente anexadas para os dados de ensaios clínicos, fornecendo aos auditores independentes prova criptográfica de que os resultados dos testes não foram alterados após a submissão.

Podes criar uma tabela de registos usando a LEDGER = ON opção. Eis um exemplo:

-- Create ledger table
CREATE TABLE dbo.FinancialTransaction (
    TransactionID INT PRIMARY KEY IDENTITY,
    AccountNumber NVARCHAR(20),
    Amount DECIMAL(15,2),
    TransactionType NVARCHAR(20)
) WITH (LEDGER = ON);

-- Append-only ledger provides immutability
CREATE TABLE dbo.AuditLog (
    LogID INT PRIMARY KEY IDENTITY,
    EventDescription NVARCHAR(500),
    EventTimestamp DATETIME2
) WITH (LEDGER = ON, APPEND_ONLY = ON);

Quando cria uma tabela de registo, o SQL Server adiciona automaticamente colunas ocultas e cria objetos de base de dados de suporte para acompanhar a cadeia criptográfica. Cada modificação de linha gera um hash criptográfico que se liga a operações anteriores, criando um rasto de auditoria que evidencia adulterações. Pode verificar a integridade dos dados usando vistas de sistema incorporadas como sys.database_ledger_transactions e procedimentos como sp_verify_database_ledger para validar que a cadeia criptográfica permanece intacta.

Utilização de tabelas de grafos

As bases de dados relacionais destacam-se em dados estruturados, mas têm dificuldades com dados altamente conectados que requerem muitas junções. Encontrar "amigos de amigos" ou "produtos relacionados por meio de três níveis de categorias" torna-se complexo com o uso de tabelas tradicionais. O SQL Graph pode modelar nativamente nós (entidades) e arestas (relações), tornando consultas complexas de relações simples e eficazes.

As tabelas de grafos simplificam a modelação de relações, mas exigem aprender nova sintaxe. Proporcionam uma modelação intuitiva de dados conectados, interrogações mais simples para a navegação de relações e melhor desempenho para interrogações de múltiplos saltos. O esquema flexível acomoda relações em evolução. No entanto, as tabelas de grafos têm uma curva de aprendizagem da sintaxe MATCH e têm melhor desempenho em consultas de relacionamento pesadas em leitura.

Uma base de dados pode conter múltiplas tabelas de nós e arestas que trabalham em conjunto para modelar os dados do seu grafo. Defines quais as tabelas que representam nós e quais as arestas com base nas tuas relações com os dados.

Observação

As tabelas gráficas não são ótimas para todos os cenários. Evite-os para relações simples de pai e filho onde chaves estrangeiras funcionam bem, principalmente dados transacionais sem relações complexas, ou esquemas estáveis e altamente estruturados.

Compreender a estrutura de tabelas de grafos

O SQL Graph utiliza dois tipos de tabelas para modelar relações. As tabelas de nós armazenam entidades e incluem automaticamente uma coluna oculta $node_id que identifica de forma única cada nó. As tabelas de arestas armazenam relações entre nós e incluem colunas $edge_id, $from_id, e $to_id, ocultas, para manter as ligações. Estas colunas especiais permitem que a MATCH sintaxe percorra relações de forma eficiente.

Pode criar tabelas gráficas usando a sintaxe AS NODE e AS EDGE. Eis um exemplo:

-- Create graph tables
CREATE TABLE Person AS NODE;
CREATE TABLE Manages AS EDGE;
CREATE TABLE Knows AS EDGE;

-- Insert nodes
INSERT INTO Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

-- Insert edges (relationships)
INSERT INTO Manages VALUES (1, 2), (2, 3);

-- Query relationships
SELECT Person1.name, Person2.name 
FROM Person AS Person1, Manages, Person AS Person2
WHERE MATCH (Person1-(Manages)->Person2)
AND Person1.id = 1;

Quando cria tabelas de nós e arestas, o SQL Server gere automaticamente as colunas ocultas do sistema que permitem consultas eficientes de percurso de grafos.

Cada tipo de tabela especializada tem compromissos: as tabelas em memória RAM necessitam de RAM, as tabelas temporais necessitam de armazenamento duplo, tabelas externas adicionam latência de rede, tabelas de registo impedem a eliminação, e tabelas de grafos requerem nova sintaxe. Recomendamos escolher o tipo de mesa certo durante o design, pois estas decisões são difíceis de alterar após a implementação.