Most relational databases have a comfort zone, and traditional Azure SQL leaves it somewhere in the hundreds of gigabytes - past that, storage limits and slow restores start to bite. Azure SQL Hyperscale is Microsoft's answer for the workloads that blow through it: OLTP tables heading toward a billion rows and beyond. It re-architects how the database separates compute, log, and storage so size stops being the thing that breaks. Getting value from it, though, depends as much on how you design the schema as on the tier itself.

Hyperscale in one picture

Traditional databases couple compute and storage on one machine, so growth eventually hits a wall. Hyperscale decouples them: a primary compute node handles transactions, a separate log service durably records changes, and storage is spread across page servers that each own a slice of the database and cache it on fast local storage. Because storage scales out across page servers rather than up on one disk, the database can grow to many terabytes. And because backups work at the storage layer through snapshots, restores and backups are near-instant regardless of size - the operation that used to scale painfully with data volume no longer does.

Design for a billion rows: partition first

Hyperscale removes the size ceiling; it doesn't remove the need to design for scale. The first lever is partitioning. Splitting a huge table by a sensible key (usually date) means queries and maintenance touch one partition instead of the whole table, and you can age, archive, or switch out partitions cheaply. Partitioning is the structural decision that makes billion-row tables manageable; indexing comes second, refining access within that structure rather than substituting for it.

Columnstore, used with precision

Columnstore indexes compress data heavily and accelerate analytical scans, but they're designed for read-heavy, scan-oriented access, not high-rate single-row OLTP writes. The effective pattern on a large table is to match the index to the data's temperature: keep hot, actively-written data on rowstore for fast point operations, and apply columnstore to warm and cold partitions that are mostly read and aggregated. Blanket-applying columnstore to a write-hot table fights its design; targeting it at the cold tail plays to its strengths.

Indexing for billion-row OLTP

At this scale, every index is a tradeoff you pay for on every write and in storage. Index the queries that actually run, resist the urge to add "just in case" indexes that slow inserts, and align indexing with your partitioning so lookups stay within a partition. The goal is the minimum set of indexes that serves real access patterns - not coverage for queries nobody issues.

Operational escape hatches

Hyperscale's architecture turns some painful operations into routine ones. Fast restore and database copy from storage snapshots make creating a test or recovery copy quick even at terabyte scale. Read scale-out lets you offload reporting and read-heavy queries onto secondary replicas, keeping the primary free for transactional work. These aren't just conveniences - they change what's feasible operationally on a very large database.

An end-to-end pattern

A coherent design for a large, growing OLTP table looks like a temperature gradient: hot recent data on partitioned rowstore for fast writes and point reads; warm data on a nonclustered columnstore index for mixed access; cold historical data on clustered columnstore, heavily compressed and read rarely. Automate the transitions with scheduled partition maintenance, and route analytical reads to a read replica. The result is a single table that serves transactional and analytical access at a scale a traditional design couldn't hold.

The takeaway

Azure SQL Hyperscale removes the storage ceiling and the backup-time penalty that cap traditional databases, which is what lets OLTP workloads grow into the billions of rows. But the tier is the enabler, not the design - the teams that succeed pair it with partitioning, temperature-aware columnstore, disciplined indexing, and read scale-out. Reach for Hyperscale when you've outgrown standard tiers; then design as if size still mattered, because how you structure the data is what decides whether a billion rows stays fast.