< MOVE TO | SET SCHEMA > ``` All dependent [views](/content/sql/ddl/create-view#sql-create-view), [materialized views](/content/sql/ddl/create-materialized-view#sql-create-materialized-view), [streams](/content/sql/ddl/create-stream#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target this *table* must be dropped before it can be moved. For example, to move the `sales_2017` table from the `example_olap` schema to the `example_archive` schema: ```sql Move Table Example theme={null} ALTER TABLE example_olap.sales_2017 MOVE TO example_archive ``` ## Set Access Mode A table can have its global accessibility modified for all users in the system, independently from and further restricting any role-based access controls in place. Note that changing the access mode cannot *widen* access for users not already granted access; it can only *narrow* access for those who already have access. This setting will also trump administrative access to a table. ```sql title="Set Table Access Mode Syntax" theme={null} ALTER TABLE [.]

SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE > ``` ## Set TTL A table's [time-to-live (TTL)](/content/concepts/ttl) can be altered. ```sql title="Set Table TTL Syntax" theme={null} ALTER TABLE [.]

SET TTL ``` For example, to set a *TTL* of *7* minutes on a table: ```sql Set Table TTL Example theme={null} ALTER TABLE example.employee SET TTL 7 ``` To set a table to never expire by *TTL* timeout: ```sql Set Table No Expiration Example theme={null} ALTER TABLE example.employee SET TTL -1 ``` ## Add Column A column can be added, specifying a [column definition](/content/sql/ddl#sql-ddl). ```sql title="Add Table Column Syntax" theme={null} ALTER TABLE [.]

ADD [DEFAULT ] ``` A new column can have its values initially populated through the use of the `DEFAULT` keyword. These values can either be a string/numeric constant or the name of an existing column in the table from which values can be copied into the new column. This default value is only in effect for the column creation; the new column will have no default value after that. **Examples** To add, to the `employee` table, a `salary` column that is a non-nullable, 10-digit number field containing 2 decimal places with a default value of `0`: ```sql Add Table Column (Numeric) Example theme={null} ALTER TABLE example.employee ADD salary NUMERIC(10, 2) NOT NULL DEFAULT 0 ``` To add, to the `employee` table, a `category` column that is a nullable, [dictionary-encoded](/content/concepts/dictionary_encoding), 32-character text field: ```sql Add Table Column (CharN/Dictionary-Encoded) Example theme={null} ALTER TABLE example.employee ADD category VARCHAR(32, DICT) ``` To add, to the `employee` table, a `bio` column that is a nullable, [text-searchable](/content/concepts/types#types-data-handling), unrestricted-width text field: ```sql Add Table Column (String/Text-Searchable) Example theme={null} ALTER TABLE example.employee ADD bio VARCHAR(TEXT_SEARCH) ``` ## Rename Column An existing column can be renamed. ```sql title="Rename Table Column Syntax" theme={null} ALTER TABLE [.]

RENAME COLUMN TO ``` All dependent [views](/content/sql/ddl/create-view#sql-create-view), [materialized views](/content/sql/ddl/create-materialized-view#sql-create-materialized-view), [streams](/content/sql/ddl/create-stream#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target the column being renamed must be dropped before it can be renamed. ```sql Rename Table Column Example theme={null} ALTER TABLE example.employee RENAME COLUMN bio TO biography ``` ## Modify Column A column can have its [column definition](/content/sql/ddl#sql-ddl) modified, affecting *column type*, *column size*, *column properties*, and nullability. ```sql title="Modify Table Column Syntax" theme={null} ALTER TABLE [.]

MODIFY [COLUMN] ``` ```sql title="Modify Table Column Alternate Syntax" theme={null} ALTER TABLE [.]

ALTER COLUMN ``` All dependent [views](/content/sql/ddl/create-view#sql-create-view), [materialized views](/content/sql/ddl/create-materialized-view#sql-create-materialized-view), [streams](/content/sql/ddl/create-stream#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target the column being modified must be dropped before it can be modified. If a column is modified to be non-nullable, it will be populated with default values--empty string for string fields and `0` for numeric fields. **Examples** To change, in the `employee` table, the `first_name` column to one that is a non-nullable, [dictionary-encoded](/content/concepts/dictionary_encoding), 50-character text field: ```sql Modify Table Column Example theme={null} ALTER TABLE example.employee ALTER COLUMN first_name VARCHAR(50, DICT) NOT NULL ``` ## Drop Column An existing column can be removed from a table. ```sql title="Drop Table Column Syntax" theme={null} ALTER TABLE [.]

DROP COLUMN ``` All dependent [views](/content/sql/ddl/create-view#sql-create-view), [materialized views](/content/sql/ddl/create-materialized-view#sql-create-materialized-view), [streams](/content/sql/ddl/create-stream#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target the column being dropped must be dropped before it can be dropped. ## Add Column Index A [column (attribute) index](/content/concepts/indexes#column-index) can be added to a table column in order to improve the performance of operations whose expressions contain relational operators against the column. See [Limitations](/content/concepts/indexes#column-index-limitations) for restrictions. ```sql title="Add Table Column Index Syntax" theme={null} ALTER TABLE [.]

ADD INDEX () ``` For example, to index the `employee` table's `last_name` column: ```sql Add Table Column Index Example theme={null} ALTER TABLE example.employee ADD INDEX (last_name) ``` ## Drop Column Index An existing [column (attribute) index](/content/concepts/indexes#column-index) can be removed from a table. ```sql title="Drop Table Column Index Syntax" theme={null} ALTER TABLE [.]

DROP INDEX () ``` For example, to drop the index on the `employee` table's `last_name` column: ```sql Drop Table Column Index Example theme={null} ALTER TABLE example.employee DROP INDEX (last_name) ``` ## Add Low-Cardinality Index A [low-cardinality index](/content/concepts/indexes#low-cardinality-index) can be added to a table column with few distinct values in order to improve the performance of operations whose expressions contain relational operators against the column. See [Limitations](/content/concepts/indexes#column-index-limitations) for restrictions. ```sql title="Add Table Low-Cardinality Index Syntax" theme={null} ALTER TABLE [.]

ADD LOW CARDINALITY INDEX () ``` For example, to index the `employee` table's `dept_id` column: ```sql Add Table Low-Cardinality Index Example theme={null} ALTER TABLE example.employee ADD LOW CARDINALITY INDEX (dept_id) ``` ## Drop Low-Cardinality Index An existing [low-cardinality index](/content/concepts/indexes#low-cardinality-index) can be removed from a table. ```sql title="Drop Table Low-Cardinality Index Syntax" theme={null} ALTER TABLE [.]

DROP LOW CARDINALITY INDEX () ``` For example, to drop the index on the `employee` table's `dept_id` column: ```sql Drop Table Low-Cardinality Index Example theme={null} ALTER TABLE example.employee DROP LOW CARDINALITY INDEX (dept_id) ``` ## Add Chunk Skip Index A [chunk skip index](/content/concepts/indexes#chunk-skip-index) can be added to a table column in order to improve the performance of operations containing equality-based filters against the column. See [Limitations](/content/concepts/indexes#chunk-skip-index-limitations) for restrictions. ```sql title="Add Table Chunk Skip Index Syntax" theme={null} ALTER TABLE [.]

ADD CHUNK [SKIP] INDEX () ``` For example, to index the `employee` table's employee ID column: ```sql Add Table Chunk Skip Index Example theme={null} ALTER TABLE example.employee ADD CHUNK SKIP INDEX (id) ``` ## Drop Chunk Skip Index An existing [chunk skip index](/content/concepts/indexes#chunk-skip-index) can be removed from a table. ```sql title="Drop Table Chunk Skip Index Syntax" theme={null} ALTER TABLE [.]

DROP CHUNK [SKIP] INDEX () ``` For example, to drop the chunk skip index on the `employee` table's employee ID column: ```sql Drop Table Chunk Skip Index Example theme={null} ALTER TABLE example.employee DROP CHUNK SKIP INDEX (id) ``` ## Add Geospatial Index A [geospatial index](/content/concepts/indexes#geospatial-index) can be added to one or more table columns to improve the performance of geospatial functions applied to them. ```sql title="Add Table Geospatial Index Syntax" theme={null} ALTER TABLE [.]

ADD GEOSPATIAL INDEX ([,...]) ``` For example, to index the `employee` table's work district WKT column: ```sql Add Table WKT Geospatial Index Example theme={null} ALTER TABLE example.employee ADD GEOSPATIAL INDEX (work_district) ``` To index the `employee` table's office location coordinate pair columns: ```sql Add Table Coordinate Pair Geospatial Index Example theme={null} ALTER TABLE example.employee ADD GEOSPATIAL INDEX (office_longitude, office_latitude) ``` ## Drop Geospatial Index An existing [geospatial index](/content/concepts/indexes#geospatial-index) can be removed from a table. ```sql title="Drop Table Geospatial Index Syntax" theme={null} ALTER TABLE [.]

DROP GEOSPATIAL INDEX ([,...]) ``` For example, to drop the geospatial index on the `employee` table's work district WKT column: ```sql Drop Table WKT Geospatial Index Example theme={null} ALTER TABLE example.employee DROP GEOSPATIAL INDEX (work_district) ``` To drop the geospatial index on the `employee` table's office location coordinate pair columns: ```sql Drop Table Coordinate Pair Geospatial Index Example theme={null} ALTER TABLE example.employee DROP GEOSPATIAL INDEX (office_longitude, office_latitude) ``` ## Add CAGRA Index A [CAGRA index](/content/concepts/indexes#cagra-index) can be added to a table column in order to improve the performance of [vector searches](/content/vector_search) applied to the column. ```sql title="Add Table CAGRA Index Syntax" theme={null} ALTER TABLE [.]

ADD CAGRA INDEX () ``` For example, to add a CAGRA index on the `employee` table's profile column: ```sql Add Table CAGRA Index Example theme={null} ALTER TABLE example.employee ADD CAGRA INDEX (profile) ``` ## Refresh CAGRA Index An existing [CAGRA index](/content/concepts/indexes#cagra-index) can be refreshed. ```sql title="Refresh Table CAGRA Index Syntax" theme={null} ALTER TABLE [.]

REFRESH CAGRA INDEX ON () ``` For example, to refresh the CAGRA index on the `employee` table's profile column: ```sql Refresh Table CAGRA Index Example theme={null} ALTER TABLE example.employee REFRESH CAGRA INDEX ON (profile) ``` ## Drop CAGRA Index An existing [CAGRA index](/content/concepts/indexes#cagra-index) can be removed from a table. ```sql title="Drop Table CAGRA Index Syntax" theme={null} ALTER TABLE [.]

DROP CAGRA INDEX () ``` For example, to drop the CAGRA index on the `employee` table's profile column: ```sql Drop Table CAGRA Index Example theme={null} ALTER TABLE example.employee DROP CAGRA INDEX (profile) ``` ## Add HNSW Index An [HNSW index](/content/concepts/indexes#hnsw-index) can be added to a table column in order to improve the performance of [vector searches](/content/vector_search) applied to the column. ```sql title="Add Table HNSW Index Syntax" theme={null} ALTER TABLE [.]

ADD HNSW INDEX () ``` For example, add an HNSW index on the `employee` table's profile column: ```sql Add Table HNSW Index Example theme={null} ALTER TABLE example.employee ADD HNSW INDEX (profile) ``` ## Drop HNSW Index An existing [HNSW index](/content/concepts/indexes#hnsw-index) can be removed from a table. ```sql title="Drop Table HNSW Index Syntax" theme={null} ALTER TABLE [.]

DROP HNSW INDEX () ``` For example, to drop the HNSW index on the `employee` table's profile column: ```sql Drop Table HNSW Index Example theme={null} ALTER TABLE example.employee DROP HNSW INDEX (profile) ``` ## Add Foreign Key A [foreign key](/content/concepts/tables#foreign-key) can be added to any column or set of columns with [primary key applicable types](/content/concepts/tables#primary-key-def) in order to improve the performance of [join](/content/sql/query/join#sql-join) operations between the table being altered and the table referenced in the *foreign key*. ```sql title="Add Table Foreign Key Syntax" theme={null} ALTER TABLE [.]

ADD FOREIGN KEY (,...) REFERENCES [.](,...) [AS ] ``` For example, to add a foreign key on the `employee` table's department ID column, linking it to the `department` table's department ID column: ```sql Add Table Foreign Key Example theme={null} ALTER TABLE example.employee ADD FOREIGN KEY (dept_id) REFERENCES example.department(id) AS fk_emp_dept ``` ## Drop Foreign Key An existing [foreign key](/content/concepts/tables#foreign-key) can be removed from a table, either by the name (alias) given to it during creation or by its definition: ```sql title="Drop Table Foreign Key by Name Syntax" theme={null} ALTER TABLE [.]

DROP FOREIGN KEY ``` ```sql title="Drop Table Foreign Key by Definition Syntax" theme={null} ALTER TABLE [.]

DROP FOREIGN KEY (,...) REFERENCES [.](,...) ``` For example, to drop the *foreign key* on the `employee` table's department ID column: ```sql Drop Table Foreign Key by Name Example theme={null} ALTER TABLE example.employee DROP FOREIGN KEY fk_emp_dept ``` ```sql Drop Table Foreign Key by Definition Example theme={null} ALTER TABLE example.employee DROP FOREIGN KEY (dept_id) REFERENCES example.department(id) ``` ## Add Partition A *partition* can be added to a [range-partitioned](/content/concepts/tables#partitioning-by-range) or [list-partitioned](/content/concepts/tables#partitioning-by-list) table. Defining (adding) partitions after data has been loaded will result in a performance penalty as the database moves existing records targeted for the new partition from the *default partition* into it. ### Range Partition The new *partition* can be given a minimum bound (inclusive) and a maximum bound (exclusive). If the new *partition* would come before an existing *partition*, omitting the maximum bound would cause the new *partition* to take on the nearest following existing *partition's* minimum bound as its maximum bound. If the new *partition* would come after an existing *partition*, omitting the minimum bound would cause the new *partition* to take on the nearest preceding *partition's* maximum bound as its minimum bound. If no *partitions* are present in the table, the new *partition* will have to declare both a minimum and maximum bound. ```sql title="Add Table Range Partition Syntax" theme={null} ALTER TABLE [.]

ADD PARTITION [ MIN ( ) ] [ MAX ( ) ] ``` For example, to add a *partition* to the `customer_order_range_by_year` table, containing all records with a *partition key* less than `2020` and greater than or equal to the maximum bound of the nearest preceding *partition*: ```sql Add Table Range Partition Example theme={null} ALTER TABLE example.customer_order_range_partition_by_year ADD PARTITION order_2020 MAX(2021) ``` ### List Partition The new *partition* can be given a list of values to match against the *partition key* values of incoming records. ```sql title="Add Table List Partition Syntax" theme={null} ALTER TABLE [.]

ADD PARTITION VALUES ( ) ``` For example, to add a *partition* to the `customer_order_manual_list_partition_by_year` table, containing all records from *2020*: ```sql Add Table List Partition (Year) Example theme={null} ALTER TABLE example.customer_order_manual_list_partition_by_year ADD PARTITION order_2020 VALUES (2020) ``` For example, to add a *partition* to the `customer_order_manual_list_partition_by_year_and_month` table, containing all records from *February 2020* & *April 2020*: ```sql Add Table List Partition (Month) Example theme={null} ALTER TABLE example.customer_order_manual_list_partition_by_year_and_month ADD PARTITION order_2020_0204 VALUES ((2020, 2), (2020, 4)) ``` ## Remove Partition An existing *partition* can be removed from a [range-partitioned](/content/concepts/tables#partitioning-by-range) or [list-partitioned](/content/concepts/tables#partitioning-by-list) table, sending all data contained within that *partition* back to the *default partition*. ```sql title="Remove Table Partition Syntax" theme={null} ALTER TABLE [.]

REMOVE PARTITION ``` For example, to remove a *partition* named `order_2017` from the `customer_order_range_by_year` table: ```sql Remove Table Partition Example theme={null} ALTER TABLE example.customer_order_range_partition_by_year REMOVE PARTITION order_2017 ``` ## Delete Partition An existing *partition* can be dropped from a [range-partitioned](/content/concepts/tables#partitioning-by-range) or [list-partitioned](/content/concepts/tables#partitioning-by-list) table, deleting all data contained within it. ```sql title="Delete Table Partition Syntax" theme={null} ALTER TABLE [.]

DELETE PARTITION ``` For example, to drop a *partition* named `order_2014_2016` from the `customer_order_range_by_year` table, deleting all data within that *partition*: ```sql Delete Table Partition Example theme={null} ALTER TABLE example.customer_order_range_partition_by_year DELETE PARTITION order_2014_2016 ``` ## Set Tier Strategy A table's [eviction priorities](/content/rm/concepts#rm-concepts-eviction-priority) can be adjusted by setting its [tier strategy](/content/rm/concepts#rm-concepts-tier-strategy). ```sql title="Set Table Tier Strategy Syntax" theme={null} ALTER TABLE [.]

SET TIER STRATEGY () ``` For example, to set the `customer_order` table's *tier strategy*, to one with a below-average *eviction priority* in the [RAM Tier](/content/rm/concepts#rm-concepts-tiers-ram): ```sql Set Table Tier Strategy Example theme={null} ALTER TABLE example.customer_order SET TIER STRATEGY ( ( ( VRAM 1, RAM 3, PERSIST 5 ) ) ) ``` The *tier strategy* can also be reset to the [system default strategy](/content/rm/configuration#rm-config-tier-strategy-default). ```sql title="Reset Table Tier Strategy Syntax" theme={null} ALTER TABLE [.]

RESET TIER STRATEGY ``` For example, to reset the `customer_order` table's *tier strategy*: ```sql Reset Table Tier Strategy Example theme={null} ALTER TABLE example.customer_order RESET TIER STRATEGY ``` ## Manage Subscription Any *table* that is subscribed to a streaming [external data source](/content/sql/ddl/create-data-source#sql-create-data-source) can have that subscription paused, resumed, canceled, or dropped. ```sql title="Manage External Table Subscription Syntax" theme={null} ALTER TABLE [.]

SUBSCRIPTION ``` Once unsubscribed, there is no way to re-subscribe the table to the *data source* via `ALTER TABLE`. An *external table* will need to be re-created, while a table whose subscription was initiated through the [LOAD INTO](/content/sql/load#sql-load-file-server) command can have that command re-executed. For example, to manage a subscription on the `ext_product` *external table* through the `product_ds` *data source*: ```sql Pause Subscription theme={null} ALTER TABLE example.ext_product PAUSE SUBSCRIPTION example.product_ds ``` ```sql Resume Subscription theme={null} ALTER TABLE example.ext_product RESUME SUBSCRIPTION example.product_ds ``` ```sql Cancel Subscription theme={null} ALTER TABLE example.ext_product CANCEL SUBSCRIPTION example.product_ds ``` ```sql Drop Subscription theme={null} ALTER TABLE example.ext_product DROP SUBSCRIPTION example.product_ds ```