How can I add an autoincrement column to a table in Snowflake?

If you need to do auto numbers or auto incrementing in Snowflake then the most versatile way to do auto-incrementing [also named IDENTITY Keys] is to CREATE A SEQUENCE. 

This SEQUENCE or really SEQUENCE object is separate from the table itself so it is great for if you need a unique value ACROSS multiple tables. Here is how you do that with a SEQUENCE named EXAMPLE_SEQUENCE_FOR_YOU with a START VALUE of 1 and an INCREMENT VALUE of 1.

CREATE SEQUENCE EXAMPLE_SEQUENCE_FOR_YOU START 1 INCREMENT 1;

Now you can either alter or create a table like this to utilize this SEQUENCE to achieve your goals.

ALTER TABLE ALTER COLUMN YOUR_AUTO_ID SET DEFAULT EXAMPLE_SEQUENCE_FOR_YOU.nextvalue;

or

CREATE OR REPLACE TABLE EXAMPLE_TABLE(YOUR_AUTO_ID SET DEFAULT EXAMPLE_SEQUENCE_FOR_YOU.nextvalue;YOUR_NAME VARCHAR);

ANOTHER WAY to achieve this auto-numbering or auto-incrementing functionality is to use the IDENTITY(START_VALUE,INCREMENT_VALUE) or AUTOINCREMENT(START_VALUE,INCREMENT_VALUE) syntax on a column [this means its part of the table itself!!].

1. CREATE the TABLE with the IDENTITY or AUTOINCREMENT VALUE LIKE THIS:
CREATE OR REPLACE TABLE EXAMPLE_TABLE(YOUR_ID AUTOINCREMENT START 1 INCREMENT 1, YOUR_NAME VARCHAR);
or
CREATE OR REPLACE TABLE EXAMPLE_TABLE(YOUR_ID IDENTITY START 1 INCREMENT 1, YOUR_NAME VARCHAR);
2. ALTER a TABLE and add the IDENTIFY VALUE COLUMN [REMEMBER – you can only do this if there is NO DATA within the TABLE!]

ALTER TABLE EXAMPLE_TABLE ADD COLUMN YOUR_ID INT AUTOINCREMENT(1,1);
or
ALTER TABLE EXAMPLE_TABLE ADD COLUMN YOUR_ID INT IDENTITY(1,1);

Note: The AUTOINCREMENT AND IDENTITY actually use sequences behind the scenes.