alter index

Renames indexes in base or global temporary tables and foreign key role names of indexes and foreign keys explicitly created by a user.

Syntax

alter index [[database.][owner].]table_name.index_name
set index_compression = {none | page} | modify_partition_clause       

modify_partition_clause::= 
modify partition partition_name [,partition_name]...       
set index_compression = {none | page | default}

Parameters

index_compression – changes the local index partition's compression state affects only index rows that are newly inserted or updated in the partition. Valid values are:
- none – the index page for the specified index is not compressed. Indexes that are specifically created with index_compression = page are compressed.
- page – when the page is full, existing index rows are compressed using the page prefix compression. When a row is added, a check is performed to determine whether the row is suitable for compression.

Examples

Example 1 – Sets the compression state to on for the index idx_char:
```
alter index order_line.idx_char 
    set index_compression = page
```

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for alter index differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the table owner, or a user with the `create any index` privilege.
Disabled	With granular permissions disabled, you must be the table owner or a user with sa_role. create index permission defaults to the table owner and is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	104
Audit option
Command or access audited	alter index
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – Name of the index Proxy information – original login name, if a set proxy is in effect