Microsoft has a round of announcements for public previews on Azure SQL Database. First up is Kevin Farlee announcing approximate count distinct:
The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.
This function is designed for use in big data scenarios and is optimized for the following conditions:
- Access of data sets that are millions of rows or higher AND
- Aggregation of a column or columns that have a large number of distinct values
Assuming these conditions, the accuracy will be within 2% of the precise result for a majority of workloads.
I’m liking this change. Sometimes I simply need an approximate number but I want it fast.
Shreya Verma announces MATCH support in the MERGE operator:
We will be further expanding the graph database capabilities with several new features. In this blog we will discuss one of these features that is now available for public preview in Azure SQL Database, MATCH support in MERGE DML for graph tables.
The MERGE statement performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a target table based on differences between the target table and the source table. Using MATCH predicates in a MERGE statement is now supported on Azure SQL Database. That is, it is now possible to merge your current graph data (node or edge tables) with new data using the MATCH predicates to specify graph relationships in a single statement, instead of separate INSERT/UPDATE/DELETE statements.
I’ll use that approximately the day they fix all of the bugs with the MERGE operator.
Joe Sack announces row mode memory grant feedback:
In Azure SQL Database, we are further expanding query processing capabilities with several new features under the Intelligent Query Processing (QP) feature family. In this blog post we’ll discuss one of these Intelligent QP features that is now available in public preview, row mode memory grant feedback. Row mode memory grant feedback expands on the memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.
Key feature benefits:
Reduce wasted memory. For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant. Consecutive executions will then request less memory.
Decrease spills to disk. For an insufficiently sized memory grant that results in a spill to disk, memory grant feedback will trigger a recalculation of the memory grant. Consecutive executions will then request more memory.
This was big for batch mode operators, and I’m happy to see it move to row mode operators as well.
Finally, Joe also announces table variable deferred compilation:
In Azure SQL Database, we will be further expanding query processing capabilities with several new features under the Intelligent Query Processing (QP) feature family. In this blog post we’ll discuss one of these Intelligent QP features that is now available in public preview in Azure SQL Database, table variable deferred compilation.
Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. During optimization and initial compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. This accurate row count information will be used for optimizing downstream plan operations.
This one has the potential to be a pretty big performance improvement as well.