Använda specialiserade tabelltyper

  • 12 minuter

SQL Server stöder specialiserade tabelltyper som utformats för specifika scenarier och arbetsbelastningar utöver standarddiskbaserade tabeller. Dessa tabelltyper, inklusive minnesintern, temporal, extern, LEDGER och GRAPH, löser särskilda prestanda-, efterlevnads- eller arkitekturutmaningar som standardtabeller inte kan hantera effektivt.

Att förstå när och hur du använder dessa specialiserade tabelltyper är avgörande för att utforma effektiva databaslösningar som uppfyller programmets krav.

Använda minnesoptimerade tabeller

Traditionella diskbaserade tabeller medför svarstid från disk-I/O, även med cachelagring. För scenarier som kräver hög hastighet, till exempel tusentals transaktioner per sekund med svarstider på millisekunder, blir diskfördröjning flaskhalsen. Minnesinterna tabeller tar bort detta hinder genom att hålla data helt i RAM med låsfri, optimistisk parallellitet.

Förstå när du ska använda minnesinterna tabeller

Minnesoptimerade tabeller ger betydande prestandafördelar för specifika arbetsbelastningar:

  • Lagring av sessionstillstånd – webbprogram med miljontals samtidiga sessioner
  • Realtidsanalys – Finansiella handelssystem som kräver svarstid på mikrosekunder
  • Högfrekvent OLTP – Orderbearbetningssystem som hanterar över 10 000 transaktioner/sekund
  • Cachelagringslager – Referensdata som används ofta (produktkataloger, konfigurationer)
  • Mellanlagringstabeller – ETL-processer med intensiva infognings-/uppdateringsåtgärder

En e-handelswebbplats använde till exempel minnesinterna tabeller för kundvagnsdata och hanterade 50 000 samtidiga kundvagnar med svarstider för undermillisekunder, vilket minskade svarstiden för kassan med 80%.

Överväg kompromisser

Minnesinterna tabeller lagrar faktiska tabelldata i RAM-minnet för snabbare åtkomst, medan traditionella tabeller lagrar data på disk. Datastorleken begränsas dock av tillgängligt RAM-minne och tabellerna stöder inte stora objekttyper som VARCHAR(MAX), NVARCHAR(MAX)eller VARBINARY(MAX).

Även om tabelldata finns kvar i minnet skriver SQL Server fortfarande transaktionsloggar till disken för att säkerställa hållbarheten. Det innebär att du inte förlorar checkade transaktioner om servern startas om – data återställs från transaktionsloggen tillbaka till minnet.

Du kan skapa en minnesoptimerad tabell med hjälp av alternativet MEMORY_OPTIMIZED = ON . Här är ett exempel:

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

Använda temporala tabeller

Temporala tabeller spårar automatiskt den fullständiga historiken för dataändringar. När du uppdaterar eller tar bort en rad lagrar SQL Server automatiskt den tidigare versionen i en länkad historiktabell med tidsstämplar som visar när den versionen var giltig. Detta sker transparent – du ändrar data med hjälp av normala INSERT, UPDATEoch DELETE -instruktioner, och databasmotorn hanterar versionshantering.

Den viktigaste fördelen är att kunna göra sökningar i data som det existerade vid vilken tidpunkt som helst. Du kan fråga "vad var den här medarbetarens lön den 1 januari 2025?" eller "visa mig alla produkter som fanns i lager förra kvartalet" utan att underhålla komplexa granskningstabeller eller skriva anpassad versionslogik.

Temporala tabeller hanterar efterlevnads-, felsöknings- och analysbehov:

  • Efterlevnad och granskning – Finansiella poster som kräver fullständig ändringshistorik
  • Felsökning – Undersöka kontosaldon vid den tidpunkt då omtvistade transaktioner inträffade
  • Trendanalys – Analysera hur produktpriserna ändrades över kvartal
  • Dataåterställning – Återställa oavsiktliga uppdateringar utan att återställa säkerhetskopior
  • Långsamt föränderliga dimensioner - Typ 2-dimensioner i datalager automatiserade

Vanliga affärsscenarier är program som spårar löneförändringar och kampanjer, lagerhantering som analyserar aktietrender, hälso- och sjukvård som upprätthåller patienthistorik för efterlevnad och policytäckningsändringar för försäkringsspårning för tvistlösning.

Överväg temporala tabellfördelar

Temporala tabeller kräver noll programkodändringar och erbjuder transparent historikspårning. Punkt-i-tid-frågor använder enkel syntax och automatisk rensning hanterar gamla historikdata. Tidstabeller har dock ungefär dubbla lagringskrav.

Temporala tabeller upprätthåller automatiskt en fullständig historik över dataändringar för granskning och analys vid tidpunkt.

Du kan skapa en temporal tabell med hjälp av alternativet SYSTEM_VERSIONING = ON . Temporala tabeller kräver två extra DATETIME2 kolumner för att spåra giltighetsperioden för varje radversion och en PERIOD FOR SYSTEM_TIME sats för att definiera vilka kolumner som spårar dessa tidsstämplar. Här är ett exempel:

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

När du skapar en temporal tabell skapar SQL Server automatiskt en historiktabell för att lagra tidigare radversioner och hanterar båda tabellerna transparent.

Använda externa tabeller

Moderna dataarkitekturer har ofta data utspridda över datasjöar, bloblagring och flera system. Traditionellt skulle du behöva ETL (extrahera, transformera, läsa in) alla data i databasen innan du kör frågor mot den. Med externa tabeller kan datavirtualisering köra frågor mot data där de finns utan att flytta dem, vilket sparar lagringskostnader och ETL-komplexitet.

Förstå när du ska använda externa tabeller

Externa tabeller är bra på att fråga efter data i distribuerade lagringssystem:

  • Data lake-integrering – Fråga Parquet/CSV-filer i Azure Data Lake Storage utan att importera
  • Datautforskning – Analysera rådata innan du bestämmer vad du ska importera
  • Kostnadsoptimering – Undvik att duplicera data som redan lagras någon annanstans
  • Federerade frågor – Koppla databastabeller med filer i externa system
  • Arkiveringslagring – Få åtkomst till historiska data som lagras i billigare bloblagring

Vanliga scenarier är att fråga efter år av loggfiler i datasjöar tillsammans med transaktionsdata, kombinera livedatabasposter med arkiverade bloblagringsdata, komma åt äldre data utan fullständig migrering och köra frågor mot miljontals IoT-sensor-JSON-filer utan att importera.

Överväg prestandabegränsningar

Externa tabeller ger enhetliga frågor mellan källor men har begränsningar:

  • Ingen dataförflyttning eller lagringsduplicering
  • Ofta långsammare än interna tabeller på grund av nätverksfördröjning och filparsning
  • Skrivskyddad (kan inte uppdatera/ta bort i de flesta scenarier)
  • Begränsad indexering och optimering

Du kan skapa en extern tabell med hjälp av -instruktionen CREATE EXTERNAL TABLE med en datakälla och ett filformat. Här är ett exempel:

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

Använda transaktionsregistertabeller

I reglerade branscher är det viktigt att bevisa att data inte har manipulerats. Traditionella databaser kan ha data som ändrats av administratörer, bakåtdaterade ändringar som gjorts eller granskningsloggar borttagna. Transaktionsregistertabeller använder kryptografisk verifiering inspirerad av blockkedjeteknik för att skapa manipuleringssäkrade poster som kan verifieras oberoende av varandra, vilket ger kryptografiska bevis på dataintegritet.

Förstå när du ska använda transaktionsregistertabeller

Transaktionsregistertabeller hanterar krav på regelefterlevnad och kriminalteknisk granskning:

  • Finansiella transaktioner – Bank, betalningsbearbetning, kryptovalutautbyten
  • Leveranskedja – Spåra produktens ursprung, vårdnad och äkthet
  • Juridiska register – Kontrakt, avtal, juridiska ansökningar som kräver oföränderlighet
  • Sjukvård – Receptregister, formulär för patientmedgivande
  • Government – Röstningsregister, landregister, tillståndsutfärdande

En bank kan till exempel använda transaktionsregistertabeller för att lagra transaktionsposter, vilket gör det möjligt för granskare att kontrollera att inga transaktioner har ändrats efter bokföringen. Ett leveranskedjeföretag kan spåra produkt proveniens med hjälp av transaktionsregistertabeller, vilket ger kunderna ett äkthetsbevis.

Välj mellan uppdaterade och endast tilläggsregister

Transaktionsregistertabeller finns i två typer. Uppdaterade transaktionsregistertabeller tillåter INSERT, UPDATEoch DELETE åtgärder samtidigt som alla ändringar spåras kryptografiskt. Systemet lagrar automatiskt tidigare versioner i en historiktabell, liknande temporala tabeller, men med den extra fördelen med manipuleringssäker verifiering. Tabeller med endast tilläggsregister tillåter endast INSERT-operationer, vilket skapar verkligt oföränderliga dataposter för scenarier som kräver absolut dataintegritet.

Du kan kombinera båda teknikerna genom att skapa tabeller som både är uppdaterade transaktionsregistertabeller och tidstabeller, vilket ger kryptografisk verifiering tillsammans med frågefunktioner för tidpunkt.

Ett läkemedelsföretag använder till exempel registertabeller med endast tillägg för kliniska utvärderingsdata, vilket ger oberoende granskare kryptografiska bevis på att testresultaten inte har ändrats efter inlämningen.

Du kan skapa en huvudbokstabell med hjälp av LEDGER = ON-alternativet. Här är ett exempel:

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

När du skapar en transaktionsregistertabell lägger SQL Server automatiskt till dolda kolumner och skapar stöddatabasobjekt för att spåra den kryptografiska kedjan. Varje radändring genererar en kryptografisk hash som länkar till tidigare åtgärder, vilket skapar en granskningslogg som visar manipulering. Du kan verifiera dataintegriteten med hjälp av inbyggda systemvyer som sys.database_ledger_transactions och procedurer som sp_verify_database_ledger för att verifiera att kryptografikedjan förblir obruten.

Använda graftabeller

Relationsdatabaser utmärker sig på strukturerade data men kämpar med mycket anslutna data som kräver många kopplingar. Att hitta "vänners vänner" eller "produkter relaterade till 3 grader av kategorier" blir komplext med traditionella tabeller. SQL Graph-funktionermodellerar inbyggda noder (entiteter) och kanter (relationer), vilket gör komplexa relationsfrågor enkla och högpresterande.

Diagramtabeller förenklar relationsmodellering men kräver ny syntax för inlärning. De ger intuitiv modellering av anslutna data, enklare frågor för relationsbläddring och bättre prestanda för frågor med flera hopp. Det flexibla schemat hanterar föränderliga relationer. Diagramtabeller har dock en inlärningskurva för MATCH syntax och presterar bäst för läsintensiva relationsfrågor.

En databas kan innehålla flera nod- och kanttabeller som fungerar tillsammans för att modellera dina grafdata. Du definierar vilka tabeller som representerar noder och vilka som representerar kanter baserat på dina datarelationer.

Anmärkning

Diagramtabeller är inte optimala för varje scenario. Undvik dem för enkla föräldra-barn-relationer där främmande nycklar fungerar bra, främst transaktionsdata utan komplexa relationer, eller mycket strukturerade och stabila scheman.

Förstå diagramtabellstrukturen

SQL Graph använder två typer av tabeller för att modellera relationer. Nodtabeller lagrar entiteter och innehåller automatiskt en dold $node_id kolumn som unikt identifierar varje nod. Edge-tabeller lagrar relationer mellan noder och inkluderar dolda kolumner $edge_id, $from_idoch $to_id för att underhålla anslutningar. Med de här specialkolumnerna kan syntaxen MATCH passera relationer effektivt.

Du kan skapa graftabeller med hjälp av syntaxen AS NODE och AS EDGE . Här är ett exempel:

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

När du skapar nod- och kanttabeller hanterar SQL Server automatiskt de dolda systemkolumner som möjliggör effektiva grafblädderingsfrågor.

Varje specialiserad tabelltyp levereras med kompromisser: minnesinterna tabeller behöver RAM-minne, temporala tabeller dubbellagring, externa tabeller lägger till nätverksfördröjning, transaktionsregistertabeller förhindrar borttagning och graftabeller kräver ny syntax. Vi rekommenderar att du väljer rätt tabelltyp under designen eftersom dessa beslut är svåra att ändra efter distributionen.