Summary of Supported SQL

Snowflake supports most SQL defined in SQL-99, as well as parts of the SQL-2003 analytic extensions.

In this Topic:

Data Types

The following tables list the supported and unsupported data types in Snowflake.

Supported Data Types

Category Type Notes
Numeric Data Types NUMBER Default precision and scale are (38,0).
DECIMAL Synonymous with NUMBER.
NUMERIC Synonymous with NUMBER.
INT, INTEGER, BIGINT, SMALLINT Synonymous with NUMBER except precision and scale cannot be specified.
FLOAT, FLOAT4, FLOAT8 [1]  
DOUBLE [1] Synonymous with FLOAT.
DOUBLE PRECISION [1] Synonymous with FLOAT.
REAL [1] Synonymous with FLOAT.
String & Binary Data Types VARCHAR Default (and maximum) is 16,777,216 bytes.
CHAR, CHARACTER Synonymous with VARCHAR except default length is VARCHAR(1).
STRING Synonymous with VARCHAR.
TEXT Synonymous with VARCHAR.
BINARY  
VARBINARY Synonymous with BINARY.
Logical Data Types BOOLEAN Currently only supported for accounts provisioned after January 25, 2016.
Date & Time Data Types DATE  
DATETIME Alias for TIMSTAMP_NTZ
TIME  
TIMESTAMP Alias for one of the TIMESTAMP variations (TIMESTAMP_NTZ by default).
TIMESTAMP_LTZ TIMESTAMP with local time zone; time zone, if provided, is not stored.
TIMESTAMP_NTZ TIMESTAMP with no time zone; time zone, if provided, is not stored.
TIMESTAMP_TZ TIMESTAMP with time zone.
Semi-structured Data Types VARIANT  
OBJECT  
ARRAY  

[1] A known issue in Snowflake displays FLOAT, FLOAT4, FLOAT8, REAL, DOUBLE, and DOUBLE PRECISION as FLOAT even though they are stored as DOUBLE.

Unsupported Data Types

Category Type Notes
LOB (Large Object) BLOB BINARY can be used instead; maximum of 8,388,608 bytes.
CLOB VARCHAR can be used instead; maximum of 16,777,216 bytes (for single-byte).
Other ENUM  
User-defined data types  

Query Constructs

The following table lists the supported query constructs in Snowflake.

Category Constructs Notes
Query Constructs [ WITH ]  
SELECT [ DISTINCT ] Required.
FROM Required.
[ AT | BEFORE ]
 
[ SAMPLE ]
 
[ JOIN ]
Inner, left outer, and right outer joins.
[ WHERE ]  
[ GROUP BY ] Columns used in GROUP BY must be in SELECT list.
[ HAVING ]  
[ ORDER BY ]  
[ VALUES ]  
[ LIMIT ]  
Query Operators INTERSECT [ ALL ]  
MINUS [ ALL ] / EXCEPT  
UNION [ ALL ]  
Predicates BETWEEN  
IN  
EXISTS  
LIKE  

DDL and DML Commands

The following tables list the DDL and DML commands supported in Snowflake.

Account and Session-based DDL

Category Commands Notes
Account ALTER Used to set account-level parameters (only for users with ACCOUNTADMIN role).
Session ALTER , USE ROLE | WAREHOUSE | DATABASE | SCHEMA  
Parameters SHOW Used to view parameters settings for the account or session.
Functions SHOW Displays both system-defined functions and UDFs.
SQL Variables SET , UNSET , SHOW  
Transactions BEGIN , COMMIT , ROLLBACK , SHOW , SHOW LOCKS Used for multi-statement transactions.

Object-based DDL

Category Commands Notes
All Objects COMMENT Comments can also be set using CREATE or ALTER for any object.
Roles ALTER , CREATE , DESC , DROP , SHOW , USE  
Users ALTER , CREATE , DESC , DROP , SHOW  
Privileges GRANT , REVOKE , SHOW Used for enabling and managing access control for objects.
Resource Monitors ALTER , CREATE , DESC , DROP , SHOW  
Virtual Warehouses ALTER , CREATE , DESC , DROP , SHOW , USE  
Databases ALTER , CREATE , DESC , DROP , SHOW , USE , UNDROP CREATE also supports cloning existing databases.
Schemas ALTER , CREATE , DESC , DROP , SHOW , USE , UNDROP CREATE also supports cloning existing schemas.
Shares ALTER , CREATE , DESC , DROP , SHOW  
Tables ALTER , CREATE , DESC , DROP , SHOW , TRUNCATE , UNDROP CREATE also supports cloning existing tables, using CTAS to create populated tables, and creating empty tables from existing tables.
Views ALTER , CREATE , DESC , DROP , SHOW  
Sequences ALTER , CREATE , DESC , DROP , SHOW CREATE also supports cloning existing sequences.
File Formats ALTER , CREATE , DESC , DROP , SHOW CREATE also supports cloning existing file formats.
Stages ALTER , CREATE , DESC , DROP , SHOW CREATE also supports cloning existing stages (external stages only).
User-Defined Functions ALTER , CREATE , DESC , DROP , SHOW Used for creating and managing UDFs.

DML

Category Commands Notes
General DELETE , INSERT , MERGE , REPLACE , UPDATE INSERT includes support for multi-table inserts.
Data Import (Loading) COPY INTO table , LIST , PUT , REMOVE  
Data Export (Unloading) COPY INTO location , GET , LIST , REMOVE  

Operators and Functions

The following tables list the supported functions, operators and expressions in Snowflake.

Operators

Category/Sub-category Operators
Arithmetic Operators + , - , * , / , %
Comparison Operators = , != , <> , < , <= , > , >=
Logical Operators AND , NOT , OR

Scalar Functions

Category/Sub-category Functions
Bitwise Expression Functions BITAND , BITOR , BITNOT , BITSHIFTLEFT , BITSHIFTRIGHT , BITXOR (see also: Bitwise Aggregation Functions)
Conditional Expression Functions BETWEEN , CASE , COALESCE , DECODE , EQUAL_NULL , GREATEST , IFF , IFNULL , [ NOT ] IN , IS [ NOT ] DISTINCT FROM , IS [ NOT ] NULL , IS_NULL_VALUE , LEAST , NULLIF , NVL , NVL2 , REGR_VALX , REGR_VALY , ZEROIFNULL
Numeric Functions  
Rounding & Truncation
ABS , CEIL , FLOOR , MOD , ROUND , SIGN , TRUNC / TRUNCATE
Exponent & Root
CBRT , EXP , POW / POWER , SQRT , SQUARE
Logarithmic
LN , LOG
Trigonometric
ACOS , ACOSH , ASIN , ASINH , ATAN , ATAN2 , ATANH , COS , COSH , COT , DEGREES , HAVERSINE , PI , RADIANS , SIN , SINH , TAN , TANH
String & Binary Functions  
General
ASCII , BIT_LENGTH , CHARINDEX , CHR / CHR , CONCAT / || , CONTAINS , EDITDISTANCE , ENDSWITH , INSERT , LEFT , LENGTH , LIKE , LOWER , LPAD , LTRIM , OCTECT_LENGTH , PARSE_URL POSITION , REPEAT , REPLACE , REVERSE , RIGHT , RPAD , RTRIM , RTRIMMED_LENGTH , SPACE , SPLIT , SPLIT_PART , STARTSWITH , SUBSTR / SUBSTRING , TRANSLATE , TRIM , UPPER
Encode & Decode
BASE64_DECODE_BINARY , BASE64_DECODE_STRING , BASE64_ENCODE , HEX_DECODE_BINARY , HEX_DECODE_STRING , HEX_ENCODE , TRY_* ( decode binary and string functions )
Crytographic Hash
MD5 / MD5_HEX , MD5_BINARY , SHA1 / SHA1_HEX , SHA1_BINARY , SHA2 / SHA2_HEX , SHA2_BINARY
Regular Expression (String) Functions REGEXP , REGEXP_COUNT , REGEXP_INSTR , REGEXP_LIKE , REGEXP_REPLACE , REGEXP_SUBSTR , RLIKE
Conversion Functions CAST , TO_* (all supported Snowflake data types) , TRY_CAST , TRY_TO_* ( numeric, Boolean, date & time data types )
Context Functions  
General
CURRENT_* ( CLIENT | DATE | SESSION | TIME | TIMESTAMP | VERSION ) , LOCALTIME , LOCALTIMESTAMP
Session Object
CURRENT_* ( DATABASE | ROLE | SCHEMA | SCHEMAS | WAREHOUSE | USER )
Session Transaction
CURRENT_* ( SESSION | STATEMENT | TRANSACTION ) , LAST_QUERY_ID , LAST_TRANSACTION
Date & Time Functions  
Construction
DATE_FROM_PARTS / DATEFROMPARTS , TIME_FROM_PARTS / TIMEFROMPARTS , TIMESTAMP_FROM_PARTS / TIMESTAMPFROMPARTS
Extraction
DATE_PART , DAY , DAYNAME , EXTRACT , HOUR , LAST_DAY , MINUTE , MONTH , MONTHNAME , QUARTER , SECOND, WEEK , YEAR
Addition/Subtraction
ADD_MONTHS , DATEADD, DATEDIFF , TIMEADD, TIMEDIFF , TIMESTAMPADD , TIMESTAMPDIFF
Truncation
DATE_TRUNC , TRUNC
Type Conversion
TO_DATE , TO_TIME , TO_TIMESTAMP , TO_TIMESTAMP_* ( LTZ | NTZ | TZ )
Time Zone
CONVERT_TIMEZONE
Semi-structured Data Functions  
Parsing
CHECK_JSON , CHECK_XML , PARSE_JSON , PARSE_XML
Array
ARRAY_AGG , ARRAY_APPEND , ARRAY_CAT , ARRAY_COMPACT , ARRAY_CONSTRUCT , ARRAY_CONSTRUCT_COMPACT , ARRAY_INSERT , ARRAY_PREPEND , ARRAY_SIZE , ARRAY_SLICE , ARRAY_TO_STRING
Object
OBJECT_AGG , OBJECT_CONSTRUCT , OBJECT_INSERT , OBJECT_DELETE
Data Extraction
GET , GET_PATH , XMLGET
Casts
AS_* (all data types) , TO_ARRAY , TO_JSON , TO_OBJECT , TO_VARIANT, TO_XML
Type Predicates
IS_* (all data types) , TYPEOF
Aggregate Functions  
General
ANY_VALUE , ARRAY_AGG , AVG , CORR , COUNT , COVAR_POP , COVAR_SAMP , GROUPING , GROUPING_ID , HASH_AGG , LIST_AGG , MAX , MEDIAN , MIN , OBJECT_AGG , PERCENTILE_CONT , PERCENTILE_DISC , STDDEV_POP , STDDEV_SAMP , SUM , VAR_POP / VARIANCE_POP , VAR_SAMP / VARIANCE_SAMP / VARIANCE
Bitwise
BITAND_AGG , BITOR_AGG , BITXOR_AGG
Linear Regression
REGR_AVGX , REGR_AVGY , REGR_COUNT , REGR_INTERCEPT , REGR_R2 , REGR_SLOPE , REGR_SXX , REGR_SXY , REGR_SYY
Cardinality Estimation
APPROX_COUNT_DISTINCT , HLL , HLL_ACCUMULATE , HLL_COMBINE , HLL_ESTIMATE , HLL_EXPORT , HLL_IMPORT
Analytic/Window Functions CUME_DIST , DENSE_RANK , FIRST_VALUE , LAG , LAST_VALUE , LEAD , NTILE , PERCENT_RANK , RANK , ROW_NUMBER
Miscellaneous Functions  
System
SYSTEM$ABORT_SESSION , SYSTEM$ABORT_TRANSACTION , SYSTEM$CANCEL_ALL_QUERIES , SYSTEM$CANCEL_QUERY , SYSTEM$CLUSTERING_DEPTH , SYSTEM$CLUSTERING_INFORMATION , SYSTEM$CLUSTERING_RATIO , SYSTEM$TYPEOF , SYSTEM$WAIT
Utility
GET_DDL , HASH
Data Generation
RANDOM , SEQ1 / SEQ2 / SEQ4 / SEQ8
Random Distribution
NORMAL , RANDSTR , UNIFORM , ZIPF

Other Types of Functions

Category/Sub-category Functions
Table Functions  
Data Loading
VALIDATE
Data Generation
GENERATOR
Semi-structured data
FLATTEN
Table Functions (Information Schema)  
Query History
QUERY_HISTORY , QUERY_HISTORY_BY_* ( SESSION | USER | WAREHOUSE )
Usage History
DATABASE_STORAGE_USAGE_HISTORY , STAGE_STORAGE_USAGE_HISTORY , WAREHOUSE_METERING_HISTORY
User Login History
LOGIN_HISTORY , LOGIN_HISTORY_BY_USER
User-Defined Functions SQL UDFs (scalar and table) , JavaScript UDFs (scalar only)