Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Switch services using the Version drop-down list. Learn more about navigation.
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
You can delete data from a table in several ways. Use the following information to help you choose which deletion method is best for your use case.
| Use case | Considerations | Method |
|---|---|---|
| Delete all data from a table. | Use the .clear table data command |
|
| Routinely delete old data. | Use if you need an automated deletion solution. | Use a retention policy |
| Bulk delete specific data by extents. | Only use if you're an expert user. | Use the .drop extents command |
| Delete records based on their content. | - Storage artifacts that contain the deleted records aren't necessarily deleted. - Deleted records can't be recovered (regardless of any retention or recoverability settings). - Use if you need a quick way to delete records. |
Use soft delete |
| Delete records based on their content. | - Storage artifacts that contain the deleted records are deleted. - Deleted records can't be recovered (regardless of any retention or recoverability settings). - Requires significant system resources and time to complete. |
Use purge |
| Use case | Considerations | Method |
|---|---|---|
| Delete all data from a table. | Use the .clear table data command |
|
| Routinely delete old data. | Use if you need an automated deletion solution. | Use a retention policy |
| Bulk delete specific data by extents. | Only use if you're an expert user. | Use the .drop extents command |
| Delete records based on their content. | - Storage artifacts that contain the deleted records aren't necessarily deleted. - Deleted records can't be recovered (regardless of any retention or recoverability settings). - Use if you need a quick way to delete records. |
Use soft delete |
The following sections describe the different deletion methods.
Delete all data in a table
To delete all data in a table, use the .clear table data command. This command is the most efficient way to remove all data from a table.
Syntax:
.clear table <TableName> data
Delete data by using a retention policy
Automatically delete data based on a retention policy. You can set the retention policy at the database or table level. There's no guarantee as to when the deletion occurs, but data isn't deleted before the retention period. This method is an efficient and convenient way to remove old data.
Consider a database or table that is set for 90 days of retention. If you need only 60 days of data, delete the older data as follows:
.alter-merge database <DatabaseName> policy retention softdelete = 60d
.alter-merge table <TableName> policy retention softdelete = 60d
Delete data by dropping extents
Extent (data shard) is the internal structure where data is stored. Each extent can hold up to millions of records. You can delete extents individually or as a group by using drop extent(s) commands.
Examples
You can delete all rows in a table or just a specific extent.
Delete all rows in a table:
.drop extents from TestTableDelete a specific extent:
.drop extent e9fac0d2-b6d5-4ce3-bdb4-dea052d13b42
Delete individual rows
You can use both purge and soft delete to delete individual rows. Soft delete doesn't necessarily remove the storage artifacts that contain records to delete, but purge removes all such storage artifacts.
Both methods prevent recovery of deleted records, regardless of any retention or recoverability settings. The deletion process is final and irreversible.
Soft delete
By using soft delete, you don't necessarily delete data from storage artifacts. This method marks all matching records as deleted, so that queries filter them out, and it doesn't require significant system resources.
Purge
By using purge, you replace extents that have one or more records to delete with new extents that don't contain those records. This deletion process isn't immediate, requires significant system resources, and can take a whole day to complete.
Use soft delete to delete individual rows. Data isn't necessarily deleted from storage artifacts. Soft delete prevents recovery of deleted records, regardless of any retention or recoverability settings. The deletion process is final and irreversible. This method marks all matching records as deleted, so queries filter them out, and it doesn't require significant system resources.