Table Storage
Characteristics
- stores NoSQL data (a single table can store entities with varying properties)
- lower in cost compard to SQL
- stores any number of entities
- storage account can have any number of tables
- good for structured, non-relational data
- supports OData protocol
- one entity can be 1MB max (2MB in CosmosDB Table API)
- one entity can have max 252 properties (+ 3 system properties: timestamp, partition key, row key)
Performance
Indexing
Entities with the same partition key can be queried more quickly, and inserted/updated in atomic operations. An entity’s row key is its unique identifier within a partition.
In Storage Account there is only primary index: PartitionKey and RowKey. In CosmosDB all properties are indexed and there is no manual index management.
Entities ofthe same PartitionKey can be updated in a single, atomic batch transaction (max 100 operations (4MB)).
There is no limit on number of partitions and their number does not impact performance.
More partitions = better loadbalancing, but also limits the ability to perform atomic transactions
Querying
Types of querying:
- A Point Query is the most efficient lookup to use and is recommended to be used for high-volume lookups or lookups requiring lowest latency. Such a query can use the indexes to locate an individual entity very efficiently by specifying both the PartitionKey and RowKey values. For example: $filter=(PartitionKey eq ‘Sales’) and (RowKey eq ‘2’)
- Second best is a Range Query that uses the PartitionKey and filters on a range of RowKey values to return more than one entity. The PartitionKey value identifies a specific partition, and the RowKey values identify a subset of the entities in that partition. For example: $filter=PartitionKey eq ‘Sales’ and RowKey ge ‘S’ and RowKey lt ‘T’
- Third best is a Partition Scan that uses the PartitionKey and filters on another non-key property and that may return more than one entity. The PartitionKey value identifies a specific partition, and the property values select for a subset of the entities in that partition. For example: $filter=PartitionKey eq ‘Sales’ and LastName eq ‘Smith’
- A Table Scan does not include the PartitionKey and is very inefficient because it searches all of the partitions that make up your table in turn for any matching entities. It will perform a table scan regardless of whether or not your filter uses the RowKey. For example: $filter=LastName eq ‘Jones’
- Queries that return multiple entities return them sorted in PartitionKey and RowKey order. To avoid resorting the entities in the client, choose a RowKey that defines the most common sort order.
If your client application needs only a limited set of properties from the entities in your table, you can use projection to limit the size of the returned data set. As with filtering, projection helps to reduce network load and client processing.
Tier
General-purpose V2 is the recommended choice. It seems that for Table Storage the Standard tier is recommended. Premium does not bring anything to the table in this case.