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.
Permanent tables are standard table types that are pretty much the same as the equivalents in SQL Server.
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.
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.