SQL improvements¶

Snowflake is continually introducing enhancements that make it easier to write queries. With these new keywords and functions, you can write simpler, shorter SELECT statements.

SQL improvements in 2024¶

The following SQL improvements were introduced in 2024:

Date released	Improvement	Impact
October 2024	Support 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. Note that you still can’t store objects larger than 16 MB in a column.
October 2024	Support 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 2024	Support 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 2024	Preview support 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 2024	Preview 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 2024	Support 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 2024	Support 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 2024	Preview 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 2024	Support 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 2024	Preview of support for CREATE OR ALTER TABLE and CREATE OR ALTER TASK.	You can combine the functionality of the CREATE command and the ALTER command.
May 2024	Support 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 2024	New ASOF JOIN construct.	You can write simpler SQL statements to join tables that contain time-series data.
May 2024	Support 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 2024	Support 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 2024	New GREATEST_IGNORE_NULLS and LEAST_IGNORE_NULLS functions.	You can return the lowest or highest non-NULL value from a list of expressions.
March 2024	Support 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 2024	Support 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.

SQL improvements in 2023¶

The following SQL improvements were introduced in 2023:

Date released	Improvement	Impact
August 2023	New ARRAY_MIN, ARRAY_MAX, and ARRAY_SORT functions.	You can now easily select the array elements with the lowest value and the highest value. You can easily get a sorted array of elements.
August 2023	New ILIKE and REPLACE parameters in the SELECT command.	You can now select all columns that match a pattern containing SQL wildcards. When selecting all columns, you can replace the value of specific columns with expressions.
July 2023	New ALL keyword in the GROUP BY construct.	You can group results by all non-aggregate columns in the SELECT list without having to specify each column by name.
February 2023	Support for bankers’ rounding (rounding half to even (link removed)) in the ROUND function.	You can now use bankers’ rounding when rounding values.
January 2023	New MIN_BY and MAX_BY functions.	You can find the row containing the minimum or maximum value in a column and retrieve the value from a different column.

SQL improvements in 2022¶

The following SQL improvements were introduced in 2022:

Date released	Improvement	Impact
November 2022	New EXCLUDE and RENAME parameters in the SELECT command.	You can now select all columns and specify that you want to exclude or rename specific columns.
November 2022	New ARRAY_EXCEPT and ARRAY_DISTINCT functions.	You can now easily select the array elements that are in one array but not in another array. You can easily get the distinct elements in an array.
May 2022	New REGEXP_SUBSTR_ALL function.	You can now easily extract the substrings that match a regular expression from a string..

Date released

Improvement

Impact

November 2022

New EXCLUDE and RENAME parameters in the SELECT command.

You can now select all columns and specify that you want to exclude or rename specific columns.

November 2022

New ARRAY_EXCEPT and ARRAY_DISTINCT functions.

You can now easily select the array elements that are in one array but not in another array.

You can easily get the distinct elements in an array.

May 2022

New REGEXP_SUBSTR_ALL function.

You can now easily extract the substrings that match a regular expression from a string..