SQL Server
Cuándo ayuda un índice datetime en SQL Server y cuándo no
Aprende cuándo ayudan los índices datetime y datetime2 en SQL Server y cómo influyen la selectividad, el orden de claves y las consultas por rango.
En este artículo
Un índice datetime2 puede ser una excelente ruta de acceso. La pregunta útil no es si las marcas de tiempo tienen demasiada granularidad, sino si los predicados, las filas devueltas, las columnas solicitadas y el ritmo de escritura hacen que ese índice sea más barato que las alternativas.
La granularidad de los valores almacenados y la granularidad de la consulta son distintas. Una tabla puede guardar horas con precisión de milisegundos mientras un informe pide un día completo. La primera describe los valores; la segunda determina cuántas filas solicita a SQL Server.
Un registro de eventos reproducible
Este laboratorio crea un millón de filas sintéticas. Sirve para comparar planes en tu propia instancia de SQL Server, no para prometer un resultado universal. Ejecútalo en una base desechable con espacio suficiente. GO es un separador de lotes que entienden SSMS y sqlcmd.
DROP TABLE IF EXISTS dbo.EventLog;
GO
CREATE TABLE dbo.EventLog
(
EventId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_EventLog PRIMARY KEY,
EventDate datetime2(3) NOT NULL,
CustomerId int NOT NULL,
EventType varchar(20) NOT NULL,
Amount decimal(10,2) NOT NULL,
Description varchar(80) NOT NULL
);
GO
WITH digit(d) AS
(
SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(d)
), n AS
(
SELECT CONVERT(int, ROW_NUMBER() OVER
(ORDER BY d6.d, d5.d, d4.d, d3.d, d2.d, d1.d) - 1) AS n
FROM digit AS d1 CROSS JOIN digit AS d2 CROSS JOIN digit AS d3
CROSS JOIN digit AS d4 CROSS JOIN digit AS d5 CROSS JOIN digit AS d6
)
INSERT dbo.EventLog(EventDate, CustomerId, EventType, Amount, Description)
SELECT DATEADD(millisecond, CONVERT(int, (CONVERT(bigint, n % 1000) * 86400000) / 1000),
DATEADD(day, n / 1000, CONVERT(datetime2(3), '2024-01-01'))),
1 + (n % 10000),
CASE n % 4 WHEN 0 THEN 'purchase' WHEN 1 THEN 'view'
WHEN 2 THEN 'refund' ELSE 'login' END,
CONVERT(decimal(10,2), (n % 50000) / 100.0),
CONCAT('Synthetic event ', n % 1000)
FROM n;
GO
CREATE INDEX IX_EventLog_EventDate ON dbo.EventLog(EventDate);
UPDATE STATISTICS dbo.EventLog WITH FULLSCAN;
GONotas
- línea 1Elimina dbo.EventLog: ejecuta el laboratorio en una base de datos creada para él.
- líneas 13–22Seis listas de diez dígitos combinadas con CROSS JOIN generan exactamente 1.000.000 de números, sin bucle ni tabla de números.
- líneas 24–251.000 eventos por día durante 1.000 días, separados 86,4 segundos.
Los valores generados cubren 1.000 días, con 1.000 eventos distribuidos uniformemente a lo largo de cada día. Cada evento tiene su propio milisegundo, por lo que EventDate presenta alta cardinalidad. Según el generador (cantidades esperadas, no resultados de benchmark), un minuto selecciona cerca de un evento, una hora unos 42, un día 1.000, un mes unos 29.000 y el intervalo amplio casi un millón. CustomerId sigue un ciclo determinista: los datos no reproducen sesgos realistas ni picos horarios. Son sintéticos y no contienen información de clientes.
Activa Include Actual Execution Plan en SSMS (Ctrl+M) y recoge las E/S, la CPU y el tiempo de cada consulta. Ejecuta cada consulta varias veces, distingue las pruebas con la caché caliente de las realizadas con la caché fría y registra el plan real y los parámetros. No vacíes la caché de un servidor compartido para simular una prueba en frío.
SET STATISTICS IO, TIME ON;
DECLARE @ExactTimestamp datetime2(3) = '2024-02-10T01:00:28.800';
DECLARE @Start datetime2(3) = '2024-02-10T00:00:00';
DECLARE @End datetime2(3) = '2024-02-11T00:00:00';
DECLARE @MinuteEnd datetime2(3) = '2024-02-10T00:01:00';
DECLARE @HourEnd datetime2(3) = '2024-02-10T01:00:00';
DECLARE @MonthStart datetime2(3) = '2024-02-01T00:00:00';
DECLARE @MonthEnd datetime2(3) = '2024-03-01T00:00:00';
DECLARE @BroadEnd datetime2(3) = '2027-01-01T00:00:00';
DECLARE @Customer int = 42;
-- Exact timestamp: a very selective equality predicate.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate = @ExactTimestamp;
-- Half-open minute range; the generator spaces events across the day.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @MinuteEnd;
-- Half-open day range: includes all times on the day, not midnight only.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @End;
-- Narrow one-hour range.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @HourEnd;
-- Wider projection: these columns are not in the date index.
-- Compare lookup work with a scan as the date range grows.
SELECT EventId, EventDate, EventType, Amount, Description
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @End;
-- Compare a conversion on the indexed column with the range form.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE CAST(EventDate AS date) = CONVERT(date, @Start);
-- Broader intervals: change parameters to one hour, one month, then a large span.
SELECT EventId, EventDate
FROM dbo.EventLog
WHERE EventDate >= @MonthStart AND EventDate < @MonthEnd;
-- Broad range covering most of the generated table.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @BroadEnd;
-- Customer equality plus time range, with no customer-leading index yet.
SELECT EventId, EventDate, EventType, Amount
FROM dbo.EventLog
WHERE CustomerId = @Customer AND EventDate >= @Start AND EventDate < @End;
SET STATISTICS IO, TIME OFF;Notas
- línea 1Las lecturas y los tiempos de CPU y transcurrido aparecen en la pestaña Mensajes, junto al plan de ejecución real.
- línea 2Esta marca de tiempo coincide exactamente con una fila generada.
En el plan real, revisa Actual Number of Rows y Actual Number of Rows Read, las lecturas lógicas de STATISTICS IO y el CPU y tiempo transcurrido de STATISTICS TIME. Registra el operador (seek, scan, lookup o una combinación), pero no deduzcas el rendimiento solo por su icono. Un seek puede leer muchas filas y un scan puede ser la opción menos costosa para una consulta amplia.
| Consulta | Predicado | Filas devueltas | Acceso | E/S lógicas / CPU / tiempo |
|---|---|---|---|---|
| Marca exacta | EventDate = @ExactTimestamp |
Medir | Medir el plan real | Ejecuta el laboratorio; no inventes cifras |
| Un minuto | Rango semiabierto | Medir | Medir plan real | Ejecuta el laboratorio; no inventes cifras |
| Una hora | Rango semiabierto | Medir | Medir el plan real | Ejecuta el laboratorio; no inventes cifras |
| Un día | Rango semiabierto | Medir | Medir el plan real | Ejecuta el laboratorio; no inventes cifras |
| Un mes | Rango semiabierto | Medir | Medir el plan real | Ejecuta el laboratorio; no inventes cifras |
| Periodo amplio | Rango semiabierto | Medir | Medir el plan real | Ejecuta el laboratorio; no inventes cifras |
| Día convertido | CAST(EventDate AS date) =... |
Medir | Medir el plan real | Ejecuta el laboratorio; no inventes cifras |
Los resultados del benchmark no se publican todavía porque el banco de pruebas aún no se ha ejecutado en un entorno controlado de SQL Server. El procedimiento permite reproducir la prueba. Si se publican mediciones, deben incluir la versión y el nivel de compatibilidad de SQL Server, el hardware y el almacenamiento, el número de filas, las definiciones de índices, el comportamiento del plan real y la salida de STATISTICS IO, TIME. Aquí no se presentan planes ni estadísticas medidos.
Búsqueda exacta y rango de fechas
Para una marca de tiempo exacta que coincide con pocas filas, una clave temporal de alta cardinalidad puede ser muy selectiva. El índice no cluster localiza rápidamente el rango de claves coincidente. Que la aplicación busque esa igualdad exacta depende de la carga de trabajo; no debe suponerse por el tipo de columna.
Para fechas de calendario, usa un rango semiabierto: >= inicio y < día siguiente. Incluye todas las horas representables del día con independencia de la precisión fraccionaria. También deja intacta la columna indexada en el predicado. Evita BETWEEN con un final a medianoche, porque excluye el resto del día, y no fabriques una frontera artificial de último milisegundo.
CAST(EventDate AS date) = @Date no implica necesariamente un scan. Según la versión y el plan, SQL Server puede usar en algunos casos un acceso similar a seek, pero las estimaciones o el número de filas leídas pueden diferir de las del rango explícito. Compara los planes y las lecturas en la versión y con los datos de destino. Un predicado SARGable expone un argumento de búsqueda que el optimizador puede aplicar directamente a una clave de índice; el rango deja claros sus límites.
Selectividad: ¿qué parte de la tabla necesitas leer?
Una hora, un día, un mes y un año pueden usar la misma clave temporal, pero solicitan cantidades muy distintas de filas. Un rango estrecho puede favorecer un seek. Un intervalo amplio puede requerir tantas páginas hoja y búsquedas en la tabla que un scan resulte más barato. No existe un porcentaje universal a partir del cual SQL Server cambie de seek a scan.
La decisión varía con el ancho de fila e índice, la cobertura de columnas solicitadas, la organización física, la latencia de almacenamiento, la calidad de estadísticas, las estimaciones de cardinalidad y el modelo de costos. Un seek que devuelve muchas filas sigue implicando una lectura grande. Un scan no es automáticamente malo cuando se necesitan la mayoría de las filas. Compara filas devueltas y leídas, E/S y CPU bajo concurrencia representativa.
Esta es también la preocupación real sobre la granularidad de fechas. EventDate distingue muchos instantes; EventDay los agrupa en un bucket más grueso. Si un millón de filas comparte un día, la igualdad por día no es selectiva. El índice puede ayudar si la tabla es mucho mayor o la consulta queda cubierta, pero el bucket por sí solo no reduce un resultado grande.
Un bucket diario como acceso de primer nivel
Si los usuarios filtran o agrupan con frecuencia por día natural, haz explícito ese patrón con una columna calculada y pruébalo:
ALTER TABLE dbo.EventLog
ADD EventDay AS CONVERT(date, EventDate) PERSISTED;
CREATE INDEX IX_EventLog_EventDay ON dbo.EventLog(EventDay);
DECLARE @Day date = '2024-02-10';
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDay = @Day;El valor persistido y el índice ocupan almacenamiento y deben mantenerse al insertar o modificar filas. La clave del día tiene menor cardinalidad que la marca de tiempo completa, por lo que un mismo valor puede corresponder a muchas filas. Esta opción tiene sentido si el día se usa con frecuencia como filtro o criterio de agrupación y las mediciones justifican el coste de mantenimiento. De lo contrario, suele bastar el rango temporal y se evita otro índice.
Las claves compuestas siguen la carga de trabajo
Supón que una consulta frecuente solicita los eventos de un cliente durante un intervalo. Compara una clave que empiece por cliente con otra que empiece por fecha:
DROP INDEX IF EXISTS IX_EventLog_Customer_EventDate ON dbo.EventLog;
DROP INDEX IF EXISTS IX_EventLog_EventDate_Customer ON dbo.EventLog;
CREATE INDEX IX_EventLog_Customer_EventDate
ON dbo.EventLog(CustomerId, EventDate);
UPDATE STATISTICS dbo.EventLog WITH FULLSCAN;
-- Run the CustomerId + EventDate query and record its plan and reads.
DROP INDEX IX_EventLog_Customer_EventDate ON dbo.EventLog;
CREATE INDEX IX_EventLog_EventDate_Customer
ON dbo.EventLog(EventDate, CustomerId);
UPDATE STATISTICS dbo.EventLog WITH FULLSCAN;
-- Run the same query and workload mix; compare results, then drop the candidate.
DROP INDEX IX_EventLog_EventDate_Customer ON dbo.EventLog;Para CustomerId = @Customer junto con un rango temporal, (CustomerId, EventDate) suele ofrecer un prefijo de igualdad seguido de un rango acotado para ese cliente. (EventDate, CustomerId) puede convenir a cargas de trabajo que consultan principalmente intervalos breves entre muchos clientes. Ningún orden es universal: considera los demás predicados, el ordenamiento, las uniones, la selectividad y la mezcla completa de consultas. No conserves ambas claves solo para dar opciones al optimizador.
Si la consulta devuelve EventType y Amount, un índice de cobertura estrecho puede evitar búsquedas de filas base:
CREATE INDEX IX_EventLog_Customer_EventDate_Cover
ON dbo.EventLog(CustomerId, EventDate)
INCLUDE(EventType, Amount);Las columnas incluidas se almacenan en las páginas hoja; no son claves de búsqueda. La cobertura puede abaratar un plan de rango al eliminar búsquedas en la tabla, pero aumenta el tamaño del índice y el coste de escritura. Incluye solo columnas que respondan a una consulta demostrada; una copia ancha de la tabla no es un valor predeterminado sensato.
El beneficio de lectura tiene un coste de escritura
Cada índice añade almacenamiento y trabajo a las inserciones, eliminaciones y actualizaciones de columnas clave o incluidas. Los registros de eventos suelen recibir principalmente inserciones, por lo que puede ser preferible mantener unos pocos índices estrechos y medidos en vez de varias variantes de fecha solapadas. Revisa el uso y el impacto en las escrituras durante un periodo representativo; comprueba las dependencias y los ciclos de carga antes de eliminar índices especulativos o duplicados. La guía de índices de Microsoft considera conjuntamente el beneficio para las consultas, el almacenamiento y el coste de modificación, y advierte contra la sobreindexación especulativa.
Una clave creciente puede ser una buena candidata a clave de clustering porque las inserciones tienden al extremo derecho del rango. Una fecha no es automáticamente la mejor opción. Comprueba si es única (o añade una clave única de desempate), su anchura y cómo se incorpora la clave de clustering a los índices no cluster; mide la contención de la última página con inserciones simultáneas. bigint IDENTITY es compacto y predecible; datetime2 puede codificar un orden útil, pero difiere en anchura, semántica y riesgo de colisión. Decide según los patrones de acceso y la evidencia operativa, no por una regla general.
Lista práctica de decisión
Indexa una columna datetime cuando los predicados la usan, los rangos son suficientemente selectivos, el orden de las claves coincide con los filtros habituales, el índice reduce lecturas costosas y el coste de escritura y almacenamiento es aceptable. Reconsidera la decisión si la mayoría de las consultas devuelve intervalos enormes, la aplicación transforma la columna sin necesidad, otra clave de igualdad debería ir primero, el índice duplica uno existente o el coste de escritura supera el beneficio de lectura.
Para empezar, consulta Índices en SQL Server, continúa con regresiones de Query Store y monitorización de cargas de trabajo de SQL Server. Explora el hub de SQL Server.
Fuentes y lecturas adicionales
- Microsoft: guía de arquitectura y diseño de índices de SQL Server
- Microsoft: precedencia y tipos de fecha/hora
Plan de capturas
Capturas reales opcionales: planes de ejecución reales de SSMS para el rango explícito y la conversión de fecha; salida de STATISTICS IO, TIME; y propiedades de los índices comparados. Indica la versión de SQL Server, el nivel de compatibilidad y las condiciones de caché. No se inventan capturas ni cifras de benchmark.
- Timestamp values
- order by timeEventDate B-tree
- Start to end range
- Few rows: seek may suit the consulta
- Compare rows read and total cost
- Many rows: scan may cost less
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Sigue aprendiendo
- Guía rápidaComprobar el uso de índicesRevisa la actividad de lectura y escritura de los índices de SQL Server, identifica los índices de poco valor y decide cuáles merecen un análisis más profundo antes de eliminar ninguno.Abrir guía rápida
- Hoja de rutaSQL Server PerformanceAprende cuándo ayudan los índices datetime y datetime2 en SQL Server y cómo influyen la selectividad, el orden de claves y las consultas por rango.Ver hoja de ruta
¿Qué debería crear después?
Estoy construyendo Javi Builds alrededor de problemas reales que enfrentan los ingenieros de datos. Dime qué tema te gustaría ver después.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.