Usare tipi di tabella specializzati

SQL Server supporta tipi di tabella specializzati progettati per scenari e carichi di lavoro specifici oltre alle tabelle standard basate su disco. Questi tipi di tabella, inclusi in memoria, temporali, esterni, LEDGER e GRAPH, risolvono particolari problemi di prestazioni, conformità o architettura che le tabelle standard non possono affrontare in modo efficiente.

Comprendere quando e come usare questi tipi di tabella specializzati è fondamentale per progettare soluzioni di database efficaci che soddisfino i requisiti dell'applicazione.

Usare tabelle ottimizzate per la memoria

Le tabelle tradizionali basate su disco comportano latenza dall'I/O del disco, anche con la memorizzazione nella cache. Per gli scenari che richiedono velocità elevata, ad esempio migliaia di transazioni al secondo con tempi di risposta in millisecondi, la latenza del disco diventa il collo di bottiglia. Le tabelle in memoria eliminano questo problema mantenendo i dati interamente in RAM, senza blocchi, e con concorrenza ottimistica.

Comprendere quando usare le tabelle in memoria

Le tabelle ottimizzate per la memoria offrono vantaggi significativi in termini di prestazioni per carichi di lavoro specifici:

• Archiviazione dello stato della sessione - Applicazioni Web con milioni di sessioni simultanee
• Analisi in tempo reale - Sistemi di trading finanziari che richiedono latenza microseconda
• OLTP ad alta frequenza - Sistemi di elaborazione degli ordini che gestiscono 10.000 transazioni al secondo
• Livello di memorizzazione nella cache : dati di riferimento a cui si accede di frequente (cataloghi di prodotti, configurazioni)
• Tabelle di staging - Processi ETL con operazioni di inserimento/aggiornamento intensive

Ad esempio, un sito di e-commerce usato tabelle in memoria per i dati del carrello acquisti, gestendo 50.000 carrelli simultanei con tempi di risposta submillisecond, riducendo la latenza di checkout di 80%.

Prendere in considerazione i compromessi

Le tabelle in memoria archiviano i dati effettivi della tabella in RAM per un accesso più rapido, mentre le tabelle tradizionali archiviano i dati su disco. Tuttavia, le dimensioni dei dati sono limitate dalla RAM disponibile e queste tabelle non supportano tipi di oggetti di grandi dimensioni come VARCHAR(MAX), NVARCHAR(MAX)o VARBINARY(MAX).

Anche se i dati della tabella si trovano in memoria, SQL Server scrive comunque i log delle transazioni su disco per garantire la durabilità. Ciò significa che non si perderanno le transazioni di cui è stato eseguito il commit se il server viene riavviato, ovvero i dati vengono recuperati dal log delle transazioni in memoria.

È possibile creare una tabella ottimizzata per la memoria usando l'opzione MEMORY_OPTIMIZED = ON . Ecco un esempio:

-- 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);

Usare le tabelle temporali

Le tabelle temporali tengono automaticamente traccia della cronologia completa delle modifiche ai dati. Quando si aggiorna o si elimina una riga, SQL Server archivia automaticamente la versione precedente in una tabella di cronologia collegata con timestamp visualizzati quando tale versione è valida. Ciò avviene in modo trasparente: si modificano i dati usando le normali INSERTistruzioni , UPDATEe DELETE il motore di database gestisce il controllo delle versioni.

Il vantaggio principale è l'esecuzione di query sui dati esistenti in qualsiasi momento. È possibile chiedere "qual è stato lo stipendio di questo dipendente il 1° gennaio 2025?" o "mostrami tutti i prodotti che erano in magazzino ultimo trimestre" senza mantenere tabelle di controllo complesse o scrivere logica di controllo personalizzata.

Le tabelle temporali servono le esigenze di conformità, risoluzione dei problemi e analisi:

• Conformità e controllo - Record finanziari che richiedono la cronologia delle modifiche completa
• Risoluzione dei problemi - Analisi dei saldi del conto al momento in cui si sono verificate transazioni contestate
• Analisi delle tendenze - Analisi del modo in cui i prezzi dei prodotti sono cambiati nei trimestri
• Ripristino dei dati - Ripristino degli aggiornamenti accidentali senza ripristinare i backup
• Dimensioni a cambiamento lento - Dimensioni di tipo 2 del data warehouse automatizzate

Gli scenari aziendali comuni includono le applicazioni che monitorano le modifiche degli stipendi e le promozioni, la gestione dell'inventario analizzando le tendenze delle scorte, il mantenimento della cronologia delle cartelle cliniche dei pazienti per garantire la conformità, e il monitoraggio delle modifiche alla copertura delle polizze assicurative per la risoluzione delle controversie.

Prendere in considerazione i vantaggi della tabella temporale

Le tabelle temporali richiedono zero modifiche al codice dell'applicazione e offrono il rilevamento della cronologia trasparente. Le query puntuali usano una sintassi semplice e la pulizia automatica gestisce i vecchi dati di cronologia. Tuttavia, le tabelle temporali raddoppiano approssimativamente i requisiti di archiviazione.

Le tabelle temporali mantengono automaticamente una cronologia completa delle modifiche ai dati per il controllo e l'analisi temporizzato.

È possibile creare una tabella temporale usando l'opzione SYSTEM_VERSIONING = ON . Le tabelle temporali richiedono due colonne aggiuntive DATETIME2 per tenere traccia del periodo di validità di ogni versione di riga e una PERIOD FOR SYSTEM_TIME clausola per definire quali colonne tengono traccia di questi timestamp. Ecco un esempio:

-- 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 si crea una tabella temporale, SQL Server crea automaticamente una tabella di cronologia per archiviare le versioni di riga precedenti e gestisce entrambe le tabelle in modo trasparente.

Usare tabelle esterne

Le architetture di dati moderne hanno spesso dati sparsi in data lake, BLOB storage e più sistemi. In genere, è necessario ETL (estrarre, trasformare, caricare) tutti i dati nel database prima di eseguirne query. Le tabelle esterne consentono alla virtualizzazione dei dati di eseguire query sui dati in cui si trovano senza spostarli, risparmiando i costi di archiviazione e la complessità ETL.

Comprendere quando usare tabelle esterne

Le tabelle esterne eccellono nell'interrogare i dati su sistemi di archiviazione distribuiti.

• Integrazione di Data Lake - Eseguire query su file Parquet/CSV in Azure Data Lake Storage senza importare
• Esplorazione dei dati - Analizzare i dati non elaborati prima di decidere cosa importare
• Ottimizzazione dei costi : evitare la duplicazione dei dati già archiviati altrove
• Query federate - Unire tabelle di database con file in sistemi esterni
• Archiviazione dati storici - Accedere ai dati storici memorizzati in un archivio BLOB più economico

Gli scenari comuni includono l'esecuzione di query su anni di file di log nei data lake insieme a dati transazionali, la combinazione di record di database live con dati BLOB archiviati, l'accesso ai dati legacy senza una migrazione completa e l'esecuzione di query su milioni di file JSON di sensori IoT senza importazione.

Prendere in considerazione i vincoli di prestazioni

Le tabelle esterne forniscono query unificate tra origini, ma presentano limitazioni:

• Nessuna duplicazione dello spostamento o dell'archiviazione dei dati
• Spesso più lento rispetto alle tabelle native a causa della latenza di rete e dell'analisi dei file
• Sola lettura (non può aggiornare/eliminare nella maggior parte degli scenari)
• Indicizzazione e ottimizzazione limitate

È possibile creare una tabella esterna usando l'istruzione CREATE EXTERNAL TABLE con un'origine dati e un formato di file. Ecco un esempio:

-- 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
);

Usare le tabelle del libro mastro

Nei settori regolamentati, la dimostrazione che i dati non sono stati manomessi è importante. I database tradizionali possono avere dati modificati dagli amministratori, modifiche retroattive apportate o log di verifica eliminati. Le tabelle libro mastro usano la verifica crittografica ispirata dalla tecnologia blockchain per creare record evidenti delle manomissioni che possono essere verificati in modo indipendente, fornendo una prova crittografica dell'integrità dei dati.

Comprendere quando usare le tabelle del libro mastro

Le tabelle libro mastro servono le esigenze di conformità alle normative e di controllo forense:

• Transazioni finanziarie - Banche, elaborazione dei pagamenti, scambi di criptovaluta
• Supply chain - Rilevamento dell'origine, custodia e autenticità dei prodotti
• Documenti legali - Contratti, contratti, archiviazioni legali che richiedono immutabilità
• Assistenza sanitaria - Record di prescrizione, moduli di consenso dei pazienti
• Governo - Record di voti, registri territoriali, rilascio di permessi

Ad esempio, una banca può utilizzare tabelle libro mastro per archiviare i record delle transazioni, consentendo ai revisori di verificare che nessuna transazione sia stata modificata dopo la registrazione. Un'azienda della catena di approvvigionamento può tenere traccia della provenienza del prodotto usando tabelle mastro, fornendo ai clienti la prova dell'autenticità.

Scegliere tra registri aggiornabili e di solo accodamento

Esistono due tipi di tabelle libro mastro. Le tabelle libro mastro aggiornabili consentono l'esecuzione delle operazioni INSERT, UPDATE e DELETE, tracciando tutte le modifiche in modo crittografico. Il sistema archivia automaticamente le versioni precedenti in una tabella di cronologia, simile alle tabelle temporali, ma con il vantaggio aggiunto della verifica a prova di manomissione. Le tabelle libro mastro di solo accodamento consentono solo operazioni INSERT creando record veramente immutabili per scenari che richiedono l'integrità assoluta dei dati.

È possibile combinare entrambe le tecnologie creando tabelle che sono sia tabelle di registro aggiornabili sia tabelle temporali, ottenendo la verifica crittografica insieme alle funzionalità di query nello specifico momento.

Ad esempio, una società farmaceutica utilizza tabelle libro mastro di solo accodamento per i dati delle sperimentazioni cliniche, al fine di fornire ai revisori indipendenti una prova crittografica che i risultati non sono stati modificati dopo l'invio.

È possibile creare una tabella del libro mastro utilizzando l'opzione LEDGER = ON. Ecco un esempio:

-- 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 si crea una tabella libro mastro, SQL Server aggiunge automaticamente colonne nascoste e crea oggetti di database di supporto per tenere traccia della catena di crittografia. Ogni modifica di riga genera un hash crittografico che si collega alle operazioni precedenti, creando un registro di audit resistente alle manomissioni. È possibile verificare l'integrità dei dati usando viste di sistema predefinite, ad esempio sys.database_ledger_transactions e procedure, ad esempio sp_verify_database_ledger per convalidare che la catena di crittografia rimanga non interrotta.

Usare le tabelle a grafo

I database relazionali sono eccellenti in dati strutturati, ma hanno difficoltà con i dati altamente connessi che richiedono molti join. Trovare "amici di amici" o "prodotti correlati attraverso 3 gradi di categorie" diventa complesso con tabelle tradizionali. Le funzionalità di SQL Graph modellano nativamente nodi (entità) e archi (relazioni), rendendo semplici ed efficienti le query di relazione complesse.

Le tabelle graph semplificano la modellazione delle relazioni, ma richiedono l'apprendimento di una nuova sintassi. Offrono una modellazione intuitiva dei dati connessi, query più semplici per l'attraversamento delle relazioni e prestazioni migliori per le query multi hop. Lo schema flessibile supporta relazioni in continua evoluzione. Tuttavia, le tabelle dei grafi hanno una curva di apprendimento per la sintassi MATCH e offrono prestazioni ottimali per le query dei rapporti ad alto carico di lettura.

Un database può contenere più tabelle di nodi e bordi che interagiscono per modellare i dati del grafo. Si definiscono le tabelle che rappresentano i nodi e che rappresentano i bordi in base alle relazioni tra i dati.

Capire la struttura della tabella dei grafici

SQL Graph usa due tipi di tabelle per modellare le relazioni. Le tabelle node archiviano le entità e includono automaticamente una colonna nascosta $node_id che identifica in modo univoco ogni nodo. Le tabelle edge archiviano le relazioni tra nodi e includono colonne $edge_idnascoste , $from_ide $to_id per mantenere le connessioni. Queste colonne speciali consentono alla MATCH sintassi di attraversare le relazioni in modo efficiente.

È possibile creare tabelle a grafo usando la sintassi AS NODE e AS EDGE. Ecco un esempio:

-- 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 si creano tabelle nodo e perimetrali, SQL Server gestisce automaticamente le colonne di sistema nascoste che consentono query di attraversamento del grafo efficienti.

Ogni tipo di tabella specializzato è dotato di compromessi: le tabelle in memoria necessitano di RAM, tabelle temporali doppio spazio di archiviazione, tabelle esterne aggiungono latenza di rete, tabelle libro mastro impediscono l'eliminazione e le tabelle del grafo richiedono una nuova sintassi. È consigliabile scegliere il tipo di tabella corretto durante la progettazione perché queste decisioni sono difficili da modificare dopo la distribuzione.