Azure Table Storage Tips for the RDBMS Developer

Suppose you only have relational database management system (RDBMS) experience and are new to Azure Table storage. It’s common to find yourself “thinking in SQL” and trying to solve database modeling requirements with a SQL approach before translating them to a key-value mindset.

In this tutorial, I’ll cover some fundamentals of Azure Table storage to get you more familiar with this type of storage service, including property types and table limits, with your RDBMS background in mind (but I welcome IT pros from all backgrounds to use this guide and learn something new).

Then, I’ll dive into common questions you might find yourself asking about how to use Azure Table storage and provide some best practices with several query examples based on potential real-world scenarios you might experience on the job.

Where code samples or references are applicable in this blog post, I’ll be focusing on .NET and using the Azure SDK (specifically relating to the Microsoft.Azure.Cosmos.Table NuGet package).

If you have a RDBMS background and are new to Azure Table Storage, it’s common to find yourself “thinking in SQL” and trying to solve database modeling requirements with a SQL approach before then trying to translate that to a key-value mindset. In this blog post, I’ll cover some of the fundamentals of Azure Table Storage and dive into some common questions you might find yourself asking about Azure Table Storage. Where code samples or references are applicable in this blog post, we’ll be focusing on .NET and using the Azure SDK (specifically relating to the Microsoft.Azure.Cosmos.Table nuget package).

You can use the table of contents below to more easily jump to the specific area of the guide you’re interested in:

Azure Table Storage Explained

Properties and Limits

Indexes

Transactions

High Availability / Data Redundancy Comparison Chart

Pricing

Azure Table Storage FAQ

What kind of NoSQL store is Azure Table storage?

How do I create custom indexes on an Azure Table storage table?

How do I optimize querying by different properties if I can’t have secondary indexes?

Azure Table storage query example: How to search for an employee by employee id or domain username using PartitionKey and RowKey

Can I query by properties other than the PartitionKey and RowKey?

How can I insert/update multiple entities in an atomic operation?

How do I store entities with different schema in the same table?

How can I perform a LIKE query for values starting with a given value?

Azure Table storage query example: How to retrieve a distinct list of employees using Range Query

How do I enforce unique constraints?

How can I include special characters in the PartitionKey or RowKey?

How do I perform case-insensitive queries?

How can I perform an “ORDER BY Date DESC” query?

Azure Table storage query example: How to look up orders in both ascending and descending order of OrderDate

How do I limit the fields I pull back in a query (SELECT <ColumnList>)?

Azure Table storage vs. CosmosDB

 How to Start Using Azure Table Storage + Additional Resources Recommendation

Azure Table Storage Explained

First, let’s cover some of the fundamental constructs and features of Azure Table storage, including the main limitations and differences versus what you might be used to as an RDBMS developer.

Azure Table Storage Properties and Size Limits

• An individual table can contain a collection of entities with different properties.
• A table can contain up to 255 properties.
• Each property can be up to 64KB in size.
• Each entity can be up to 1MB in size.
• You can have as many tables as you want, up to the storage capacity of an Azure Storage Account (500TB).
• Every entity has a PartitionKey, RowKey, and a Timestamp. The Timestamp property is maintained server-side as the time the entity was last modified and is used to provide optimistic concurrency, so you cannot directly modify it.
• PartitionKey and RowKey are both strings and can each be up to 1KB in size, and the combination of both must be unique.

Azure Table Storage Indexes

• Only the PartitionKey and RowKey properties are indexed.
• Secondary indexes on other properties cannot be created.
• These values are indexed in ascending order.
• Having a solid understanding of your query requirements is important, as there are strategies for dealing with the apparent limitation on indexes, which I’ll cover in some common scenarios below.

Azure Table Storage Transactions

• Atomic updates can be performed as long as the entities are all within the same table partition (same PartitionKey).
• You cannot perform cross-table or cross-partition transactions.
• A maximum of 100 operations can be performed within the same atomic operation.
• The total payload of the batch must be no more than 4MB.

Azure Table Storage High Availability / Data Redundancy Comparison Chart

Use the comparison chart below to choose the level of replication you may need for Azure Table storage:

Azure Table Storage Pricing

• Azure Table storage is very cost-effective.
• You’re charged based on the amount of storage and the number of storage transactions made against the service (e.g., an individual operation against the storage API).
• For example, in December 2021, for 1000GB of storage and 100 million storage transactions/month, the approximate costs in U.S. regions are as follows:
  • LRS redundancy: $45.04/month
  • RA-GRS redundancy: $75.04/month
  • RA-GZRS (where offered): $126.54/month

Back to top  

Azure Table Storage FAQ for the RDBMS Developer

What kind of NoSQL store is Azure Table storage?

Azure Table storage is a NoSQL key-value PaaS data store designed to be a great option for highly scalable, highly available systems. It supports storing petabytes of data and a flexible data schema, meaning different entities in the same table can have different schemas.

References to NoSQL databases having “flexible schema” or having a “schemaless design” can give the impression database schema design is a thing of the past, and you can bypass it to focus more on the application code. The reality is, even in this NoSQL data world, schema design is very important and if you don’t give it due care and attention, it can come back to bite you.

How do I create custom indexes on an Azure Table Storage table?

The only index existing on a table is on the PartitionKey and RowKey properties. Secondary indexes are not supported.

How do I optimize querying by different properties if I can’t have secondary indexes?

Storing duplicate copies of the data with different PartitionKey and RowKeys properties is the way to go. Storage is inexpensive. You can use the in-partition batch support to insert/modify the copies in an atomic manner. Just bear in mind the limitations on batches—e.g., 100 operations max, 4MB limit on total payload size—but for most scenarios, this is not a concern.

Azure Table storage query example: How to search for an employee by employee id or domain username using PartitionKey and RowKey

In this scenario, we have the following data in the Employee table:

Here, we’re storing two copies of an Employee entity—all the custom properties are the same (FirstName, LastName, etc.). However, to facilitate our query requirements, we’ll use a different RowKey for each.

A query by Employee Id would be an efficient Point Query (a query for a single entity identified by its PartitionKey and RowKey).

// Equivalent to SQL: SELECT * FROM Employee WHERE PartitionKey = 'Employee' AND RowKey = 'Id_012345'
var query = TableOperation.Retrieve<EmployeeEntity>("Employee", "Id_012345");
var result = await employeeTable.ExecuteAsync(query);

A query by domain username would also be an efficient Point Query. The above query is a shortened convenience wrapper for single-row lookups by PartitionKey and RowKey. However, you can also use ExecuteQuerySegmentedAsync (as shown below), which is what you’d use for queries returning multiple entities (complete example).

// Equivalent to SQL: SELECT * FROM Employee WHERE PartitionKey = 'Employee' AND RowKey = 'Uname_jbloggs'
var filter =
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("PartitionKey",QueryComparisons.Equal, "Employee"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, "Uname_jbloggs"));

var query = new TableQuery<EmployeeEntity>().Where(filter);
var querySegment = employeeTable.ExecuteQuerySegmentedAsync(query, null);

But how would you retrieve a list of all employees? If we retrieved everything in the Employee partition, we’d get duplicates, as we’re storing multiple copies of the data. So, we would want to query for entities where the RowKey starts with “Id_” (we could also choose to search for RowKeys starting with “Uname_”). See the “How can I perform a LIKE query for values starting with a given value?” question further down for more information.

Can I query by properties other than the PartitionKey and RowKey?

Yes. But just like in an RDBMS, when you query on a field that is not indexed, it will perform a scan. If you’re searching on a custom property within a specific partition, it will be a Partition Scan. If you’re searching across all partitions, a Table Scan will be the most inefficient query to perform.

How can I insert/update multiple entities in an atomic operation?

Using the previous employee example, how do you go about creating both entities atomically when adding a new employee to the database? Well, as shown in the code below, you can perform multiple operations as part of a TableBatchOperation—just recall the limitations I mentioned in the fundamentals section: a maximum of 100 operations per batch and all entities must have the same PartitionKey and a maximum batch payload size of 4MB.

var employeeById = new EmployeeEntity {
    PartitionKey = "Employee",
    RowKey = "Id_12345",
    FirstName = "Joe",
    LastName = "Bloggs",
    EmployeeId = "012345",
    DomainUsername = "jbloggs"
};

var employeeByDomainUsername = new EmployeeEntity {
    PartitionKey = "Employee",
    RowKey = "Uname_jbloggs",
    ... rest of properties...
};

var batchOperation = new TableBatchOperation();
batchOperation.Insert(employeeById);
batchOperation.Insert(employeeByUname);

// employeeTable variable is a reference to a CloudTable instance pointing to the Employee table
await employeeTable.ExecuteBatchAsync(batchOperation);

How do I store entities with different schema in the same table?

There’s nothing special here—just create a new entity with different properties and insert it into the same table.

The following example demonstrates this for Order and OrderItem entities, storing the order header and order item entities in the same table.

var order = new OrderEntity {
    PartitionKey = "Order",
    RowKey = "12345",
    OrderId = "12345",
    Total = 10.99,
    OrderDate = "2020-01-02T03:04:05.678Z"
};

var orderItem = new OrderItemEntity {
    PartitionKey = "Order",
    RowKey = "Item_12345_ABC123",
    ProductCode = "ABC123",
    Quantity = 1,
    UnitPrice = 10.99
};

var batchOperation = new TableBatchOperation();
batchOperation.Insert(order);
batchOperation.Insert(orderItem);

This would result in the following data in the table:

A note about the limitations of properties on a single table—the combined total number of properties cannot exceed 255.

How can I perform a LIKE query for values starting with a given value?

You can run a Range Query equivalent to a LIKE ‘Something%’ condition. Range queries are the second-best types of query, filtering on the partition key and a range of row key values.

Azure Table storage query example: How to retrieve a distinct list of employees using Range Query

We’re looking for all rows starting with “Id_” in this case.

var rowKeyStartsWith = "Id_";

var partitionKeyFilter =
    TableQuery.GenerateFilterCondition("PartitionKey",QueryComparisons.Equal, "Employee");

// Replace the last character in the starting value with the next character in sequence
// to serve as the end of the range to filter on
var rowKeyRangeEnd =
    rowKeyStartsWith.Substring(0, rowKeyStartsWith.Length - 1) + (char)(rowKeyStartsWith.Last() + 1);

var rowKeyFilter =
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("RowKey",  QueryComparisons.GreaterThan, rowKeyStartsWith),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, rowKeyRangeEnd));

// Equivalent to SQL: SELECT * FROM Employee WHERE PartitionKey = 'Employee' AND RowKey LIKE 'Id[_]%'
var fullFilter = TableQuery.CombineFilters(partitionKeyFilter, TableOperators.And, rowKeyFilter);

var query = new TableQuery<EmployeeEntity>().Where(fullFilter);

The outcome is we search for RowKeys > ‘Id_’ and < ‘Id’ for the employee list.

How do I enforce unique constraints?

PartitionKey and RowKey combinations are unique. In a relational database such as SQL Server, you might add a unique constraint or a unique index to fields unique outside of the Primary Key. In Azure Table storage, you can add an extra copy of the entity within the same atomic batch to enforce uniqueness.

For example, in the previous employee scenario above, EmployeeId and DomainUsername values would have uniqueness enforced, as we’ve duplicated the data with separate RowKeys with those properties built-in.

How can I include special characters in the PartitionKey or RowKey?

A limitation of PartitionKeys and RowKeys is that certain characters are not allowed. You’d get an error when trying to insert the Uname entity shown above because of the backslash if you try to create an employee with “MyDomain\jbloggs” as the DomainUsername.

One approach for this is to base64 encode the value, which results in a safe string except it could still contain a forward slash, which is not allowed. You can replace the forward slash with a substitute safe character, such as an underscore.

For “MyDomain\jbloggs” username the outcome would be as follows:

// Equivalent to SQL:
// SELECT PartitionKey, RowKey, DomainUsername FROM Employee WHERE PartitionKey = 'Employee' AND RowKey = 'Id_012345'
var filter =
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("PartitionKey",QueryComparisons.Equal, "Employee"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, "Id_012345"));

var query = new TableQuery<EmployeeEntity>().Where(filter);
query.SelectColumns = new List<string> { "DomainUsername" });

Adrian Hills

Adrian (@AdaTheDev) is a Lead Development Engineer at SentryOne, focusing on the development of the Data DevOps SaaS product portfolio – namely SentryOne Document and SentryOne Test. Based in the UK, he joined SentryOne from Pragmatic Works in 2018, with 18 years experience in software development that includes a background in developing SaaS platforms in the hospitality and digital marketing industries. Adrian is a fan of good (bad) puns, dad jokes, and proper British biscuits.