TransactSQL
Snowflake
Notes
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
AVG
AVG
CHECKSUM_AGG
*to be defined
COUNT
COUNT
COUNT_BIG
*to be defined
GROUPING
GROUPING
GROUPING_ID
GROUPING_ID
MAX
MAX
MIN
MIN
STDEV
STDDEV, STDEV_SAMP
STDEVP
STDDEV_POP
SUM
SUM
VAR
VAR_SAMP
VARP
VAR_POP
TransactSQL
Snowflake
Notes
CUME_DIST
CUME_DIST
FIRST_VALUE
FIRST_VALUE
LAG
LAG
LAST_VALUE
LAST_VALUE
LEAD
LEAD
PERCENTILE_CONT
PERCENTILE_CONT
PERCENTILE_DISC
PERCENTILE_DISC
PERCENT_RANK
PERCENT_RANK
TransactSQL
Snowflake
Notes
COLLATIONPROPERTY
*to be defined
TERTIARY_WEIGHTS
*to be defined
TransactSQL
Snowflake
Notes
@@DBTS
*to be defined
@@LANGID
*to be defined
@@LANGUAGE
*to be defined
@@LOCK_TIMEOUT
*to be defined
@@MAX_CONNECTIONS
*to be defined
@@MAX_PRECISION
*to be defined
@@NESTLEVEL
*to be defined
@@OPTIONS
*to be defined
@@REMSERVER
*to be defined
@@SERVERNAME
CONCAT('app.snowflake.com', CURRENT_ACCOUNT( ))
@@SERVICENAME
*to be defined
@@SPID
*to be defined
@@TEXTSIZE
*to be defined
@@VERSION
*to be defined
Can be mimicked by using CURRENT_VERSION
TransactSQL
Snowflake
Notes
CAST
CAST
Returns NULL if the value isn't a number, otherwise returns the numeric value as its. When using operators such as <, >, =, <> then must be follow by a NULL
CONVERT
Check CONVERT
Same behavior as CAST
PARSE
*to be defined
TRY_CAST
TRY_CAST
Returns NULL if the value isn't a number, otherwise returns the numeric value as its. When using operators such as <, >, =, <> then must be follow by a NULL
*to be defined
Same behavior as TRY_CAST
TRY_PARSE
TRY_CAST
Behavior may be different when parsing an integer as date or timestamp.
TransactSQL
Snowflake
Notes
ASYMKEY_ID
*to be defined
ASYMKEYPROPERTY
*to be defined
CERTENCODED
*to be defined
CERTPRIVATEKEY
*to be defined
DECRYPTBYASYMKEY
*to be defined
DECRYPTBYCERT
*to be defined
DECRYPTBYKEY
*to be defined
DECRYPTBYKEYAUTOASYMKEY
*to be defined
DECRYPTBYKEYAUTOCERT
*to be defined
DECRYPTBYPASSPHRASE
*to be defined
Can be mimicked by using DENCRYPT_RAW
ENCRYPTBYASYMKEY
*to be defined
ENCRYPTBYCERT
*to be defined
ENCRYPTBYKEY
*to be defined
ENCRYPTBYPASSPHRASE
*to be defined
Can be mimicked by using ENCRYPT_RAW
HASHBYTES
MD5, SHA1, SHA2
Currently only supported separated hash. Use proper one according to the required algorithm
MD5, is a 32-character hex-encoded
SHA1, has a 40-character hex-encoded string containing the 160-bit
SHA2, a hex-encoded string containing the N-bit SHA-2 message digest. Sizes are:
224 = SHA-224
256 = SHA-256 (Default)
384 = SHA-384
512 = SHA-512
IS_OBJECTSIGNED
*to be defined
KEY_GUID
*to be defined
KEY_ID
*to be defined
KEY_NAME
*to be defined
SIGNBYASYMKEY
*to be defined
SIGNBYCERT
*to be defined
SYMKEYPROPERTY
*to be defined
VERIGYSIGNEDBYCERT
*to be defined
TransactSQL
Snowflake
Notes
@@CURSOR_ROWS
*to be defined
@@FETCH_STATUS
*to be defined
CURSOR_STATUS
*to be defined
TransactSQL
Snowflake
Notes
DATALENGTH
OCTET_LENGTH
Snowflake doesn’t use fractional bytes so length is always calculated as 8 * OCTET_LENGTH
IDENT_SEED
*to be defined
IDENT_CURRENT
*to be defined
IDENTITY
*to be defined
IDENT_INCR
*to be defined
SQL_VARIANT_PROPERTY
*to be defined
TransactSQL
Snowflake
Notes
@@DATEFIRST
*to be defined
@@LANGUAGE
*to be defined
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
CURRENT_TIMEZONE
*to be defined
DATEADD
DATEADD
DATEDIFF
DATEDIFF
DATEDIFF_BIG
*to be defined
DATEFROMPARTS
DATE_FROM_PARTS
DATENAME
*to be defined
This function receives two arguments: a datepart and date. It returns a string. Here are the supported dateparts from TSQL to Snowflake
year, yyyy, yy -> DATE_PART(YEAR, "$date") quarter, qq, q -> DATE_PART(QUARTER, "$date") month, mm, m -> MONTHNAME( "$date"), thou only providing a three-letter english month name dayofyear, dy, y -> DATE_PART(DAYOFYEAR, "$date") day, dd, d -> DATE_PART(DAY, "$date") week, wk, ww -> DATE_PART(WEEK, "$date")
weekday, dw -> DAYNAME("$date"), thou only providing an three-letter english day name hour, hh -> DATE_PART(HOUR, "$date") minute, n -> DATE_PART(MINUTE, "$date") second, ss, s -> DATE_PART(SECOND, "$date") millisecond, ms -> DATE_PART(MS, "$date") microsecond, mcs -> DATE_PART(US, "$date") nanosecond, ns -> DATE_PART(NS, "$date") TZoffset, tz -> needs a special implementation to get the time offset
DATEPART
DATE_PART
DATETIME2FROMPARTS
*to be defined
DATETIMEFROMPARTS
*to be defined
Can be mimicked by using a combination of DATE_FROM_PARTS and TIME_FROM_PARTS
DATETIMEOFFSETFROMPARTS
*to be defined
DAY
DAY
EOMONTH
*to be defined
Can be mimicked by using LAST_DAY
FORMAT
*to be defined
Maps to TO_CHAR
GETDATE
GETDATE
GETUTCDATE
*to be defined
Can be mimicked by using CONVERT_TIMEZONE
ISDATE
*to be defined
Can be mimicked by using TRY_TO_DATE
Returns NULL if the value isn't a date, otherwise returns the date value as its. When using operators such as <, >, =, <> then must be follow by a NULL
MONTH
MONTH
SMALLDATETIMEFROMPARTS
*to be defined
Can be mimicked by using a combination of DATE_FROM_PARTS and TIME_FROM_PARTS
SWITCHOFFSET
*to be defined
Can be mimicked by using CONVERT_TIMEZONE
SYSDATETIME
LOCALTIME
SYSDATETIMEOFFSET
*to be defined
Can be mimicked by using CONVERT_TIMEZONE and LOCALTIME
SYSUTCDATETIME
*to be defined
Can be mimicked by using CONVERT_TIMEZONE and LOCALTIME
TIMEFROMPARTS
TIME_FROM_PARTS
TODATETIMEOFFSET
*to be defined
Can be mimicked by using CONVERT_TIMEZONE
YEAR
YEAR
TransactSQL
Snowflake
Notes
ISJSON
CHECK_JSON
This is a 'preview feature' in Snowflake
JSON_VALUE
*to be defined
Can be mimic by using
TO_VARCHAR(GET_PATH(PARSE_JSON(JSON), PATH))
JSON_QUERY
*to be defined
JSON_MODIFY
*to be defined
TransactSQL
Snowflake
Notes
ABS
ABS
ACOS
ACOS
ASIN
ASIN
ATAN
ATAN
ATN2
ATAN2
CEILING
CEIL
COS
COS
COT
COT
DEGREES
DEGREES
EXP
EXP
FLOOR
FLOOR
LOG
LN
LOG10
LOG
PI
PI
POWER
POWER
RADIANS
RADIANS
RAND
RANDOM
ROUND
ROUND
SIGN
SIGN
SIN
SIN
SQRT
SQRT
SQUARE
SQUARE
TransactSQL
Snowflake
Notes
CHOOSE
*to be defined
Can be mimic by using DECODE
GREATEST
GREATEST
IIF
IIF
LEAST
LEAST
NULLIF
NULLIF
TransactSQL
Snowflake
Notes
@@PROCID
*to be defined
APP_NAME
*to be defined
APPLOCK_MODE
*to be defined
APPLOCK_TEST
*to be defined
ASSEMBLYPROPERTY
*to be defined
COL_LENGTH
*to be defined
COL_NAME
*to be defined
COLUMNPROPERTY
*to be defined
DATABASE_PRINCIPAL_ID
*to be defined
Maps to CURRENT_USER when no args
DATABASEPROPERTYEX
*to be defined
DB_ID
*to be defined
We recommend changing to CURRENT_DATABASE(). If there is a need to emulate this functionality. SELECT DATE_PART(EPOCH,CREATED) FROM INFORMATION_SCHEMA.DATABASES WHERE DATABASE_NAME = 'DB' ; Can achieve something similar
DB_NAME
*to be defined
Mostly used in the procedurename mentioned above
FILE_ID
*to be defined
FILE_IDEX
*to be defined
FILE_NAME
*to be defined
FILEGROUP_ID
*to be defined
FILEGROUP_NAME
*to be defined
FILEGROUPPROPERTY
*to be defined
FILEPROPERTY
*to be defined
FULLTEXTCATALOGPROPERTY
*to be defined
FULLTEXTSERVICEPROPERTY
*to be defined
INDEX_COL
*to be defined
INDEXKEY_PROPERTY
*to be defined
INDEXPROPERTY
*to be defined
NEXT VALUE FOR
*to be defined
OBJECT_DEFINITION
*to be defined
OBJECT_ID
*to be defined
In most cases can be replaced. Most cases are like: IF OBJECT_ID('dbo.TABLE') IS NOT NULL DROP TABLE dbo.Table which can be replaced by a DROP TABLE IF EXISTS (this syntax is also supported in SQL SERVER). If the object_id needs to be replicated, a UDF is added depending on the second parameter of the function call.
OBJECT_NAME
*to be defined
Can be replaced by: CREATE OR REPLACE PROCEDURE FOO() RETURNS STRING LANGUAGE JAVASCRIPT AS ' var rs = snowflake.execute({sqlText:SELECT CURRENT_DATABASE() || '.' || ?, binds:[arguments.callee.name]}); rs.next(); var procname = rs.getColumnValue(1); return procname; ';
OBJECT_NAME(@@PROCID)
'ObjectName'
This transformation only occurs when it is inside a DeclareStatement.
ObjectName is the name of the TopLevelObject that contains the Function.
OBJECT_SCHEMA_NAME
*to be defined
OBJECT_SCHEMA_NAME(@@PROCID)
:OBJECT_SCHEMA_NAME
This transformation only occurs when it is inside a DeclareStatement.
OBJECTPROPERTY
*to be defined
OBJECTPROPERTYEX
*to be defined
ORIGINAL_DB_NAME
*to be defined
PARSENAME
PARSENAME_UDF
It creates a UDF to emulate the same behavior of Parsename function.
*to be defined
SCHEMA_NAME
*to be defined
SCOPE_IDENTITY
*to be defined
It this is needed I would recommend to use sequences, and capture the value before insert
SERVERPROPERTY
*to be defined
STATS_DATE
*to be defined
TYPE_ID
*to be defined
TYPE_NAME
*to be defined
TYPEPROPERTY
*to be defined
VERSION
*to be defined
TransactSQL
Snowflake
Notes
DENSE_RANK
DENSE_RANK
NTILE
NTILE
RANK
RANK
ROW_NUMBER
ROW_NUMBER
TransactSQL
Snowflake
Notes
PUBLISHINGSERVERNAME
*to be defined
TransactSQL
Snowflake
Notes
OPENDATASOURCE
*to be defined
OPENJSON
*to be defined
QPENQUERY
*to be defined
OPENROWSET
*to be defined
OPENXML
OPENXML_UDF
User-defined function used as a equivalent behavior in Snowflake.
TransactSQL
Snowflake
Notes
CERTENCODED
*to be defined
CERTPRIVATEKEY
*to be defined
CURRENT_USER
CURRENT_USER
DATABASE_PRINCIPAL_ID
*to be defined
HAS_PERMS_BY_NAME
*to be defined
IS_MEMBER
*to be defined
Change to query INFORMATION_SCHEMA although the client might require defining new roles
IS_ROLEMEMBER
*to be defined
Snowflake's a similar function
IS_ROLE_IN_SESSION
IS_SRVROLEMEMBER
*to be defined
LOGINPROPERTY
*to be defined
ORIGINAL_LOGIN
*to be defined
PERMISSIONS
*to be defined
PWDCOMPARE
*to be defined
PWDENCRYPT
*to be defined
SCHEMA_ID
*to be defined
SCHEMA_NAME
*to be defined
SESSION_USER
*to be defined
SUSER_ID
*to be defined
SUSER_NAME
*to be defined
SUSER_SID
*to be defined
SUSER_SNAME
*to be defined
sys.fn_builtin_permissions
*to be defined
sys.fn_get_audit_file
*to be defined
sys.fn_my_permissions
*to be defined
SYSTEM_USER
*to be defined
USER_ID
*to be defined
USER_NAME
*to be defined
Maps to CURRENT_USER
TransactSQL
Snowflake
Notes
ASCII
ASCII
CHAR
CHR, CHAR
CHARINDEX
CHARINDEX
CONCAT
CONCAT
CONCAT_WS
CONCAT_WS
COALESCE
COALESCE
DIFFERENCE
*to be defined
FORMAT
*to be defined
LEFT
LEFT
LEN
LEN
LOWER
LOWER
LTRIM
LTRIM
NCHAR
*to be defined
PATINDEX
*to be defined
Map to REGEXP_INSTR
QUOTENAME
QUOTENAME_UDF
It creates a UDF to emulate the same behavior of Quotename function
REPLACE
REPLACE
REPLICATE
REPEAT
REVERSE
REVERSE
RIGHT
RIGHT
RTRIM
RTRIM
SOUNDEX
SOUNDEX
SPACE
*to be defined
STR
*to be defined
STRING_AGG
*to be defined
STRING_ESCAPE
*to be defined
STRING_SPLIT
SPLIT_TO_TABLE
STUFF
*to be defined
CREATE OR REPLACE FUNCTION STUFF(S string, STARTPOS int, LENGTH int, NEWSTRING string) RETURNS string LANGUAGE SQL AS ' left(S, STARTPOS) || NEWSTRING || substr(S, STARTPOS + LENGTH + 1) ';
SUBSTRING
SUBSTRING
TRANSLATE
TRANSLATE
TRIM
TRIM
UNICODE
UNICODE
UPPER
UPPER
TransactSQL
Snowflake
Notes
$PARTITION
*to be defined
@@ERROR
*to be defined
@@IDENTITY
*to be defined
It this is needed I would recommend to use sequences, and capture the value before insert
@@PACK_RECEIVED
*to be defined
@@ROWCOUNT
*to be defined
@@TRANCOUNT
*to be defined
BINARY_CHECKSUM
*to be defined
CHECKSUM
*to be defined
COMPRESS
COMPRESS
Snowflake's version has a method argument to indicate the compression method. These are the valid values: SNAPPY, ZLIB, ZSTD, BZ2
The compression level is specified in parentheses and must be a non-negative integer
CONNECTIONPROPERTY
*to be defined
CONTEXT_INFO
*to be defined
CURRENT_REQUEST_ID
*to be defined
CURRENT_TRANSACTION_ID
*to be defined
DECOMPRESS
*to be defined
Snowflake has two functions for these: DECOMPRESS_BINARY and DECOMPRESS_STRING
ERROR_LINE
*to be defined
Will map to ERROR_LINE helper. EXEC helper will capture the Exception line property from the stack trace.
ERROR_MESSAGE
SQLERRM
ERROR_NUMBER
*to be defined
Will map to ERROR_NUMBER helper. EXEC helper will capture the Exception code property.
ERROR_PROCEDURE
*to be defined
Will map to ERROR_PROCEDURE helper, taken from the arguments.callee.name procedure property
ERROR_SEVERITY
*to be defined
ERROR_STATE
*to be defined
Helper will capture Exception state property
FORMATMESSAGE
FORMATEMESSAGE_UDF
It creates a UDF to emulate the same behavior of FORMATMESSAGE function but with some limitations.
GET_FILESTREAM_TRANSACTION_CONTEXT
*to be defined
GETANSINULL
*to be defined
HOST_ID
*to be defined
HOST_NAME
*to be defined
ISNULL
NVL
ISNUMERIC
*to be defined
No direct equivalent but can be mapped to a custom UDF, returning the same values as in TSQL.
MIN_ACTIVE_ROWVERSION
*to be defined
NEWID
*to be defined
Maps to UUID_STRING
NEWSEQUENTIALID
*to be defined
ROWCOUNT_BIG
*to be defined
SESSION_CONTEXT
*to be defined
SESSION_ID
*to be defined
XACT_STATE
*to be defined
TransactSql
Snowflake
Notes
@@CONNECTIONS
*to be defined
Snowflake's a similar function: LOGIN_HISTORY.
Returns login events within a specified time range
@@PACK_RECEIVED
*to be defined
@@CPU_BUSY
*to be defined
@@PACK_SENT
*to be defined
@@TIMETICKS
*to be defined
@@IDLE
*to be defined
@@TOTAL_ERRORS
*to be defined
@@IO_BUSY
*to be defined
@@TOTAL_READ
*to be defined
@@PACKET_ERRORS
*to be defined
@@TOTAL_WRITE
*to be defined
TransactSQL
Snowflake
Notes
TEXTPTR
*to be defined
TEXTVALID
*to be defined
TransactSQL
Snowflake
Notes
COLUMNS_UPDATED
*to be defined
EVENTDATA
*to be defined
TRIGGER_NESTLEVEL
*to be defined
UPDATE
*to be defined
