Snowflake series : Differences between Microsoft SQL en Snowflake

Introduction

Although, there is lot of support for (ANSI) SQL support in Snowflake there are always the nittygritty things that I always assumed in Microsoft SQL doesn't work in Snowflake. This blogpost is a collection of these differences between Microsoft SQL. This applicable for SQL Server (on premise), Azure SQL Database and Azure Synapse. 

Azure SQL Database to Snowflake conversion issues

There are some differences with the comma between Microsoft SQL and Snowflake. It seems that Microsoft SQL is less strict with commas than Snowflake.

Azure SQL Database  (and other SQL Server versions)

In this example, there is NOT a comma between the NULL and the CONSTRAINT. To my surprise Microsoft SQL code is accepted.

DROP TABLE IF EXISTS dbo.test;

CREATE TABLE dbo.test(
    ID	      VARCHAR (50)  NOT NULL,
    Oms       VARCHAR (255)     NULL
    CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED (ID ASC)
);

Results in :

Snowflake 

But when I try this in Snowflake it is not accepted and it results in an error.

CREATE TABLE test(
    ID	      VARCHAR (50)  NOT NULL,
    Oms       VARCHAR (255)     NULL
    CONSTRAINT PK_ID PRIMARY KEY (ID)
);

Results in  :

Azure SQL Database  (and other SQL Server versions)

Another example is the following script. Look at the last line with , . Strange at first sight and I didn't expect that Microsoft SQL accepts kind of code, but surprise surprise, no errors.

DROP TABLE IF EXISTS dbo.test2;

CREATE TABLE dbo.test2(
    ID	      VARCHAR (50)  NOT NULL,
    oms       VARCHAR (255)     NULL,
);

Results in :

Snowflake 

The same code in Snowflake results in an error.

DROP TABLE IF EXISTS test2;

CREATE TABLE test2(
    ID	      VARCHAR (50)  NOT NULL,
    oms       VARCHAR (255)     NULL,
);

Results in :

Final thoughts 

This is a small blogpost about some experiences with converting Microsoft SQL code (SQL Server, Azure SQL Database and Synapse) to Snowflake.

Hennie