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