Learning Snowflake SQL and Scripting

Chapter 5. Creating and Modifying Data

The first four chapters have been primarily concerned with the select statement. Now it’s time to switch gears and look at table creation along with the rest of the SQL data statements, including insert, update, delete, and merge.

Data Types

This section explores the different data types that may be used to store data in Snowflake.

Character Data

Storing character data in Snowflake is refreshingly simple; there is a single variable-length data type named varchar that can store up to 16 MB of character data. Data is stored using the Unicode UTF-8 character set, so the upper limit on the number of characters that may be stored in a single varchar column depends on whether you are storing single-byte or multibyte characters. When defining a column as varchar, you may optionally specify a maximum number of characters, as in:

favorite_movie varchar(100)

For the favorite_movie column, Snowflake would store up to 100 bytes of data depending on the length of the string. If you do not specify a maximum length, the column will hold up to 16 MB.

In order to be compatible with other database systems, Snowflake allows character  columns to be defined using any of the following:

• char, character, char varying
• nchar, nchar varying
• nvarchar, nvarchar2
• string, text

While you are free to use any of these synonyms, they are generally used for porting table definitions from other database servers. If you’re building new tables in Snowflake, you should just ...