I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.
In Snowflake, there are several methods to create a table. You obviously have the standard DDL statement CREATE TABLE, but you also have the CREATE TABLE AS SELECT, or CTAS for the friends. An example:
It’s a real efficient way to create a new table based on the output of a SELECT statement. This doesn’t exist (yet?) in SQL Server, but it is available in Azure SQL Data Warehouse (and its on-premises siblings, whatever they are called these days). It is however very similar to the SELECT … INTO construct in SQL Server. The upside is performance, but the downside is that you cannot define additional constraints such as NOT NULL, defaults, IDENTITY, primary key et cetera. UPDATE: Sam has pointed out in the comments that this is possible, which is great news. I should read the docs more carefully 🙂
Another method to create a table is copying the structure of an already existing table, using the CREATE TABLE … LIKE (my colleague Bart pointed this out to me). With the LIKE keyword, you specify the table from which you want to copy the DDL.
CREATE TABLE dbo.TagsTemp LIKE dbo.Tags;
In this case, the dbo.TagsTemp table will be created with the same column structure as dbo.Tags, but also inheriting all constraints, defaults … However, data is not copied. It’s similar to SELECT … INTO … WHERE 1 = 0 construct, where you create a table using a result set but use the WHERE clause to filter out all rows. But as said before, this doesn’t copy any constraints at all.
The final option is to take a CLONE of an existing table.
CREATE TABLE dbo.TagsTemp CLONE dbo.Tags;
This is where Snowflake as a cloud data warehouse really shines. Creating a clone doesn’t copy the data, but instead creates an empty structure with pointers to the original data: a zero-copy clone. Of course, this is blazingly fast. In fact, using CLONE isn’t limited to tables only, but you can also clone schema’s and entire databases. This is ideal for quickly creating a dev environment by cloning your production environment. I might elaborate more on this in a future blog post. In SQL Server, database snapshots come close to this functionality, but in SQL Server you cannot edit the snapshot, while in Snowflake the clone acts like a newly created table/schema/database with its own data.
Finally, all CREATE TABLE variants can either use CREATE OR REPLACE TABLE or CREATE TABLE IF NOT EXISTS. With the first option, you will overwrite an existing table (and losing all data in the process), while with the second option you the CREATE TABLE statement will not be executed if the table already exists.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Good series.
Regarding the downside to CTAS, I was able to do this:
create or replace table staging.test_sk_ctas (
id int primary key,
pid int constraint fk_id foreign key references staging.test_parent(id) not null,
val int not null,
unique(val)
) as
select 1 as id, 2 as pid, 20 as val;
Wondering if I am missing something.
Hi Sam,
thanks for letting me know. I’ve updated the post.
Regards,
Koen