Delete All Azure Table Records

Question

I have an Azure Storage Table and it has 3k+ records.

What is the most efficient way to delete all the rows in the table?

Answer 1

For 3000 records, easiest way would be to delete the table. However please note that when you delete the table, it is not deleted at that time but is put in some kind of queue to be deleted and is actually deleted some time later. This time depends on the load on the system + number of entities in the table. During this time, you will not be able to recreate this table or use this table.

If it is important for you to keep using the table, the only other option is to delete entities. For faster deletes, you can look at deleting entities using Entity Batch Transactions. But for deleting entities, you would need to first fetch the entities. You can speed up the fetching process by only fetching PartitionKey and RowKey attributes of the entities instead of fetching all attributes as only these two attributes are required for deleting an entity.

Answer 2

For someone finding this later, the problem with the accepted answer "just deleted the table" is that while it works great in the storage emulator, it will fail randomly in production. If your app/service requires regenerating tables regularly then you'll find that you'll have failures due to either conflicts or deletion still in progress.

Instead, I found the fastest and most error proof EF friendly approach to be deleting all rows within a segmented query. Below is a simple drop-in example that I'm using. Pass in your client, table name, and a type that implements ITableEntity.

private async Task DeleteAllRows<T>(string table, CloudTableClient client) where T: ITableEntity, new()
    {
        // query all rows
        CloudTable tableref = client.GetTableReference(table);           
        var query = new TableQuery<T>();
        TableContinuationToken token = null;
                                         
        do
        {
            var result = await tableref.ExecuteQuerySegmentedAsync(query, token);  
            foreach (var row in result)
            {
                var op = TableOperation.Delete(row);
                tableref.ExecuteAsync(op);
            }
            token = result.ContinuationToken;
        } while (token != null);  
        
    }

Example Usage:

table = client.GetTableReference("TodayPerformanceSnapshot");
created = await table.CreateIfNotExistsAsync();

if(!created)
{ 
    // not created, table already existed, delete all content
   await DeleteAllRows<TodayPerformanceContainer>("TodayPerformanceSnapshot", client);
   log.Information("Azure Table:{Table} Purged", table);
}

A batched approach takes significantly more effort since you have to handle both the "only same partition keys in a batch" and "only 100 rows" limitations. The following version of DeleteAllRows does this.

private async Task DeleteAllRows<T>(string table, CloudTableClient client) where T: ITableEntity, new()
    {
        // query all rows
        CloudTable tableref = client.GetTableReference(table);           
        var query = new TableQuery<T>();
        TableContinuationToken token = null;            
        TableBatchOperation batchops = new TableBatchOperation();
        Dictionary<string, Stack<TableOperation>> pendingOperations = new Dictionary<string, Stack<TableOperation>>();
        
        do
        {
            var result = await tableref.ExecuteQuerySegmentedAsync(query, token);
            foreach (var row in result)
            {
               var op = TableOperation.Delete(row);
                if (pendingOperations.ContainsKey(row.PartitionKey))
                {
                    pendingOperations[row.PartitionKey].Push(op);
                }
                else
                {
                    pendingOperations.Add(row.PartitionKey, new Stack<TableOperation>() );
                    pendingOperations[row.PartitionKey].Push(op);
                }                                    
            }
            token = result.ContinuationToken;
        } while (token != null);

        // order by partition key            
        foreach (var key in pendingOperations.Keys)
        {                
            log.Information($"Deleting:{key}");                
            var rowStack = pendingOperations[key];
            int max = 100;
            int current = 0;

            while (rowStack.Count != 0)
            {
                // dequeue in groups of 100
                while (current < max && rowStack.Count > 0)
                {
                    var op = rowStack.Pop();
                    batchops.Add(op);
                    current++;
                }

                //execute and reset
                _ = await tableref.ExecuteBatchAsync(batchops);
                log.Information($"Deleted batch of size:{batchops.Count}");
                current = 0;
                batchops.Clear();
            }
        }                       
    }