SQL Server
When a Datetime Index Helps in SQL Server - and When It Does Not
Learn when datetime and datetime2 indexes help SQL Server, how selectivity and key order shape plans, and how to benchmark date-range queries safely.
On this page
A datetime2 index can be an excellent access path. The useful question is not whether timestamps have too much granularity. It is whether the workload’s predicates, rows returned, selected columns, and write rate make that index cheaper than the alternatives.
Stored timestamp granularity and query granularity are different. A table may contain timestamps down to milliseconds, while a report asks for a whole day. The first describes the values; the second determines how many rows the query asks SQL Server to retrieve.
A reproducible event log
The following lab creates one million synthetic rows. It is designed to compare plans on your own SQL Server instance, not to promise a universal result. Run it in a disposable database with enough space. GO is a client batch separator understood by SSMS and 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;
GONotes
- line 1Drops dbo.EventLog: run the lab in a database you created for it.
- lines 13–22Six cross-joined lists of ten digits produce exactly 1,000,000 numbers, without a loop or a numbers table.
- lines 24–251,000 events per day for 1,000 days, spaced 86.4 seconds apart.
The generated values cover 1,000 days, with 1,000 events spaced evenly through each day. Every seeded event has its own millisecond value, so the timestamp key has high cardinality. From the generator (expected counts, not benchmark results), one minute selects about one event, an hour about 42, a day 1,000, a month about 29,000, and the broad interval nearly one million. CustomerId cycles deterministically, so there is no realistic skew or time-of-day burst; use production-like distributions before choosing indexes. The data is synthetic and contains no customer information.
Turn on Include Actual Execution Plan in SSMS (Ctrl+M), then collect I/O and CPU/time for each query. Run each more than once, distinguish warm-cache from cold-cache runs, and record the actual plan and parameter values. Do not clear a shared server’s cache to manufacture a cold run.
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;Notes
- line 1Reads and CPU/elapsed times appear in the Messages tab, next to the actual execution plan.
- line 2This timestamp matches exactly one generated row.
Because EventId is the clustered key, SQL Server carries it as the row locator in the nonclustered EventDate index; the narrow (EventId, EventDate) projection is covered without a lookup. The wider projection deliberately asks for EventType, Amount, and Description, so a date-index plan needs lookups unless another index covers them. Use the actual plan’s Actual Number of Rows and Actual Number of Rows Read, plus STATISTICS IO logical reads and STATISTICS TIME CPU and elapsed time. Record the access operator (seek, scan, lookup, or a combination), but do not infer performance from the operator icon alone. A seek can still read many rows, and a scan can be the least expensive plan for a broad request.
| Query shape | Predicate | Rows returned | Access method | Logical reads / CPU / elapsed |
|---|---|---|---|---|
| Exact timestamp | EventDate = @ExactTimestamp |
Measure | Measure actual plan | Run the lab; do not fill with guessed values |
| One minute | Half-open range | Measure | Measure actual plan | Run the lab; do not fill with guessed values |
| One hour | Half-open range | Measure | Measure actual plan | Run the lab; do not fill with guessed values |
| One day | Half-open range | Measure | Measure actual plan | Run the lab; do not fill with guessed values |
| One month | Half-open range | Measure | Measure actual plan | Run the lab; do not fill with guessed values |
| Broad span | Half-open range | Measure | Measure actual plan | Run the lab; do not fill with guessed values |
| Converted day | CAST(EventDate AS date) = ... |
Measure | Measure actual plan | Run the lab; do not fill with guessed values |
Benchmark results are intentionally not published yet because the harness has not been executed on a controlled SQL Server environment. The setup below is provided so the test can be reproduced. Record the SQL Server version and compatibility level, hardware/storage, row count, index definitions, actual plan behavior, and STATISTICS IO, TIME output with any future measurements. No execution plan or statistics output is claimed here.
Exact lookup and date range are different jobs
For an exact timestamp that identifies very few rows, a high-cardinality timestamp key can be highly selective. The nonclustered index can quickly locate the matching key range. Whether an application actually asks for exact timestamp equality is a workload question; it should not be assumed from the column type.
For calendar dates, use a half-open range: >= start and < next day. It includes every representable time on the requested date regardless of fractional precision. It also leaves the indexed column unwrapped in the predicate. Avoid BETWEEN with an end value at midnight, which excludes later times on that date, and avoid manufacturing an artificial last millisecond boundary.
CAST(EventDate AS date) = @Date is not a universal scan sentence. SQL Server versions and plans can sometimes use a seek-like access strategy for this expression, but it can produce different estimates or read more rows than the explicit range. Compare actual plans and reads on the target version and data. A SARGable predicate exposes a search argument the optimizer can use directly against an index key; preserving the column as a bare key in the range form makes the intended bounds explicit.
Selectivity: how much of the table do you need?
An hour, a day, a month, and a year can all use the same timestamp key, but they request very different row counts. A narrow range may make a seek attractive. A broad interval may require enough leaf pages and base-table lookups that scanning is cheaper. There is no universal percentage at which SQL Server switches from seek to scan.
The decision changes with row width, index width, whether requested columns are covered, physical organization, storage latency, statistics quality, cardinality estimates, and the cost model. A seek returning many rows is still a large read. A scan is not automatically a bad plan when most rows are needed. Compare rows returned with rows read, and compare reads and CPU under representative concurrency.
The distinction also explains the real date-granularity concern. EventDate can distinguish many instants, but EventDay groups them into a coarser bucket. If a million rows share one day, equality on that day is not selective. A day index can still help if the table is much larger or the request is covered, but the bucket alone does not make a large result small.
A day bucket as a first-class access pattern
If users repeatedly filter or group by calendar day, make that access pattern explicit with a computed column and test it:
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;The persisted value and index consume storage and must be maintained as rows are inserted or changed. The day key has lower cardinality than the full timestamp, so one value may point to many rows. It makes sense when day is genuinely a frequent filter/grouping key and measurements justify the maintenance cost. Otherwise, the timestamp range is often sufficient and avoids another index.
Composite keys follow the workload
Suppose a frequent query asks for one customer’s events during a time interval. Compare a customer-leading key with a date-leading key:
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;For CustomerId = @Customer plus a time range, (CustomerId, EventDate) often gives SQL Server an equality prefix followed by a bounded range within that customer. (EventDate, CustomerId) may suit workloads that primarily retrieve a narrow time interval across customers. Neither order is universally correct: consider other predicates, ordering, joins, selectivity, and the full query mix before retaining either index. Do not leave both in place just to give the optimizer options.
If the query returns EventType and Amount, a narrow covering index may avoid base-row lookups:
CREATE INDEX IX_EventLog_Customer_EventDate_Cover
ON dbo.EventLog(CustomerId, EventDate)
INCLUDE(EventType, Amount);Included columns are leaf data, not search keys. Covering can make a range plan cheaper by removing lookups, but it makes the index larger and adds work to writes. Include only columns that serve a demonstrated query; a wide duplicate of the table is not a sensible default.
Read benefit has a write price
Every index is additional storage and work on inserts, deletes, and updates to indexed or included columns. Event logs are often append-heavy, so a small set of narrow, measured indexes may be preferable to overlapping timestamp variations. Review usage and write impact over a representative interval; remove speculative or duplicate indexes only after checking dependencies and workload cycles. Microsoft’s index design guidance treats query benefit, storage, and modification cost as one design problem, and cautions against speculative over-indexing.
A monotonically increasing key can be an attractive clustered-key candidate because inserts tend toward the right edge of the key range. A datetime key is not automatically the answer. Decide whether it is unique (or add a unique tie-breaker), consider key width and how the clustered key is carried in nonclustered indexes, and measure last-page latch pressure under concurrent inserts. A bigint IDENTITY is compact and predictable; datetime2 can encode useful ordering but has different width, semantics, and collision behavior. Choose based on access patterns and operational evidence, not a blanket rule.
Practical decision checklist
Index a datetime column when workload predicates use it, the ranges are selective enough, key order matches common filters, the index reduces expensive reads or lookups, and the write/storage cost is acceptable. Reconsider when most requests return huge date spans, the application needlessly transforms the column, another equality key belongs first for the dominant query, the candidate duplicates an existing index, or write cost exceeds read benefit.
For fundamentals, see Indexes in SQL Server, then continue with Query Store regressions, and SQL Server workload monitoring. Browse the SQL Server hub for related guides.
Sources and further reading
- Microsoft: SQL Server index architecture and design guide
- Microsoft: Data type precedence and date/time types
Screenshot plan
Optional real captures: SSMS actual plans for the explicit range and converted-date query; STATISTICS IO, TIME output; and index properties for the measured alternatives. Use results from the lab environment and label SQL Server version, compatibility level, and cache conditions. No screenshots or benchmark figures are fabricated here.
- Timestamp values
- ordered by timeEventDate B-tree
- Start to end range
- Few rows: seek may suit the query
- Compare rows read and total cost
- Many rows: scan may cost less
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetQuery Store Quick ReferenceUse SQL Server Query Store to find regressions, compare plans, analyze runtime history, force or unforce plans safely, and understand when Query Store data can mislead you.Open cheat sheet
- RoadmapSQL Server PerformanceLearn when datetime and datetime2 indexes help SQL Server, how selectivity and key order shape plans, and how to benchmark date-range queries safely.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.