Tables SQL Server avec DURABILITY : SCHEMA_ONLY

SQL Server permet de créer des tables optimisées en mémoire (memory-optimized) avec l'option DURABILITY = SCHEMA_ONLY. Ces tables conservent uniquement leur structure après un redémarrage ou un basculement, tandis que les données sont perdues. Cette fonctionnalité élimine la journalisation des transactions et offre des performances exceptionnelles pour les données temporaires ou intermédiaires qui n'ont pas besoin d'être persistées.

Cas d'usage

Tables de calculs intermédiaires

Idéal pour stocker des résultats de calculs temporaires qui n'ont pas besoin d'être conservés au-delà de la session. L'absence de journalisation accélère considérablement les insertions et mises à jour.

-- Create a memory-optimized table with SCHEMA_ONLY durability
CREATE TABLE dbo.TempCalculations (
    Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    Value DECIMAL(18,2) NOT NULL,
    Result DECIMAL(18,2) NULL,
    ProcessedAt DATETIME2 NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

-- Insert data (no logging, extremely fast)
INSERT INTO dbo.TempCalculations (Id, Value, ProcessedAt)
VALUES (1, 100.50, SYSDATETIME());

-- Data is lost on restart/failover
-- Only the table structure persists

Tables de staging pour ETL

Parfait pour les processus d'extraction, transformation et chargement (ETL). Les données sont chargées en mémoire à grande vitesse, transformées, puis transférées vers les tables persistantes. Le staging ne nécessite pas de durabilité.

-- Create a staging table for ETL processes
CREATE TABLE dbo.StagingOrders (
    OrderId INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    INDEX IX_Customer NONCLUSTERED (CustomerId)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

-- Fast bulk insert without transaction log overhead
INSERT INTO dbo.StagingOrders
SELECT OrderId, CustomerId, Amount, OrderDate
FROM ExternalSource;

-- Transform and load into permanent tables
INSERT INTO dbo.Orders
SELECT * FROM dbo.StagingOrders
WHERE Amount > 0;

-- Clear staging data
DELETE FROM dbo.StagingOrders;

Gestion de sessions utilisateur

Excellent pour suivre l'état des sessions utilisateur en temps réel. Les sessions sont par nature éphémères : si le serveur redémarre, les utilisateurs doivent simplement se reconnecter. Les performances ultra-rapides améliorent l'expérience utilisateur.

-- Create a session state table
CREATE TABLE dbo.UserSessions (
    SessionId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED,
    UserId INT NOT NULL,
    LoginTime DATETIME2 NOT NULL,
    LastActivity DATETIME2 NOT NULL,
    SessionData NVARCHAR(MAX) NULL,
    INDEX IX_User NONCLUSTERED HASH (UserId) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

-- Ultra-fast session tracking
INSERT INTO dbo.UserSessions 
VALUES (NEWID(), 12345, SYSDATETIME(), SYSDATETIME(), '{"theme":"dark"}');

-- Update last activity (no disk I/O)
UPDATE dbo.UserSessions
SET LastActivity = SYSDATETIME()
WHERE SessionId = @sessionId;

Résumé

Utilisez DURABILITY = SCHEMA_ONLY lorsque vous avez besoin de performances maximales pour des données temporaires qui n'ont pas besoin de survivre à un redémarrage du serveur. Cette option est particulièrement efficace pour les tables de staging, les caches applicatifs, les calculs intermédiaires et la gestion de sessions.

Avantages

• Performances exceptionnelles : aucune journalisation des transactions, tout en mémoire
• Réduit drastiquement l'utilisation du disque et les I/O
• Parfait pour les données éphémères ou temporaires (staging, cache, sessions)
• La structure de la table persiste, facilitant la récréation automatique des données

Inconvénients

• Perte totale des données en cas de redémarrage ou basculement
• Nécessite SQL Server 2014+ avec l'option In-Memory OLTP activée
• Consomme de la mémoire RAM (à dimensionner correctement)

Bonnes pratiques

• Réservez cette option uniquement aux données qui peuvent être recréées facilement ou qui sont temporaires
• Documentez clairement que les données ne sont pas durables pour éviter toute confusion
• Dimensionnez correctement la mémoire disponible pour éviter les problèmes de ressources
• Utilisez des index HASH avec un BUCKET_COUNT approprié pour optimiser les performances
• Combinez avec DURABILITY = SCHEMA_AND_DATA pour les tables qui nécessitent la persistance