Uso de tipos de tabla especializados
SQL Server admite tipos de tabla especializados diseñados para escenarios y cargas de trabajo específicos más allá de las tablas basadas en disco estándar. Estos tipos de tabla, incluidos en memoria, temporales, externos, LEDGER y GRAPH, resuelven desafíos específicos de rendimiento, cumplimiento o arquitectura que las tablas estándar no pueden abordar de forma eficaz.
Comprender cuándo y cómo usar estos tipos de tabla especializados es fundamental para diseñar soluciones de base de datos eficaces que cumplan los requisitos de la aplicación.
Uso de tablas optimizadas en memoria
Las tablas tradicionales basadas en disco incurren en latencia de E/S de disco, incluso con el almacenamiento en caché. En escenarios que requieren alta velocidad, como miles de transacciones por segundo con tiempos de respuesta medidos en milisegundos, la latencia del disco se convierte en el cuello de botella. Las tablas en memoria eliminan esto manteniendo los datos completamente en RAM con concurrencia optimista y libre de bloqueos.
Comprender cuándo usar tablas en memoria
Las tablas optimizadas en memoria proporcionan ventajas de rendimiento significativas para cargas de trabajo específicas:
- Almacenamiento de estado de sesión : aplicaciones web con millones de sesiones simultáneas
- Análisis en tiempo real : sistemas comerciales financieros que requieren latencia microsegunda
- OLTP de alta frecuencia : sistemas de procesamiento de pedidos que controlan más de 10 000 transacciones por segundo
- Capa de almacenamiento en caché : datos de referencia a los que se accede con frecuencia (catálogos de productos, configuraciones)
- Tablas de almacenamiento provisional : procesos ETL con operaciones intensivas de inserción y actualización
Por ejemplo, un sitio de comercio electrónico usaba tablas en memoria para los datos del carrito de compras, controlando 50.000 carritos simultáneos con tiempos de respuesta por debajo de milisegundos, reduciendo la latencia en el proceso de pago en un 80%.
Considere las ventajas y desventajas
Las tablas en memoria almacenan los datos de tabla reales en RAM para un acceso más rápido, mientras que las tablas tradicionales almacenan datos en el disco. Sin embargo, el tamaño de los datos está limitado por la RAM disponible y estas tablas no admiten tipos de objetos grandes como VARCHAR(MAX), NVARCHAR(MAX)o VARBINARY(MAX).
Aunque los datos de la tabla residen en la memoria, SQL Server sigue escribiendo registros de transacciones en el disco para garantizar la durabilidad. Esto significa que no perderá las transacciones confirmadas si se reinicia el servidor; los datos se recuperan del registro de transacciones en la memoria.
Puede crear una tabla optimizada en memoria mediante la MEMORY_OPTIMIZED = ON opción . Este es un ejemplo:
-- 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);
Uso de tablas temporales
Las tablas temporales realizan un seguimiento automático del historial completo de los cambios de datos. Al actualizar o eliminar una fila, SQL Server almacena automáticamente la versión anterior en una tabla de historial vinculada con marcas de tiempo que muestran cuándo esa versión era válida. Esto sucede de forma transparente: se modifican los datos mediante instrucciones normales INSERT, UPDATEy DELETE el motor de base de datos controla el control de versiones.
La ventaja clave es consultar los datos tal como existían en cualquier momento. Puede preguntar "¿qué era el salario de este empleado el 1 de enero de 2025?" o "mostrarme todos los productos que estaban en stock el último trimestre" sin mantener tablas de auditoría complejas ni escribir lógica de control de versiones personalizada.
Las tablas temporales proporcionan cumplimiento, solución de problemas y necesidades analíticas:
- Cumplimiento y auditoría: registros financieros que requieren historial de cambios completo
- Solución de problemas : investigación de saldos de cuentas en el momento en que se produjeron transacciones controvertidas
- Análisis de tendencias : análisis de cómo cambiaron los precios del producto a lo largo de los trimestres
- Recuperación de datos : reversión de actualizaciones accidentales sin restaurar copias de seguridad
- Dimensiones - Dimensiones automatizadas de tipo 2 de almacenamiento de datos
Entre los escenarios empresariales comunes se incluyen aplicaciones que rastrean los cambios de salario y las promociones, la administración del inventario analizando las tendencias de existencias, el mantenimiento del historial de registros de pacientes para el cumplimiento y los cambios de cobertura de la póliza de seguimiento de seguros para la resolución de conflictos.
Considere las ventajas de las tablas temporales
Las tablas temporales requieren cambios de código de aplicación cero y ofrecen seguimiento de historial transparente. Las consultas a un momento dado usan sintaxis sencilla y la limpieza automática administra los datos antiguos del historial. Sin embargo, las tablas temporales duplican aproximadamente los requisitos de almacenamiento.
Las tablas temporales mantienen automáticamente un historial completo de los cambios de datos para la auditoría y el análisis a un momento dado.
Puede crear una tabla temporal mediante la SYSTEM_VERSIONING = ON opción . Las tablas temporales requieren dos columnas adicionales DATETIME2 para realizar un seguimiento del período de validez de cada versión de fila y una PERIOD FOR SYSTEM_TIME cláusula para definir qué columnas realizan un seguimiento de estas marcas de tiempo. Este es un ejemplo:
-- 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;
Al crear una tabla temporal, SQL Server crea automáticamente una tabla de historial para almacenar versiones de fila anteriores y administra ambas tablas de forma transparente.
Uso de tablas externas
Las arquitecturas de datos modernas suelen tener datos dispersos en lagos de datos, almacenamiento de blobs y varios sistemas. Tradicionalmente, tendrías que ETL (extraer, transformar, cargar) todos los datos en la base de datos antes de consultarlos. Las tablas externas permiten la virtualización de datos para consultar los datos donde reside sin moverlos, lo que ahorra costos de almacenamiento y complejidad de ETL.
Comprender cuándo usar tablas externas
Las tablas externas se destacan en consultar datos a través de sistemas de almacenamiento distribuidos.
- Integración de Data Lake - Consultar archivos Parquet/CSV en Azure Data Lake Storage sin importar
- Exploración de datos : análisis de datos sin procesar antes de decidir qué importar
- Optimización de costos : evite duplicar los datos que ya están almacenados en otro lugar
- Consultas federadas : combinación de tablas de base de datos con archivos en sistemas externos
- Almacenamiento de archivo: acceso a datos históricos almacenados en almacenamiento de datos binarios más barato
Entre los escenarios comunes se incluyen la consulta de años de archivos de registro en lagos de datos junto con datos transaccionales, la combinación de registros de bases de datos activas con datos de almacenamiento de blobs archivados, el acceso a datos heredados sin migración completa y la consulta de millones de archivos JSON del sensor de IoT sin importar.
Tener en cuenta las restricciones de rendimiento
Las tablas externas proporcionan consultas unificadas entre orígenes, pero tienen limitaciones:
- Sin movimiento de datos ni duplicación de almacenamiento
- A menudo, más lenta que las tablas nativas debido a la latencia de red y al análisis de archivos
- Solo lectura (no se puede actualizar o eliminar en la mayoría de los escenarios)
- Indexación y optimización limitadas
Puede crear una tabla externa mediante la CREATE EXTERNAL TABLE instrucción con un origen de datos y un formato de archivo. Este es un ejemplo:
-- 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
);
Uso de tablas de libro de contabilidad
En los sectores regulados, demostrar que los datos no se han manipulado es importante. Las bases de datos tradicionales pueden tener datos modificados por administradores, cambios retroactivos realizados o registros de auditoría eliminados. Las tablas del libro de contabilidad usan la verificación criptográfica inspirada en la tecnología de cadena de bloques para crear registros evidentes de alteraciones que se pueden comprobar de forma independiente, lo que proporciona una prueba criptográfica de la integridad de los datos.
Comprender cuándo usar tablas de libro de contabilidad
Las tablas de libro de contabilidad satisfacen las necesidades de cumplimiento normativo y auditoría forense:
- Transacciones financieras : banca, procesamiento de pagos, intercambios de criptomoneda
- Cadena de suministro : seguimiento del origen, custodia y autenticidad del producto
- Registros legales : contratos, acuerdos, presentaciones legales que requieren inmutabilidad
- Atención sanitaria : registros de prescripción, formularios de consentimiento del paciente
- Gobierno - registros de votación, registros de tierras, emisión de permisos
Por ejemplo, un banco puede usar tablas de libro de contabilidad para almacenar registros de transacciones, lo que permite a los auditores comprobar que no se modificaron transacciones después de la contabilización. Una empresa de cadena de suministro puede realizar un seguimiento de la procedencia del producto mediante tablas de libro de contabilidad, lo que proporciona a los clientes una prueba de autenticidad.
Elegir entre libros de contabilidad actualizables y de solo anexión
Las tablas del libro de contabilidad se incluyen en dos tipos.
Las tablas de contabilidad actualizables permiten INSERT, UPDATE y DELETE operaciones, mientras se realiza un seguimiento criptográfico de todos los cambios. El sistema almacena automáticamente las versiones anteriores en una tabla de historial, similar a las tablas temporales, pero con la ventaja adicional de la comprobación a prueba de alteraciones.
Las tablas del libro de contabilidad de solo anexión solo permiten operaciones INSERT, creando registros verdaderamente inmutables para escenarios que requieren una integridad de datos absoluta.
Puede combinar ambas tecnologías mediante la creación de tablas que son tablas de libro de contabilidad actualizables y tablas temporales, obteniendo la comprobación criptográfica junto con las funcionalidades de consulta a un momento dado.
Por ejemplo, una empresa farmacéutica usa tablas contables de solo anexión para los datos de pruebas clínicas, proporcionando a auditores independientes pruebas criptográficas de que los resultados de pruebas no se modificaron después de su envío.
Puede crear una tabla de libro de contabilidad mediante la LEDGER = ON opción . Este es un ejemplo:
-- 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);
Al crear una tabla de libro de contabilidad, SQL Server agrega automáticamente columnas ocultas y crea objetos de base de datos auxiliares para realizar un seguimiento de la cadena criptográfica. Cada modificación de fila genera un hash criptográfico que se vincula a las operaciones anteriores, creando una pista de auditoría a prueba de alteraciones. Puede comprobar la integridad de los datos mediante vistas del sistema integradas, como sys.database_ledger_transactions y procedimientos como sp_verify_database_ledger para validar que la cadena criptográfica permanece sin desbloquear.
Uso de tablas de grafos
Las bases de datos relacionales se destacan en datos estructurados, pero tienen problemas con datos altamente conectados que requieren muchas combinaciones. Encontrar "amigos de amigos" o "productos relacionados a través de 3 grados de categorías" se vuelve complejo con tablas tradicionales. Las funcionalidades de SQL Graph modela de forma nativa los nodos (entidades) y las aristas (relaciones), simplificando y mejorando el rendimiento de las consultas sobre relaciones complejas.
Las tablas de grafos simplifican el modelado de relaciones, pero requieren aprender una sintaxis nueva. Proporcionan un modelado intuitivo de datos conectados, consultas más sencillas para el recorrido de relaciones y un mejor rendimiento para las consultas de varios saltos. El esquema flexible admite relaciones en evolución. Sin embargo, las tablas de grafos tienen una curva de aprendizaje para MATCH sintaxis y funcionan mejor para consultas de relaciones que requieren mucha lectura.
Una base de datos puede contener varias tablas perimetrales y de nodo que funcionan conjuntamente para modelar los datos del grafo. Usted define qué tablas representan nodos y cuáles representan aristas en función de sus relaciones de datos.
Nota:
Las tablas de grafos no son óptimas para cada escenario. Evite las relaciones de elementos primarios y secundarios simples donde las claves externas funcionan bien, principalmente para datos transaccionales sin relaciones complejas o esquemas altamente estructurados y estables.
Descripción de la estructura de la tabla de grafos
SQL Graph usa dos tipos de tablas para modelar relaciones.
Las tablas de nodos almacenan entidades e incluyen automáticamente una columna oculta $node_id que identifica de forma única cada nodo.
Las tablas perimetrales almacenan relaciones entre nodos e incluyen columnas ocultas $edge_id, $from_idy $to_id para mantener conexiones. Estas columnas especiales permiten que la MATCH sintaxis recorra las relaciones de forma eficaz.
Puede crear tablas de grafos mediante la AS NODE sintaxis y AS EDGE . Este es un ejemplo:
-- 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;
Al crear tablas perimetrales y de nodo, SQL Server administra automáticamente las columnas ocultas del sistema que permiten consultas de recorrido de grafos eficaces.
Cada tipo de tabla especializado conlleva compensaciones: las tablas en memoria necesitan RAM, las tablas temporales requieren el doble de almacenamiento, las tablas externas agregan latencia de red, las tablas de libro de contabilidad impiden la eliminación y las tablas de grafos requieren una sintaxis nueva. Se recomienda elegir el tipo de tabla correcto durante el diseño, ya que estas decisiones son difíciles de cambiar después de la implementación.