Snowflake for SQL Server Users - Part 15 - Table types

Snowflake has a richer set of options for how tables are created, than we have in SQL Server.

CREATE TABLE Variants

As well as a CREATE TABLE statement as you would already be familiar with in SQL Server, Snowflake offers these variants:

CREATE TABLE tablename AS SELECT

This is basically similar to a SELECT INTO in SQL Server. It executes the SELECT query and creates a table from the results (including the data).

CREATE TABLE tablename LIKE

This is an interesting variant. It creates a table with the same schema as an existing table but without any data i.e. it creates a new empty table based upon the design of another table.

CREATE TABLE tablename CLONE

This is another interesting variant. It clones one table to create another table. It’s similar to the LIKE option but also includes all the data.

Table Types

Permanent

Permanent tables are standard table types that are pretty much the same as the equivalents in SQL Server.

TEMPORARY

This is similar to a temporary table in SQL Server. The table and its data are retained until the end of the user’s session. The syntax supports a concept of LOCAL TEMPORARY and GLOBAL TEMPORARY but these options have no affect. A standard TEMPORARY table is created.

Note that TEMPORARY can be abbreviated to TEMP, and has a synonym of VOLATILE.

TRANSIENT

These tables aren’t dropped at the end of a user session and stay until someone drops them. They are also visible to all users.

A key difference with them though is that they don’t offer the same level of protection as standard tables. They are more like an eventually-consistent table where you might lose data if the system fails. They should only be used for data that can easily be recreated if needed.

 

For an index to all posts in this series, see the first post here.

2019-11-15