Fabric : Create some tables in Synapse Data Warehouse

Introduction

It has been a while since I wrote my last blogpost. In the coming weeks and months I expect to invest some time in Microsoft Fabric. In the past years, I invested heavenly in Snowflake and I was and I still am enthousiastic about Snowflake. Now Microsoft has a competing product, at least according to some people and articles on the internet. And, yes, the ideas are comparable, but I also see some differences between Snowflake and Microsoft Fabric. In the coming period I'll elaborate on these differences and the things that are the same.

In this blogpost I'll discover the simple steps of creating a Synapse Data Warehouse.

The steps

After you have created Microsoft Fabric capacity and a workspace, you create a Synapse data warehouse with the following option, Show all : 

Then click on the "Warehouse (Preview)" option:

And give the data warehouse a name (in my case "WH_SynapseDemo"):

It takes a while to create the warehouse.

And when it's finished, creating the warehouse, the following window appears. It is ready now. 

And now let's try some SQL code in order to check if it works. I grabbed some where some SQL code from the very very old pubs database;-). This is the first snippet of the script: 

CREATE TABLE authors
(
   au_id          id
         CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
         CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED,
   au_lname       varchar(40)       NOT NULL,
   au_fname       varchar(20)       NOT NULL,
   phone          char(12)          NOT NULL
         DEFAULT ('UNKNOWN'),
   address        varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   zip            char(5)               NULL
         CHECK (zip like '[0-9][0-9][0-9][0-9][0-9]'),
   contract       bit               NOT NULL
)
GO

And the first error is the CHECK statement is not supported by the engine.

The CHECK keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 6, Code line 12

I've never used a CHECK statement in my SQL code. I removed the CHECK statement. The next error is : 

The PRIMARY KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 3, Code line 4

I removed that and the following error is : 

The DEFAULT keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 17, Code line 4

OK, Let's remove that and now it works. The following table is cleaned and not supported code is removed.

DROP TABLE IF EXISTS authors;
CREATE TABLE  authors
(
   au_id          varchar(40)   ,
   au_lname       varchar(40)       NOT NULL,
   au_fname       varchar(20)       NOT NULL,
   phone          char(12)          NOT NULL,
   address        varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   zip            char(5)               NULL,
   contract       bit               NOT NULL
);

Ok, There is not much left of the initial create table statement;-)

Next statement is the creation of  the publishers table.

DROP TABLE IF EXISTS publishers;
CREATE TABLE publishers
(
   pub_id         char(4)           NOT NULL,
   pub_name       varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   country        varchar(30)           NULL
);

That went well.

CREATE TABLE titles
(
   title_id       int,
   title          varchar(80)       NOT NULL,
   type           char(12)          NOT NULL
   pub_id         char(4)               NULL
         REFERENCES publishers(pub_id),
   price          money                 NULL,
   advance        money                 NULL,
   royalty        int                   NULL,
   ytd_sales      int                   NULL,
   notes          varchar(200)          NULL,
   pubdate        datetime          NOT NULL
)

An error occurs : 

The FOREIGN KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 5, Code line 1

The references statement is not supported in this way. 

Now let's try something else with the Keywords PRIMARY KEY and FOREIGN KEY.

CREATE TABLE titles
(
   title_id       int,
   title          varchar(80)       NOT NULL,
   type           char(12)          NOT NULL,
   pub_id         char(4)               NULL,
   price          money                 NULL,
   advance        money                 NULL,
   royalty        int                   NULL,
   ytd_sales      int                   NULL,
   notes          varchar(200)          NULL,
   pubdate        datetime          NOT NULL,
   PRIMARY KEY (title_id),
   FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)
)

That doesn't work either

The PRIMARY KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 3, Code line 1

And

The FOREIGN KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 5, Code line 1

So let's remove both the "Primary key" and "Foreign key" keywords

Next a table with a "money" data type (is not supported) : 

CREATE TABLE titles
(
   title_id       int,
   title          varchar(80)       NOT NULL,
   type           char(12)          NOT NULL,
   pub_id         char(4)               NULL,
   price          money                 NULL,
   advance        money                 NULL,
   royalty        int                   NULL,
   ytd_sales      int                   NULL,
   notes          varchar(200)          NULL,
   pubdate        datetime          NOT NULL
);

And I changed that into "dec(10,2)". Next the following error occurs at the "datetime" data type.

The data type 'datetime' is not supported in this edition of SQL Server.

Msg 24574, Level 16, State 1, Code line 33

I changed that into "datetime2" datatype, resulting in the following table :

DROP TABLE IF EXISTS titles;
CREATE TABLE titles
(
   title_id       varchar(80),
   title          varchar(80)       NOT NULL,
   type           char(12)          NOT NULL,
   pub_id         char(4)               NULL,
   price          DECIMAL(7,2)         NULL,
   advance        DECIMAL(7,2)         NULL,
   royalty        int                   NULL,
   ytd_sales      int                   NULL,
   notes          varchar(200)          NULL,
   pubdate        datetime2(0)          NOT NULL
);

Next the error :

The data type 'tinyint' is not supported in this edition of SQL Server.

Msg 24574, Level 16, State 1, Code line 48

CREATE TABLE titleauthor
(
   au_id          int,
   title_id       int,
   au_ord         tinyint               NULL,
   royaltyper     int                   NULL,
);

And I change that to "int".

Next the error :

The IDENTITY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

Msg 24584, Level 16, State 12, Code line 93

CREATE TABLE jobs
(
   job_id         smallint          IDENTITY(1,1),
   job_desc       varchar(50)       NOT NULL,
   min_lvl        tinyint           NOT NULL,
   max_lvl        tinyint           NOT NULL
);

I removed the identity column. Can we use a SEQUENCE instead? No, unfortunately not supported.

CREATE SEQUENCE is not supported.

Msg 15868, Level 16, State 24, Code line 1

And finally the last table with a couple of errors with the "image" and the "text" datatype :

CREATE TABLE pub_info
(
   pub_id         char(4)           NOT NULL,
   logo           image                 NULL,
   pr_info        text                  NULL
);

resulting in : 

The data type 'image' is not supported in this edition of SQL Server.

Msg 24574, Level 16, State 1, Code line 101

and 

The data type 'text' is not supported in this edition of SQL Server.

Msg 24574, Level 16, State 1, Code line 101

I tried to change the "text" datatype in to "varchar(max)" but that doesn't work either.

The data type 'varchar(max)' is not supported in this edition of SQL Server.

Msg 24574, Level 16, State 2, Code line 101

After a couple of adjustments in the script it finally approves the script.

9:37:58 AM

Commands completed successfully.

9:38:02 AM

Total execution time: 00:00:04.166

This is the final script. May be I have to change the script when I try to insert some data into. That's for a future blogpost.

SET NOCOUNT ON
GO
DROP TABLE IF EXISTS authors;
CREATE TABLE  authors
(
   au_id          varchar(40)   ,
   au_lname       varchar(40)       NOT NULL,
   au_fname       varchar(20)       NOT NULL,
   phone          char(12)          NOT NULL,
   address        varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   zip            char(5)               NULL,
   contract       bit               NOT NULL
);
DROP TABLE IF EXISTS publishers;
CREATE TABLE publishers
(
   pub_id         char(4)           NOT NULL,
   pub_name       varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   country        varchar(30)           NULL
);
DROP TABLE IF EXISTS titles;
CREATE TABLE titles
(
   title_id       varchar(80),
   title          varchar(80)       NOT NULL,
   type           char(12)          NOT NULL,
   pub_id         char(4)               NULL,
   price          DECIMAL(7,2)         NULL,
   advance        DECIMAL(7,2)         NULL,
   royalty        int                   NULL,
   ytd_sales      int                   NULL,
   notes          varchar(200)          NULL,
   pubdate        datetime2(0)          NOT NULL
);
DROP TABLE IF EXISTS titleauthor;
CREATE TABLE titleauthor
(
   au_id          varchar(100),
   title_id       varchar(80),
   au_ord         int               NULL,
   royaltyper     int                   NULL
);
DROP TABLE IF EXISTS stores;
CREATE TABLE stores
(
   stor_id        char(4)           NOT NULL,
   stor_name      varchar(40)           NULL,
   stor_address   varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   zip            char(5)               NULL
);
DROP TABLE IF EXISTS sales;
CREATE TABLE sales
(
   stor_id        char(4)           NOT NULL,
   ord_num        varchar(20)       NOT NULL,
   ord_date       datetime2(0)      NOT NULL,
   qty            smallint          NOT NULL,
   payterms       varchar(12)       NOT NULL,
   title_id       varchar(80)
);
DROP TABLE IF EXISTS roysched;
CREATE TABLE roysched
(
   title_id       varchar(80),
   lorange        int                   NULL,
   hirange        int                   NULL,
   royalty        int                   NULL
);
DROP TABLE IF EXISTS discounts;
CREATE TABLE discounts
(
   discounttype   varchar(40)       NOT NULL,
   stor_id        char(4)               NULL,
   lowqty         smallint              NULL,
   highqty        smallint              NULL,
   discount       dec(4,2)          NOT NULL
);
DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs
(
   job_id         smallint         ,
   job_desc       varchar(50)       NOT NULL,
   min_lvl        smallint           NOT NULL,
   max_lvl        smallint           NOT NULL
);
DROP TABLE IF EXISTS pub_info;
CREATE TABLE pub_info
(
   pub_id         char(4)           NOT NULL,
   logo           varbinary         NULL,
   pr_info        varchar(8000)     NULL
);
DROP TABLE IF EXISTS employee;
CREATE TABLE employee
(
   emp_id        varchar(40),
   fname          varchar(20)       NOT NULL,
   minit          char(1)               NULL,
   lname          varchar(30)       NOT NULL,
   job_id         smallint          NOT NULL,
   job_lvl        smallint,
   pub_id         char(4)           NOT NULL,
   hire_date      datetime2(0)         NOT NULL
)
GO

In the next blog I will add the other scripts like the INSERTs, VIEWs and STORED PROCEDURES.

Final Thoughts

Before using T-SQL in Synapse warehouse, I read the following warning : 

Microsoft Fabric is still in preview/public mode and It will generally available quite soon. One thing that bothers me is the autonumbering, for data warehouse solutions is quite mandatory to generate an autonumber. Hopefully Microsoft add this feature in the near future.

Here is a list of the unsupported statements in Microsoft Fabric Synapse data warehouse.

• ALTER TABLE ADD/ALTER/DROP COLUMN
• BULK LOAD
• CREATE ROLE
• CREATE SECURITY POLICY - Row Level Security (RLS)
• CREATE USER
• GRANT/DENY/REVOKE
• Hints
• Identity Columns
• Manually created multi-column stats
• MASK and UNMASK (Dynamic Data Masking)
• MATERIALIZED VIEWS
• MERGE
• OPENROWSET
• PREDICT
• Queries targeting system and user tables
• Recursive queries
• Result Set Caching
• Schema and Table names can't contain / or \
• SELECT - FOR (except JSON)
• SET ROWCOUNT
• SET TRANSACTION ISOLATION LEVEL
• sp_showmemo_xml
• sp_showspaceused
• sp_rename
• Temp Tables
• Triggers
• TRUNCATE

Hennie