2024 SQL improvements

The following SQL improvements were introduced in 2024:

Date releasedImprovementImpact
November 2024Full-text search with the SEARCH and SEARCH_IP functions is now generally available and is no longer in preview.You can find character data (text) and IPv4 addresses in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns.
October 2024Support for querying objects up to 128 MB in files on a stage.You can more easily reduce the size of an object before storing it in a column. Also, with the 9.17 release, you can now store objects larger than 16 MB in a column. For more information, see Size limits for database objects.
October 2024Support for higher-order functions extended with the REDUCE function.You can use lambda expressions to reduce semi-structured and structured data, providing a concise, readable, and efficient way to perform data manipulation and advanced analysis.
September 2024Support for selecting from a stored procedure that returns tabular data.You can simplify the SQL statements for saving results to a table. For example, rather than using the SQLID Snowflake Scripting variable with the RESULT_SCAN function to create a table containing the query results, you can use a query that directly selects from the results.
September 2024Support extended for RANGE BETWEEN window frames with explicit offsets (n PRECEDING and n FOLLOWING) to include the FIRST_VALUE and LAST_VALUE window functions.You can use additional functions to run moving aggregations when expected or unexpected missing records cause gaps to occur in time-series data sets.
August 2024RANGE BETWEEN window frames with explicit offsets (n PRECEDING and n FOLLOWING) are now generally available and are no longer in preview.You can more easily run moving aggregations when expected or unexpected missing records cause gaps to occur in time-series data sets.
August 2024Preview support for full-text search with the SEARCH function and the SEARCH_IP function.You can find character data (text) and IPv4 addresses in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns.
August 2024Support for using the ILIKE and EXCLUDE keywords for filtering in a SELECT list or GROUP BY clause in function calls and object constants.In function calls and object constants, you can filter for columns that match a pattern, and you can exclude specific columns.
July 2024Support for specifying wildcards in OBJECT constants for filtering in a SELECT list or GROUP BY clause.You can construct an OBJECT value from the specified data using the attribute names as keys and the associated values as values.
June 2024Preview support for RANGE BETWEEN window frames with explicit offsets (n PRECEDING and n FOLLOWING) for the following window functions: AVG, COUNT, MIN, MAX and SUM.You can more easily run moving aggregations when expected or unexpected missing records cause gaps to occur in time-series data sets.
May 2024Support for using the { INCLUDE | EXCLUDE } NULLS option in an UNPIVOT subclause to specify whether to include rows with NULL values in the results.You have more flexibility when you use the UNPIVOT subclause in a SQL statement.
May 2024Support for using the TABLE keyword to get a reference to a table, view, secure view, or query and to call a method in a class in the FROM clause.You can use the TABLE keyword to write simpler SQL statements.
May 2024New ASOF JOIN construct.You can write simpler SQL statements to join tables that contain time-series data.
May 2024Support for specifying the ANY keyword or a subquery with the PIVOT construct.You can easily pivot on all distinct values or on all values returned by a subquery.
May 2024Support for the FILTER and TRANSFORM higher-order functions.You can use lambda expressions to filter and transform semi-structured and structured data, providing a concise, readable, and efficient way to perform data manipulation and advanced analysis.
March 2024New GREATEST_IGNORE_NULLS and LEAST_IGNORE_NULLS functions.You can return the lowest or highest non-NULL value from a list of expressions.
March 2024Support for trailing commas in SELECT lists.You can delete or move the last columns in a SELECT list without having to delete the preceding comma.
February 2024Support for the upper, lower, and trim collations in additional SQL functions.You can pass strings that use the upper, lower, and trim collations to these functions without having to change the collation.