Recently, real-time applications have started being built around events – a payment system records transactions as they happen, or an e-commerce store registers a purchase the moment a customer completes checkout. Security systems record access attempts and other events as they happen. And as events arrive continuously, they need to be stored safely and counted within a certain timeframe.
One of the best examples today is the CCTV Rush Hour stake game, where the system uses live traffic footage and AI-based vehicle detection tools to count how many vehicles crossed a certain point during a certain period, before the betting window closes.
Now, there’s no suggestion that Rush Hour uses SQL Server. But these mechanics provide an interesting scenario that SQL Server developers see across environments, which is the possibility of designing a database that can process a continuous stream of events while also providing fast and reliable counts.
In practice, the counting is the easy part. Indexing, concurrency, duplicate events, time boundaries, data growth, and many other aspects of this game remain complex. Here’s an example of an evolving schema for how you may approach this.
Start With the Workload
Before designing indexes or writing stored procedures, you should understand what the database is actually being asked to do.
Remember, the system is receiving vehicle detection events from an external computer-vision service. And this detection service interprets the video and determines that the vehicle crossed a relevant line. This means that the SQL Server doesn’t need to process the video; it only stores the resulting structured events and makes them available for the application.
A simplified event must contain:
| EventID | Unique identifier for the database record |
| SourceID | Identifies the camera or other event source |
| EventTime | Time at which the event occurred |
| EventType | Type of event detected |
| ExternalEventID | Identifier supplied by the upstream system |
| ConfidenceScore | Optional confidence value from the detection system |
So the SQL Server table might look like this:
CREATE TABLE dbo.Event
(
EventID bigint IDENTITY(1,1) NOT NULL,
SourceID int NOT NULL,
EventTime datetime2(3) NOT NULL,
EventType varchar(50) NOT NULL,
ExternalEventID uniqueidentifier NOT NULL,
ConfidenceScore decimal(5,4) NULL,
RecordedAt datetime2(3) NOT NULL
CONSTRAINT DF_Event_RecordedAt DEFAULT CAST(SYSUTCDATETIME() AS datetime2(3)),
CONSTRAINT PK_Event PRIMARY KEY NONCLUSTERED (EventID)
);
CREATE CLUSTERED INDEX CX_Event_EventTime
ON dbo.Event (EventTime);
The important characteristics of this workload are immediately apparent, where rows are inserted continuously, and most queries are likely to be interested in recent events. The queries will filter by the event source and a time range, while historical reporting may need to examine larger portions of the table.
The Basic Real-Time Count
Let’s say an application wants to determine how many events occurred within a 30-second window. The query might be:
DECLARE @StartTime datetime2(3) = ‘2026-08-28 14:00:00’;
DECLARE @EndTime datetime2(3) = ‘2026-08-28 14:00:30’;
DECLARE @SourceID int = 1;
SELECT COUNT_BIG(*)
FROM dbo.Event
WHERE SourceID = @SourceID
AND EventTime >= @StartTime
AND EventTime < @EndTime;
The biggest challenge here is what happens when dbo.Event gets millions of rows, and the query keeps running continuously. Without the proper access path, the SQL Server may examine more data than the application really needs.
But the query only cares about two things: which source generated the events and when those events occurred. This suggests the need for an index.
Designing an Index Around the Query
A general SQL Server principle says indexes should be designed around the queries that actually matter. But good query performance depends not just on the T-SQL itself but also on appropriate database structure and indexes.
CREATE NONCLUSTERED INDEX IX_Event_SourceID_EventTime
ON dbo.Event (SourceID, EventTime);
If the application is inserting thousands of events per second, every additional index should be maintained as the rows are inserted. But the objective is not to generate as many indexes as possible. It’s to create the smallest set of indexes that are efficient in supporting the workload.
SARGability and Time-Based Queries
Keep in mind that time-based filtering is one of the most important aspects of an event database. So consider a query that needs all events from a specific date:
WHERE CAST(EventTime AS date) = @EventDate
But know that applying a function to the indexed column could make it more difficult for the SQL Server to use the index properly, so a range is preferred:
WHERE EventTime >= @StartOfDay
AND EventTime < DATEADD(day, 1, @StartOfDay);
Instead of transforming the EventTime column, define the boundaries of the window and compare columns against them.
Suppose one window ends at exactly 14:00:30 and the next begins at exactly 14:00:30. An event at 14:00:30 belongs to the second window, not both.
For systems where event timing determines a result, precise boundaries are part of correctness, not merely query optimisation.
What Happens While New Events Are Being Inserted?
A real-time database doesn’t stop to wait for a query to calculate a result. The detection system still inserts new events while another process is calculating currently available data. That creates several concurrency questions: should the query wait for writers or should the writers wait for the query? Also, what version of the data should the reader see?
SQL Server provides several transaction isolation mechanisms for controlling these interactions. One option that may be appropriate for workloads involving frequent reads and writes is row-versioned READ COMMITTED, enabled through READ_COMMITTED_SNAPSHOT.
Still, these mechanisms aren’t a magic performance switch. Real-time performance is often a concurrency problem as well as an indexing problem. Focus on short transactions and know the isolation level you use could have a bigger impact than just adding another index.
Preventing Duplicate Events
There’s another issue to note, which occurs when the database is not the first component in the event pipeline.
Let’s say an external detection service sends an event to the application. The application then attempts to insert it into the SQL Server, but the connection fails after the server commits the transaction. In this case, the application can’t know whether the insert was a success, so it retries.
SQL Server has now received the same event twice, and if both rows get accepted, the event count is incorrect.
This is a common problem when it comes to distributed event-processing systems. Network failures and retries can easily make duplicate delivery possible, regardless of how correct the application is. One solution could be to assign every event a unique identifier:
CREATE UNIQUE NONCLUSTERED INDEX UX_Event_ExternalEventID
ON dbo.Event (ExternalEventID);
Now SQL Server itself can enforce the rule that a particular external event may only appear once, and any database constraints become part of the application correctness model.
Defining When an Event Window Is Finished
Counting events is straightforward when the window is already closed, but you still may find it difficult to determine when it’s safe to declare the result final. Let’s go back to our 30-second event window. At the end of those 30 seconds, the system will likely receive a detection event with EventTime that’s within the window, but may arrive a bit later due to network processing latency.
This creates a distinction between:
event time — when the event actually occurred
processing time — when SQL Server received it
Those timestamps should not automatically be treated as interchangeable.
The database schema retains the event timestamp independently from the time the row was inserted using the RecordedAt column we included in our initial table definition. Now the database can distinguish between an event that occurred at 14:00:15 and one that happened to arrive at 14:00:17.
Raw Events or Pre-Aggregated Counts?
Now it’s time to discuss whether every event should be calculated from the raw event table. If you have a somewhat small number of queries, this might be the best approach. But if you have a system that constantly asks for counts over recent windows while retaining years of events, repeatedly calculating the same aggregations may become unnecessary work.
But know that if the query already performs an efficient index seek over a small time range, adding an aggregation pipeline could create unnecessary complexity.
Managing a Continuously Growing Event Table
Due to the nature of real-event counting, the table may never stop growing, and it can easily accumulate millions of rows. This means you should design a system that defines how long raw events stay online, when they’re archived, how quickly and when old data must be removed, and whether reporting workloads need access to the operational table.
Partitioning can be useful in this situation, particularly when the data has a natural time dimension, but it should not be treated as an automatic solution for large tables.
Security Is Part of the Architecture
In this system, data must be trusted, especially since every event count can determine a final outcome. The application should receive only the permissions required for its specific operations. Separate permissions for:
- Inserting incoming events
- Reading event data
- Calculating results
- Administrative operations
- Auditing
Also protect finalised results from arbitrary modifications. Generate a table which can record when a result was generated and by which database principal:
CREATE TABLE dbo.EventWindowResult
(
ResultID bigint IDENTITY(1,1) NOT NULL,
EventWindowID bigint NOT NULL,
FinalCount bigint NOT NULL,
CalculatedAt datetime2(3) NOT NULL
CONSTRAINT DF_EventWindowResult_CalculatedAt DEFAULT SYSUTCDATETIME(),
CalculatedBy sysname NOT NULL,
CONSTRAINT PK_EventWindowResult
PRIMARY KEY CLUSTERED (ResultID)
);
Where the exact audit design depends on regulatory and application requirements.
SQL Server as Part of a Larger Event Pipeline
Finally, have in mind that a system that supports CCTV-related games doesn’t necessarily need SQL Server to run. The computer-vision component can perform detection and produce structured events. But the SQL Server can still be responsible for the durable relational portion of the workload.
At sufficiently high event rates, a dedicated streaming platform may be appropriate upstream of SQL Server. The database can then remain focused on durable storage, transactional processing, querying, and reporting.
Conclusion
The CCTV gaming concept provides an interesting showcase of challenges, mainly because the outcome is based on real-world events: vehicles that are tracked crossing a determined point while in live traffic. But the underlying SQL Server lessons extend far beyond gaming.
A real-time event-counting application can make the underlying database problem look simple on the surface, while it’s actually quite complex. The database needs appropriate schema and indexes, and you need to understand time predicates so that SQL Server can use the indexes effectively. Handle duplicate events, set clear boundaries on the event windows, and ensure a secure environment.
And remember, the goal is the same: ingest events efficiently, query them predictably, preserve their integrity, and keep the database performant as the event stream grows.