Snowflake SQL Tutorial 114 - How Do I Create An Index In Snowflake - The Answer Has Three Parts

Snowflake SQL · Episode 114

In this post we are going to cover the question people ask about Snowflake more than any other. How do I create an index?

The answer has three parts. You cannot create one on an ordinary table. There is one kind of table that does take an index, and it may not be available on your account. And the other statement people bring with them from Postgres, SELECT ... FOR UPDATE, is refused as well.

Snowflake says all three of those out loud, so we will ask it each question and read the answer it gives back. Then we will look at the two things you do reach for instead, both of which worked here.

This is also the last post of Level 9. Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.

Episode 114 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with the statement everybody tries first
3. Will IF NOT EXISTS help?
4. Is there a SHOW command for indexes at all?
5. One thing the cleanup taught us about DROP INDEX
6. So let's make the kind of table that does take an index
7. And now everything planned after that cannot run
8. What the hybrid flag says about the table we do have
9. Part three: the other thing people bring from Postgres
10. Does being inside a transaction change it?
11. So what do you use instead? Let's cluster the table
12. Can we take the clustering key away again?
13. Now let's add search optimization
14. One last thing: declared is not the same as measured

Let's create some sample data

First, where the session is pointing. Everything below runs in this database, this schema and this role.

Query

SELECT CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_VERSION()   AS THE_VERSION;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	THE_ROLE	THE_VERSION
DEMO_DB	PUBLIC	ACCOUNTADMIN	10.35.101

Now a table to ask our questions about. This is an ordinary table, which is the kind almost everybody has.

Sample data

-- Sample data for this post.
-- An ordinary table, which is the kind almost everybody has.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS_T114 (
    ORDER_ID  INT,
    CUSTOMER  VARCHAR(40),
    REGION    VARCHAR(20),
    AMOUNT    NUMBER(10,2),
    PLACED    DATE
);

INSERT INTO ORDERS_T114 (ORDER_ID, CUSTOMER, REGION, AMOUNT, PLACED)
SELECT 1, 'Ana Ltd',  'north', 100.00, '2026-01-05'::DATE UNION ALL
SELECT 2, 'Ben GmbH', 'north', 250.00, '2026-01-18'::DATE UNION ALL
SELECT 3, 'Cleo SA',  'south',  75.50, '2026-02-02'::DATE UNION ALL
SELECT 4, 'Dan Oy',   'south', 310.00, '2026-02-14'::DATE UNION ALL
SELECT 5, 'Eve AB',   'east',   40.00, '2026-03-01'::DATE UNION ALL
SELECT 6, 'Finn AS',  'east',   60.00, '2026-03-09'::DATE UNION ALL
SELECT 7, 'Gus Inc',  'west',  900.00, '2026-03-22'::DATE UNION ALL
SELECT 8, 'Hana KK',  'west',   15.00, '2026-04-04'::DATE;

Let's look at what we just created.

Query

SELECT ORDER_ID, CUSTOMER, REGION, AMOUNT, PLACED
FROM ORDERS_T114 ORDER BY ORDER_ID;

Result8 rows · 5 columns

ORDER_ID	CUSTOMER	REGION	AMOUNT	PLACED
1	Ana Ltd	north	100.00	2026-01-05
2	Ben GmbH	north	250.00	2026-01-18
3	Cleo SA	south	75.50	2026-02-02
4	Dan Oy	south	310.00	2026-02-14
5	Eve AB	east	40.00	2026-03-01
6	Finn AS	east	60.00	2026-03-09
7	Gus Inc	west	900.00	2026-03-22
8	Hana KK	west	15.00	2026-04-04

Eight orders and five columns. An order ID, a customer, a region, an amount and a date. The region is the column we would want an index on, because it is the one we would filter by.

Let's start with the statement everybody tries first

CREATE INDEX, on the region column. This is the statement you would write in any other database.

Query

CREATE INDEX IDX_REGION_T114 ON ORDERS_T114 (REGION);

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'Secondary index on non-hybrid tables is not supported'.

Refused. Now read that message carefully, because it is precise: Unsupported feature ‘Secondary index on non-hybrid tables is not supported’.

It does not say indexes do not exist. It does not say to use clustering instead. It says the feature exists, and that this kind of table cannot have one. Those are different answers, and the second one tells us where to look next.

Will IF NOT EXISTS help?

Maybe the objection is about the name clashing with something. IF NOT EXISTS is the usual way to deal with that, so let's try it. I doubted it would help, but doubting is not knowing.

Query

CREATE INDEX IF NOT EXISTS IDX_REGION2_T114 ON ORDERS_T114 (REGION);

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'Secondary index on non-hybrid tables is not supported'.

The identical refusal, word for word. So the objection is to the statement itself, not to a name that is already taken.

Is there a SHOW command for indexes at all?

If the feature exists, there should be somewhere to list them. Let's ask.

Query

SHOW INDEXES IN SCHEMA PUBLIC;

Result0 rows · 12 columns · scroll →

created_on	name	is_unique	table	columns	included_columns	database_name	schema_name	owner	status	status_info	owner_role_type

No rows, and twelve columns. The command works. There is simply nothing here for it to report.

The headings are the interesting part. is_unique, table, columns, included_columns. That is a real index catalogue, with the columns an index catalogue would need.

So the grammar is all here. There is just nothing on this account that can use it.

One thing the cleanup taught us about DROP INDEX

This next pair came from the end of the script, not from here. The script drops whatever it created, and it tries the two index drops whether the creates worked or not. Neither index was ever created, so these should have been quiet no-ops.

Cleanup, from the end of the script

DROP INDEX IF EXISTS IDX_REGION_T114;

Query failed — Snowflake returned an error

391435 (42601): SQL compilation error: The command requires a qualified index name 'IDX_REGION_T114'.

And the second one

DROP INDEX IF EXISTS IDX_REGION2_T114;

Query failed — Snowflake returned an error

391435 (42601): SQL compilation error: The command requires a qualified index name 'IDX_REGION2_T114'.

The command requires a qualified index name. Not “no such index”, which is what IF EXISTS would normally give us. DROP INDEX wants to know which table the index is on before it will even look.

That is a real syntax fact, and I would never have gone looking for it. The cleanup ran anyway and told me.

So let's make the kind of table that does take an index

Indexes belong to hybrid tables. A hybrid table is Snowflake's row-store table, and on one of those a primary key is enforced and a secondary index is allowed.

Let's write one. A customer ID as the primary key, a name that cannot be null, and an index on the region declared inline.

Query

CREATE OR REPLACE HYBRID TABLE CUSTOMERS_T114 (
    CUSTOMER_ID INT PRIMARY KEY,
    NAME        VARCHAR(40) NOT NULL,
    REGION      VARCHAR(20),
    INDEX IDX_REGION_H_T114 (REGION)
);

Query failed — Snowflake returned an error

391404 (0A000): Hybrid tables are currently not available to trial accounts.

Refused, and this is a completely different kind of refusal: Hybrid tables are currently not available to trial accounts.

It is not “unsupported feature” and it is not a syntax error. It is an answer about this account. The statement is fine. The account cannot have the object.

Let's confirm nothing was created.

Query

SELECT TABLE_NAME, TABLE_TYPE, IS_INSERTABLE_INTO
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T114'
ORDER BY TABLE_NAME;

Result1 row · 3 columns

TABLE_NAME	TABLE_TYPE	IS_INSERTABLE_INTO
ORDERS_T114	BASE TABLE	YES

One row, and it is the ordinary table we made at the start. The hybrid table is not in the catalog, because it does not exist.

And now everything planned after that cannot run

Here is what was meant to come next. Insert two customers with the same key, and watch the primary key reject the second one.

Query

INSERT INTO CUSTOMERS_T114 (CUSTOMER_ID, NAME, REGION)
SELECT 1, 'Ana', 'north'
UNION ALL SELECT 1, 'Duplicate Ana', 'south';

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Table 'CUSTOMERS_T114' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE CUSTOMERS_T114.

Of course not. There is no table.

And selecting from it

SELECT CUSTOMER_ID, NAME, REGION FROM CUSTOMERS_T114 ORDER BY CUSTOMER_ID;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'CUSTOMERS_T114' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE CUSTOMERS_T114.

The same. And the constraint lookup, which is the one I actually wanted to show you.

Query

SELECT CONSTRAINT_TYPE, TABLE_NAME, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'CUSTOMERS_T114'
ORDER BY CONSTRAINT_TYPE;

Result0 rows · 3 columns

CONSTRAINT_TYPE	TABLE_NAME	ENFORCED

No rows at all, because there is no table to have a constraint on.

I am leaving all three of those on the page rather than cutting them. A post that hides its own broken half is not worth reading.

What this would have proved

Episode 105 showed that a PRIMARY KEY on an ordinary Snowflake table is not enforced. The catalog there has a column called ENFORCED and it said NO. On a hybrid table it is supposed to be real. I cannot show you that on this account, and that is the honest answer rather than a worked example.

What the hybrid flag says about the table we do have

There is one thing we can show. SHOW OBJECTS reports a flag per special kind of table, and one of them is is_hybrid.

Query

SHOW OBJECTS IN SCHEMA PUBLIC;

Result12 rows · 16 columns · scroll →

Everything in the schema. Most of these tables belong to earlier posts in this series.

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	owner_role_type	is_hybrid	is_dynamic	is_iceberg	is_interactive
2026-09-29 18:34:09.486000-07:00	CUSTOMERS	DEMO_DB	PUBLIC	TABLE			10	1536	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:08.133000-07:00	DEPARTMENTS	DEMO_DB	PUBLIC	TABLE			4	1024	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:16.008000-07:00	DOCS_XML	DEMO_DB	PUBLIC	TABLE			4	1536	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:08.820000-07:00	EMPLOYEES	DEMO_DB	PUBLIC	TABLE			8	2048	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:12.446000-07:00	EVENTS_JSON	DEMO_DB	PUBLIC	TABLE			4	3072	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-27 03:50:28.146000-07:00	JSON_LOADED	DEMO_DB	PUBLIC	TABLE			11	2560	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:15.229000-07:00	JSON_TEXT_RAW	DEMO_DB	PUBLIC	TABLE			11	1536	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:10.357000-07:00	ORDERS	DEMO_DB	PUBLIC	TABLE			41	1536	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:18.693000-07:00	ORDERS_T114	DEMO_DB	PUBLIC	TABLE			8	1536	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:11.748000-07:00	SALES_RAW	DEMO_DB	PUBLIC	TABLE			8	1536	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-29 18:34:16.629000-07:00	TYPED_SHAPES	DEMO_DB	PUBLIC	TABLE			2	2560	ACCOUNTADMIN	1	ROLE	N	N	N	N
2026-09-27 20:10:07.808000-07:00	XML_LOADED	DEMO_DB	PUBLIC	TABLE			4	4096	ACCOUNTADMIN	1	ROLE	N	N	N	N

Twelve rows and sixteen columns. Let's pull out the row for our table and the flags we care about, by reading that output again with RESULT_SCAN.

Query

SELECT "name", "kind", "is_hybrid", "is_dynamic", "is_iceberg"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T114'
ORDER BY "name";

Result1 row · 5 columns

name	kind	is_hybrid	is_dynamic	is_iceberg
ORDERS_T114	TABLE	N	N	N

is_hybrid says N. Not hybrid. Which is exactly why CREATE INDEX was refused at the top of this post. The refusal and this flag are the same fact, reported twice.

Have a look at the comment and cluster_by cells in that wide grid too. They are marked empty rather than NULL, because a SHOW command reports nothing-set-here as an empty string. That becomes important further down.

Part three: the other thing people bring from Postgres

SELECT ... FOR UPDATE locks the rows you selected so that nobody else can change them until you are finished. If you have come from Postgres or Oracle you have written it many times.

Query

SELECT ORDER_ID, AMOUNT FROM ORDERS_T114 WHERE ORDER_ID = 1 FOR UPDATE;

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'FOR UPDATE clause'.

Unsupported feature ‘FOR UPDATE clause’. Short and clear.

Does being inside a transaction change it?

It should, if anything is going to, because nobody writes FOR UPDATE on its own. You write it inside an explicit transaction. Let's open one.

Query

BEGIN;

Result1 row · 1 column

status
Statement executed successfully.

Now the identical select, inside the transaction.

Query

SELECT ORDER_ID, AMOUNT FROM ORDERS_T114 WHERE ORDER_ID = 1 FOR UPDATE;

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'FOR UPDATE clause'.

The same refusal. Being in a transaction changes nothing about it.

Let's roll back and check the table is as we left it.

Query

ROLLBACK;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT COUNT(*) AS ROWS_PRESENT, SUM(AMOUNT) AS TOTAL FROM ORDERS_T114;

Result1 row · 2 columns

ROWS_PRESENT	TOTAL
8	1750.50

Eight rows and the same total as before. Nothing moved.

Refused, not ignored

A refused clause is better news than a quiet one. A FOR UPDATE that was accepted and then ignored is how you ship a race condition and find out about it in production. A FOR UPDATE that is refused is how you find out while you are still writing the query.

So what do you use instead? Let's cluster the table

Two things, and both of them worked here. The first is a clustering key, which tells Snowflake how to organise the table's own storage.

Query

ALTER TABLE ORDERS_T114 CLUSTER BY (REGION);

Result1 row · 1 column

status
Statement executed successfully.

Accepted. Now let's find where that was written down. SHOW TABLES first.

Query

SHOW TABLES LIKE 'ORDERS_T114' IN SCHEMA PUBLIC;

Result1 row · 27 columns · scroll →

Scroll the grid sideways for the whole row.

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	automatic_clustering	change_tracking	search_optimization	search_optimization_progress	search_optimization_bytes	is_external	enable_schema_evolution	owner_role_type	is_event	is_hybrid	is_iceberg	is_dynamic	is_immutable	is_interactive	row_timestamp	error_logging
2026-09-29 18:34:18.693000-07:00	ORDERS_T114	DEMO_DB	PUBLIC	TABLE		LINEAR(REGION)	8	1536	ACCOUNTADMIN	1	ON	OFF	OFF	NULL	NULL	N	N	ROLE	N	N	N	N	N	N	OFF	OFF

One row and twenty seven columns. The cluster_by column reads LINEAR(REGION), and automatic_clustering reads ON.

That grid is wide, so let's read the same output again and keep only the four columns we want.

Query

SELECT "name", "cluster_by", "rows", "automatic_clustering"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 4 columns

name	cluster_by	rows	automatic_clustering
ORDERS_T114	LINEAR(REGION)	8	ON

Same four values, easier to read. Be clear about what that second query is, though. RESULT_SCAN re-reads the output of the SHOW TABLES above it. It is the same command, read twice, and not a second place where the clustering key is recorded.

The catalog view is a genuinely different source, so let's ask that.

Query

SELECT TABLE_NAME, CLUSTERING_KEY, ROW_COUNT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'ORDERS_T114';

Result1 row · 3 columns

TABLE_NAME	CLUSTERING_KEY	ROW_COUNT
ORDERS_T114	LINEAR(REGION)	8

CLUSTERING_KEY says the same thing, under a different column name in a different view.

And the third place is the table's own definition.

Query

SELECT GET_DDL('TABLE', 'ORDERS_T114') AS THE_DDL;

Result1 row · 1 column

THE_DDL
create or replace TABLE ORDERS_T114 cluster by (REGION)( ORDER_ID NUMBER(38,0), CUSTOMER VARCHAR(40), REGION VARCHAR(20), AMOUNT NUMBER(10,2), PLACED DATE );

GET_DDL writes the clustering key straight into the CREATE statement, so if you script this table out and run it somewhere else, the key goes with it.

Three places, not four

The video says this is recorded in four different places. Counting the grids above, it is three: SHOW TABLES, INFORMATION_SCHEMA.TABLES and GET_DDL. The fourth grid is a RESULT_SCAN of the first one, which is the same command read a second time. SHOW OBJECTS, which the video also names, ran earlier in the script and its cluster_by column was empty, so it never reported this at all.

Can we take the clustering key away again?

Yes, and it is worth doing, because this is a probe and not a configuration.

Query

ALTER TABLE ORDERS_T114 DROP CLUSTERING KEY;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT TABLE_NAME, CLUSTERING_KEY
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'ORDERS_T114';

Result1 row · 2 columns

TABLE_NAME	CLUSTERING_KEY
ORDERS_T114	NULL

CLUSTERING_KEY is a real NULL now. The table is back to the way it was declared.

Now let's add search optimization

The second thing you reach for is the search optimization service, which is a different feature with its own switch.

Query

ALTER TABLE ORDERS_T114 ADD SEARCH OPTIMIZATION;

Result1 row · 1 column

status
Statement executed successfully.

Accepted too. Let's see how SHOW TABLES reports it.

Query

SHOW TABLES LIKE 'ORDERS_T114' IN SCHEMA PUBLIC;

Result1 row · 27 columns · scroll →

Scroll the grid sideways for the whole row.

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	automatic_clustering	change_tracking	search_optimization	search_optimization_progress	search_optimization_bytes	is_external	enable_schema_evolution	owner_role_type	is_event	is_hybrid	is_iceberg	is_dynamic	is_immutable	is_interactive	row_timestamp	error_logging
2026-09-29 18:34:18.693000-07:00	ORDERS_T114	DEMO_DB	PUBLIC	TABLE			8	1536	ACCOUNTADMIN	1	OFF	OFF	ON	0	0	N	N	ROLE	N	N	N	N	N	N	OFF	OFF

Query

SELECT "name", "search_optimization", "search_optimization_progress"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

name	search_optimization	search_optimization_progress
ORDERS_T114	ON	0

search_optimization is ON, and there is a search_optimization_progress column that tells you how much of it has actually been built. The two features are reported separately, in their own columns.

Empty string, or NULL

Compare the cluster_by column in the two SHOW TABLES grids on this page. Before we dropped the clustering key it read LINEAR(REGION). Now it is an empty cell, marked empty. The catalog view reported the same absence as a real NULL in the section above. Same fact, two answers: a SHOW command gives you an empty string where INFORMATION_SCHEMA gives you a null. The grids are not disagreeing with each other.

Let's switch it off again.

Query

ALTER TABLE ORDERS_T114 DROP SEARCH OPTIMIZATION;

Result1 row · 1 column

status
Statement executed successfully.

One last thing: declared is not the same as measured

What this post does not claim

Nothing on this page is timed and nothing is compared. We have shown that a clustering key and search optimization are declared, and where the catalog records them. What either of them does to a query is a different post with different evidence, and on eight rows it would show nothing anyway.

Here is one row that sums up what this account supports.

Query

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'CUSTOMERS_T114')
         AS HYBRID_TABLE_EXISTS,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'ORDERS_T114')
         AS ORDINARY_TABLE_EXISTS,
       (SELECT COUNT(*) FROM ORDERS_T114) AS ROWS_IN_THE_ORDINARY_TABLE;

Result1 row · 3 columns

HYBRID_TABLE_EXISTS	ORDINARY_TABLE_EXISTS	ROWS_IN_THE_ORDINARY_TABLE
0	1	8

No hybrid table, one ordinary table, and the rows still in it.

Then everything the script made gets dropped.

Query

DROP TABLE IF EXISTS CUSTOMERS_T114;

Result1 row · 1 column

status
Drop statement executed successfully (CUSTOMERS_T114 already dropped).

Read that status line. DROP TABLE IF EXISTS reports success on a table that was never created, and says so in brackets.

Query

DROP TABLE IF EXISTS ORDERS_T114;

Result1 row · 1 column

status
ORDERS_T114 successfully dropped.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T114')  AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)        AS DEMO_DB_IS_READABLE;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	10

No probe tables left, and the rest of the database still reads fine.

Key points

• CREATE INDEX on an ordinary table is refused with Secondary index on non-hybrid tables is not supported. The feature exists; that kind of table cannot have one.
• IF NOT EXISTS makes no difference, because the objection is to the statement and not to a name clash.
• SHOW INDEXES works and returns a full index catalogue with no rows in it.
• DROP INDEX needs a qualified index name, even with IF EXISTS.
• Indexes belong to hybrid tables, and a hybrid table can be refused at the account level: Hybrid tables are currently not available to trial accounts.
• is_hybrid in SHOW OBJECTS is the same fact as that refusal, reported as a flag.
• SELECT ... FOR UPDATE is refused, and being inside an explicit transaction changes nothing.
• A clustering key is recorded in three places: SHOW TABLES, INFORMATION_SCHEMA.TABLES and GET_DDL.
• DROP CLUSTERING KEY puts the catalog column back to NULL, which SHOW TABLES reports as an empty string.
• ADD SEARCH OPTIMIZATION works, and has its own ON column and its own progress column.
• Declaring a feature and measuring a feature are two different jobs. This post only did the first one.

Next in this series

That is Level 9 finished. Eighteen episodes on DDL, from data types to this one. Level 10 is DML, which is changing the data rather than the shape of it. Episode 115 — INSERT, and the one behaviour in it that will put your data in the wrong columns without a single warning.

Every query, every result grid and every error on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The account is a trial account with no hybrid tables available, which is the premise of the post rather than a gap in it: the refusals are real captured errors and are shown as they came back. The _T114 table is created and dropped by the script, and the other objects in the grids belong to earlier posts in this series. Empty cells marked empty are empty strings and cells reading NULL are real nulls. Nothing on this page is timed and no performance claims are made anywhere in this series. Index behaviour follows the Snowflake CREATE INDEX documentation.