Série: Specialized Tables no SQL Server 2025

SQLCrespi

6 horas atrás

Sobre a série

São 5 posts independentes, um por tipo de specialized table do SQL Server 2025, pensados como material de estudo para a DP-800. Cada post tem introdução, conceito, laboratório reproduzível (mão na massa), pegadinhas, notas para a prova e conclusão.

Parte	Tema	Versão Mínima	Ideia Central
1	In-Memory OLTP	SQL Server 2014	Tabela que vive na memória, lock-free e latch-free
2	Temporal Table	SQL Server 2016	Histórico automático de cada linha
3	External Tables	SQL Server 2016 (Polybase)	Consultar dados que estão fora do banco
4	Ledger Tables	SQL Server 2022	prova criptográfica de que ninguém adulterou os dados
5	Graph Tables	SQL Server 2017	Nós e arestas para modelar relacionamentos

Pré-requisitos para os labs: uma instância SQL Server 2025 (Developer Edition serve) e o SSMS 21 ou o VS Code com a extensão mssql. Todos os scripts criam e usam o banco DP800_Lab, então rode o bloco abaixo uma vez antes de começar.

Pré-requisitos para os labs: uma instância SQL Server 2025 (Developer Edition serve) e o SSMS 21 ou o VS Code com a extensão mssql. Todos os scripts criam e usam o banco DP800_Lab, então rode o bloco abaixo uma vez antes de começar.

-- Cria o banco de laboratório usado em todos os posts da série
USE master;
GO
IF DB_ID('DP800_Lab') IS NOT NULL
BEGIN
    ALTER DATABASE DP800_Lab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DP800_Lab;
END
GO
CREATE DATABASE DP800_Lab;
GO
USE DP800_Lab;
GO

Parte 1 — Specialized Tables no SQL Server 2025: In-Memory OLTP

Introdução

Abrindo a série sobre specialized tables para a DP-800, começamos pela mais “veloz” de todas: as memory-optimized tables do In-Memory OLTP (o antigo codinome Hekaton). A promessa é simples: tirar a tabela do modelo baseado em páginas de 8 KB e colocá-la inteira na memória, sem locks e sem latches. Em cenários de alta concorrência, como fila de pedidos, session state ou ingestão de IoT, o ganho pode ser de várias vezes.

Como funciona

Uma memory-optimized table usa row versioning otimista (MVCC): cada UPDATE gera uma nova versão da linha, e conflitos são detectados no commit, não com bloqueio. Os índices também vivem só em memória e são reconstruídos quando a instância sobe.

DURABILITY = SCHEMA_AND_DATA: o padrão. Os dados são persistidos via checkpoint file pairs e log, e sobrevivem a um restart.
DURABILITY = SCHEMA_ONLY: só a estrutura sobrevive. Ótimo para staging e cache, porque não gera log de dados.
HASH index: excelente para busca por igualdade (WHERE Id = @Id). Exige BUCKET_COUNT, idealmente 1 a 2 vezes o número de valores distintos.
NONCLUSTERED index (Bw-tree): para range scans e ORDER BY.
Natively compiled stored procedures: o T-SQL é compilado para código de máquina (DLL), eliminando boa parte do custo de interpretação.

Mão na massa

USE DP800_Lab;
GO

-- 1. Toda tabela memory-optimized exige um filegroup MEMORY_OPTIMIZED_DATA
ALTER DATABASE DP800_Lab
    ADD FILEGROUP FG_InMemory CONTAINS MEMORY_OPTIMIZED_DATA;
GO

-- Ajuste o caminho para uma pasta existente na sua máquina
ALTER DATABASE DP800_Lab
    ADD FILE (NAME = 'DP800_InMemory', FILENAME = 'C:\SQLData\DP800_InMemory')
    TO FILEGROUP FG_InMemory;
GO

-- 2. Tabela durável (SCHEMA_AND_DATA) com HASH index na PK
CREATE TABLE dbo.Pedido
(
    PedidoId     INT IDENTITY(1,1) NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1048576),
    ClienteId    INT          NOT NULL,
    Valor        DECIMAL(10,2) NOT NULL,
    DataPedido   DATETIME2    NOT NULL DEFAULT SYSUTCDATETIME(),

    -- Índice Bw-tree para consultas por intervalo de data
    INDEX IX_Pedido_Data NONCLUSTERED (DataPedido)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

-- 3. Tabela não durável (SCHEMA_ONLY): os dados somem no restart
CREATE TABLE dbo.SessaoCache
(
    SessaoId   UNIQUEIDENTIFIER NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 131072),
    Payload    NVARCHAR(4000)   NULL,
    ExpiraEm   DATETIME2        NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

-- 4. Natively compiled stored procedure para inserir pedidos
CREATE OR ALTER PROCEDURE dbo.usp_InserirPedido
    @ClienteId INT,
    @Valor     DECIMAL(10,2)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    INSERT INTO dbo.Pedido (ClienteId, Valor)
    VALUES (@ClienteId, @Valor);
END;
GO

-- 5. Carga de teste: 100 mil pedidos
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    -- EXEC não aceita expressões nos parâmetros, por isso as variáveis
    DECLARE @c INT = @i % 1000,
            @v DECIMAL(10,2) = (@i % 500) + 0.99;
    EXEC dbo.usp_InserirPedido @ClienteId = @c, @Valor = @v;
    SET @i += 1;
END;
GO

-- 6. Conferindo a saúde do HASH index (cadeias longas = BUCKET_COUNT baixo)
SELECT  OBJECT_NAME(hs.object_id)  AS Tabela,
        i.name                     AS Indice,
        hs.total_bucket_count,
        hs.empty_bucket_count,
        hs.avg_chain_length,
        hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs
JOIN sys.indexes AS i
    ON i.object_id = hs.object_id AND i.index_id = hs.index_id;

-- 7. Quanto de memória cada tabela está usando
SELECT  OBJECT_NAME(object_id) AS Tabela,
        memory_allocated_for_table_kb,
        memory_used_by_table_kb
FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id > 0;

WHERE object_id > 0;

Bônus: dá para usar o mesmo motor em memory-optimized table types, substituindo #temp e variáveis de tabela em procedures muito chamadas.

-- Table type em memória: sem tempdb, sem contenção de alocação
CREATE TYPE dbo.tt_ListaIds AS TABLE
(
    Id INT NOT NULL PRIMARY KEY NONCLUSTERED
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @ids dbo.tt_ListaIds;
INSERT INTO @ids (Id) VALUES (1), (2), (3);
SELECT * FROM @ids;

Pegadinhas

O filegroup MEMORY_OPTIMIZED_DATA não pode ser removido depois de criado (só dropando o banco).
Toda memory-optimized table durável precisa de PRIMARY KEY. Não existe clustered index.
Não há suporte a TRUNCATE TABLE, FOREIGN KEY para tabela disk-based ou partitioning.
Em transações cross-container (memória + disco), use WITH (SNAPSHOT) ou MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON no banco.
Conflitos de escrita geram erro 41302 (update conflict): a aplicação precisa de lógica de retry.
No Azure SQL Database, In-Memory OLTP só está disponível nas camadas Premium e Business Critical.

Conclusão

In-Memory OLTP não é botão mágico de performance: ele brilha em cargas OLTP com muita concorrência e transações curtas, e cobra o preço em memória e em restrições de T-SQL. Para a DP-800, foque em quando usar, nos níveis de durabilidade e nos tipos de índice. No próximo post vamos voltar ao disco e falar de uma tabela que guarda o passado: as temporal tables.

-- Limpeza opcional desta parte
DROP PROCEDURE IF EXISTS dbo.usp_InserirPedido;
DROP TABLE IF EXISTS dbo.Pedido, dbo.SessaoCache;
-- O filegroup MEMORY_OPTIMIZED_DATA permanece no banco

Link para os scripts.