create index

Creates an index on one or more computed or noncomputed columns in a table. Creates partitioned indexes. Allows computed columns, like ordinary columns, to be index keys, and creates function-based indexes. A function-based index has one or more expressions as its index key.

The existing create index syntax can create indexes on computed columns, but function-based indexes require additional syntax.

Syntax

create [unique] [clustered | nonclustered] index index_name 
	on [[database.][owner].]table_name 
		 (column_expression [asc | desc]
			[, column_expression [asc | desc]]...) 
	[with {fillfactor = pct, 
			online,
            max_rows_per_page = num_rows,
			reservepagegap = num_pages, 
			consumers = x, ignore_dup_key, sorted_data,
            [ignore_dup_row | allow_dup_row], 
 		   [statistics using num_steps values ] 
            [, statistics hash_option]                        
            [, statistics max_resource_granularity = int]             
            [, statistics histogram_tuning_factor = int]
            [, statistics print_progress = int] } ]
    [on segment_name]
	[index_partition_clause]
    [with index_compression = { NONE | PAGE }]...]]]

To create index partitions:

index_partition_clause::=
	[local index [partition_name 
	[with index_compression = { NONE | PAGE }][on segment_name]
    [, partition_name [on segment_name]
    [with index_compression = { NONE | PAGE }][on segment_name]...]]

For function-based indexes:

create [unique | nonclustered] index index_name
	on [[database.] [owner].] table_name
	 (column_expression [asc | desc] 
	[, column_expression [asc | desc]]...

Parameters

unique – prohibits duplicate index values (also called “key values”). The system checks for duplicate key values when the index is created (if data already exists), and each time data is added with an insert or update. If there is a duplicate key value or if more than one row contains a null value, the command fails, and the SAP ASE server prints an error message giving the duplicate entry.
Warning! The SAP ASE server does not detect duplicate rows if a table contains any non-null text, unitext, or image columns.

insert and update commands, which generate duplicate key values, can succeed if you create your index using the allow_dup_row option.

Composite indexes (indexes in which the key value is composed of more than one column) can also be unique.

The default is nonunique. To create a nonunique clustered index on a table that contains duplicate rows, specify allow_dup_row or ignore_dup_row.

When you create a unique local index on range-, list-, and hash-partitioned tables, the index key list is a superset of the partition-key list.
clustered – means that the physical order of rows on the current database device is the same as the indexed order of the rows. The bottom, or leaf level, of the clustered index contains the actual data pages. A clustered index almost always retrieves data faster than a nonclustered index. Only one clustered index per table is permitted.
If clustered is not specified, nonclustered is assumed.
nonclustered – means that the physical order of the rows is not the same as their indexed order. The leaf level of a nonclustered index contains pointers to rows on data pages. You can have as many as 249 nonclustered indexes per table.
index_name – is the name of the index. Index names must be unique within a table, but need not be unique within a database.
table_name – is the name of the table in which the indexed column or columns are located. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
column_expression – is a valid Transact-SQL expression that references at least one base column, and does not contain columns from other tables, local and global variables, aggregate functions, or subqueries.
Note: column_expressions replaces the column_name variable used in SAP ASE versions earlier than 15.0.
asc | desc – specifies whether the index is to be created in ascending or descending order for the column specified. The default is ascending order.
fillfactor – specifies how full the SAP ASE server makes each page when it creates a new index on existing data. The fillfactor percentage is relevant only when the index is created. As data changes, the pages are not maintained at any particular level of fullness.
The value you specify is not saved in sysindexes. Use sp_chgattribute to create stored fillfactor values.

The default for fillfactor is 0; this is used when you do not include with fillfactor in the create index statement (unless the value has been changed with sp_configure). When specifying a fillfactor, use a value between 1 and 100.

A fillfactor of 0 creates clustered indexes with completely full pages and nonclustered indexes with completely full leaf pages. It leaves a comfortable amount of space within the index B-tree in both the clustered and nonclustered indexes. There is seldom a reason to change the fillfactor.

If the fillfactor is set to 100, the SAP ASE server creates both clustered and nonclustered indexes with each page 100 percent full. A fillfactor of 100 makes sense only for read-only, to which no data is ever added.

fillfactor values smaller than 100 (except 0, which is a special case) cause the SAP ASE server to create new indexes with pages that are not completely full. A fillfactor of 10 might be a reasonable choice if you are creating an index on a table that eventually holds a great deal more data, but small fillfactor values cause each index (or index and data) to occupy more storage space.

Warning! Creating a clustered index with a fillfactor affects the amount of storage space your data occupies, since the SAP ASE server redistributes the data as it creates the clustered index.
max_rows_per_page – limits the number of rows on data pages and the leaf-level pages of indexes. Unlike fillfactor, the max_rows_per_page value is maintained until it is changed with sp_chgattribute.
If you do not specify a value for max_rows_per_page, the SAP ASE server uses a value of 0 when creating the table. Values for tables and clustered indexes range from 0 to 183K on a 2K page, to 0 to 1486 on a 16K page.

The maximum number of rows per page for nonclustered indexes depends on the size of the index key. The SAP ASE server returns an error message if the specified value is too high.

A max_rows_per_page value of 0 creates clustered indexes with full pages and nonclustered indexes with full leaf pages. It leaves a comfortable amount of space within the index B-tree in both clustered and nonclustered indexes.

If max_rows_per_page is set to 1, the SAP ASE server creates both clustered and nonclustered indexes with one row per page at the leaf level. Use low values to reduce lock contention on frequently accessed data. However, low max_rows_per_page values cause the SAP ASE server to create new indexes with pages that are not completely full, uses storage space, and may cause more page splits.

If CIS is enabled, you cannot use max_rows_per_page for remote servers.

Warning! Creating a clustered index with max_rows_per_page can affect the amount of storage space your data occupies, since the SAP ASE server redistributes the data as it creates the clustered index.
with reservepagegap = num_pages – specifies a ratio of filled pages to empty pages to be left during extent I/O allocation operations. For each specified num_pages, an empty page is left for future expansion of the index. Valid values are 0 – 255. The default is 0.
with consumers – specifies the number of consumer processes that should perform the sort operation for creating the index. The actual number of consumer processes used to sort the index may be different from the specified number, depending on the number of worker processes available and the number of data partitions.
ignore_dup_key – cancels attempts of duplicate key entry into a table that has a unique index (clustered or nonclustered). The SAP ASE server cancels the attempted insert or update of a duplicate key with an informational message. After the cancellation, the transaction containing the duplicate key proceeds to completion.
You cannot create a unique index on a column that includes duplicate values or more than one null value, whether or not ignore_dup_key is set. If you attempt to do so, the SAP ASE server prints an error message that displays the first of the duplicate values. You must eliminate duplicates before the SAP ASE server can create a unique index on the column.
ignore_dup_row – allows you to create a new, nonunique clustered index on a table that includes duplicate rows. ignore_dup_row deletes the duplicate rows from the table, and cancels any insert or update that would create a duplicate row, but does not roll back the entire transaction.
allow_dup_row – allows you to create a nonunique clustered index on a table that includes duplicate rows, and allows you to duplicate rows with insert and update statements.
sorted_data – speeds creation of clustered indexes or unique nonclustered indexes when the data in the table is already in sorted order (for example, when you have used bcp to copy data that has already been sorted into an empty table).
with statistics using num_steps values – specifies the number of steps to generate for the histogram used to optimize queries. If you omit this clause:
- The default value is 20, if no histogram is currently stored for the leading index column.
- The current number of steps is used, if a histogram for the leading column of the index column already exists.
If you specify 0 for num_steps, the index is re-created, but the statistics for the index are not overwritten in the system tables.

The actual number of steps may differ from the one you specify; if the histogram steps specified with num_steps is M, and the histogram_tuning_factor parameter is N, then the actual steps are between M and M*N, depending on the number of frequency cells that exist in the distribution.
online – creates indexes without blocking access to the data.
on segment_name – creates the index on the named segment. Before using the on segment_name option, initialize the device with disk init, and add the segment to the database using sp_addsegment. See your system administrator, or use sp_helpsegment to generate a list of the segment names available in your database. There are two locations where you can use on segment_name:
- Immediately before the index_partition_clause – defines a global default which is used for all partitions where the segment is not explicitly defined in the index_partition_clause.
- Within that clause itself – allows you to specify a segment for each individual partition
See the examples section for an example that uses on segment_name in both locations.
hash_option – indicates whether to gather index statistics on tables:
- hashing – statistics are gathered with hashing.
- new_ hashing – enables hash-based gathering for minor attributed columns that have not had statistics previously gathered.
- no_ hashing – no hash-based statistics are gathered.
with statistics hashing –
- max_resource_granularity – sets the maximum percentage of system resources a query can use.
- histogram_tuning_factor – controls the number of steps that SAP ASE analyzes per histogram.
- print_progress – shows progress messages during gathering of statictics with hashing.
local index – specifies, for semantically partitioned tables, an index that is always equipartitioned with its base table; that is, the table and index share the same partitioning key and partitioning criteria. For round-robin-partitioned tables, a local index means that index keys in each of the tables’ index partitions refer to data rows in one and only one table partition.
For both semantically partitioned tables and round-robin-partitioned tables, each table partition has only one corresponding index partition.
partition_name – specifies the name of a new partition on which indexes are to be stored. Partition names must be unique within the set of partitions on a table or index. Partition names can be delimited identifiers if set quoted_identifier is on. Otherwise, they must be valid identifiers.
If partition_name is omitted, the SAP ASE server creates a name in the form table_name_partition_id. The SAP ASE server truncates partition names that exceed the allowed maximum length.
index_compression = { NONE | PAGE } – specifies whether to compress an index or index partition. 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 determines whether the row suitable for compression.

Examples

Example 1 – Creates an index named au_id_ind on the au_id column of the authors table:
```
create index au_id_ind on authors (au_id)
```
Example 2 – Creates a unique clustered index named au_id_ind on the au_id column of the authors table:
```
create unique clustered index au_id_ind 
on authors (au_id)
```
Example 3 – Creates an index named ind1 on the au_id and title_idcolumns of the titleauthor table:
```
create index ind1 on titleauthor (au_id, title_id)
```
Example 4 – Creates a nonclustered index named zip_ind on the zip column of the authors table, filling each index page one-quarter full and limiting the sort to 4 consumer processes:
```
create nonclustered index zip_ind 
on authors (postalcode) 
with fillfactor = 25, consumers = 4
```
Example 5 – Creates an index with ascending ordering on pub_id and descending order on pubdate:
```
create index pub_dates_ix 
on titles (pub_id asc, pubdate desc)
```
Example 6 – Creates an index on title_id, using 50 histogram steps for optimizer statistics and leaving 1 empty page out of every 40 pages in the index:
```
create index title_id_ix
on titles (title_id)
with reservepagegap = 40, 
statistics using 50 values
```
Example 7 – Creates a local, clustered index on a partitioned salesdetail table. The clust_idx index inherits the partition strategy, partition key, and partition bounds of salesdetail.
```
create clustered index clust_idx
on salesdetail (ord_num) local index
```
Example 8 – Creates a nonpartitioned, nonclustered global index on a partitioned sales table, which is partitioned by range on the date column.
```
create nonclustered index global_idx
on sales (order_num)
```

Example 9 – First, creates a table, pback_sales, with three data partitions:

create table pback_sales (c1 int, c2 int, 
    c3 varchar (20)) partition range (c1) 
        (p1 c1 values <= (10), 
        p2 c1 values <= (20), 
        p3 c1 values <= (MAX))

Then, creates a local, function-based index on partition p1:

create index fc_idx on pback_sales (c1*c2) local index
    p1

Example 10 – create index with sorted_data selects a parallel query plan when an explicit consumers = clause is included. This example uses a parallel query plan for the first query, but uses a serial query plan for the second:
```
create index i1 on t1(c1) with sorted_data, consumers = N
create index i1 on t1(c1) with sorted_data
```
Example 11 – Creates a function-based index:
```
create index sum_sales on mytitles (price * total_sales)
```

Example 12 – Specifies the on segment_name clause both before and after the partition name:

use tempdb
go
if not exists(select 1 from tempdb..syssegments where name = 'seg1')
    exec sp_addsegment seg1,tempdb,master
go
if not exists(select 1 from tempdb..syssegments where name = 'seg2')
    exec sp_addsegment seg2,tempdb,master
go
if not exists(select 1 from tempdb..syssegments where name = 'seg3')
    exec sp_addsegment seg3,tempdb,master
go
if not exists(select 1 from tempdb..syssegments where name = 'seg4')
    exec sp_addsegment seg4,tempdb,master
go
if exists(select 1 from sysobjects where name = 't1')
    drop table t1
go
create table t1 (a int, b varchar(30)) partition by roundrobin (p1 on seg1, p2 on seg2)
go
create index t1_i1 on t1 (a) local index
go
create index t1_i2 on t1 (a) on seg3 local index ip1 on seg4
go
sp_help t1
go

Provides the following output:

 Name Owner Object_type Create_date
---- ----- ----------- ------------------- 
t1   dbo   user table  Aug 7 2008 11:14AM 

(1 row affected)
Column_name Type    Length Prec Scale Nulls Default_name Rule_name
    Access_Rule_name Computed_Column_object Identity
----------- ------- ------ ---- ----- ----- ------------ ---------
    ---------------- ---------------------- ---------- 
a          int         4 NULL  NULL    0 NULL        NULL
     NULL           NULL                         0 
b          varchar    30 NULL  NULL    0 NULL        NULL
     NULL           NULL                         0 
Object has the following indexes

index_name index_keys index_description index_max_rows_per_page
    index_fillfactor index_reservepagegap index_created       index_local 
---------- ---------- ----------------- -----------------------
    ---------------- -------------------- ------------------- ----------- 
t1_i1       a       nonclustered                          0
                  0                  0 Aug  7 2008 11:14AM Local Index 
t1_i2       a       nonclustered                         0
                  0                  0 Aug  7 2008 11:14AM Local Index 

(2 rows affected)
index_ptn_name   index_ptn_seg 
---------------- ------------- 
t1_i1_952063116  default 
t1_i1_968063173  default 
ip1             seg4 
t1_i2_1000063287 seg3 

(4 rows affected)
No defined keys for this object.
name type      partition_type partitions partition_keys 
---- ---------- -------------- ---------- -------------- 
t1   base table roundrobin            2 NULL 

(1 row affected)

partition_name partition_id pages row_count segment create_date
-------------- ------------ ----- --------- ------- ------------------- 
p1              920063002    1        0 seg1    Aug 7 2008 11:14AM 
p2              936063059    1        0 seg2    Aug 7 2008 11:14AM 

Partition_Conditions 
-------------------- 
NULL 

Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)   Ratio(Min/Avg)
----------- ----------- ----------- --------------    ---------------- 
1          1          1                1.000000   1.000000 
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with
allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts 
------------ -------------- ---------- ----------------- ------------ ----------- 
0           0             0        0                0           0 

(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg 
------------------------- --------------------- ------------------- 
0                       0                  0 

(1 row affected)
(return status = 0)

Example 13 – Creates a compressed index called idx_order_line on columns ol_delivery_d and ol_dist_info:
```
create index idx_order_line 
    on order_line (ol_delivery_d, ol_dist_info)  
with index_compression = page
```
If the index has an index row length that is too short to benefit from compression, a warning is raised indicating the index will not be compressed.
Example 14 – Creates a compressed index called idx_Sales. The index contains local index partitions that can be compressed. Index prefix compression is applied to the local index partition. Page prefix compression is applied while the index page is full:
```
create index idx_sales
    on Sales(store_id, order_num) 
    local index ip1 with index_compression = PAGE, 
    ip2 with index_compression = PAGE,
ip3
```
Example 15 – Creates a unique clustered index named au_id_ind on the au_id and title_id columns of the authors table. Statistics are gathered with hashing, counts 50 steps, and enables print_progress which shows progress messages:
```
create unique clustered index au_id_ind
    on authors(au_id, title_id)
    with statistics hashing,
        statistics using 50 values,
        statistics print_progress = 1 
```
Example 16 – Creates an index named ind1 on the au_id and title_id columns of the titleauthor table. Statistics are gathered with hashing, counts 50 steps, and sets the percentage of system resources a query can use at 80 percent using the max_resource_granularity option:
```
create index ind1
    on titleauthor (au_id, title_id)
    with statistics using 50 values,
        statistics hashing,
        statistics max_resource_granularity = 80  
```
Example 17 – Creates a nonclustered index named zip_ind on the postalcode and au_id columns of the authors table. Each index page is filled one-quarter full and the sort is limited to 4 consumer processes. Statistics are gathered with hashing, counts 50 steps, and generates an intermediate 40-step histogram:
```
create nonclustered index zip_ind
    on authors(postalcode, au_id)
    with fillfactor = 25,
        consumers = 4,
        statistics using 50 values,
        statistics hashing,
        statistics histogram_tuning_factor = 40    
```
Example 18 – Creates a unique clustered index named au_id_ind on the au_id and title_id columns of the authors table. Statistics are gathered with hashing for minor attributed columns that have not had statistics previously gathered:
```
create unique clustered index au_id_ind
    on authors(au_id, title_id)
    with statistics new_hashing
           
```

Usage

Periodically run update statistics if you add data to the table that changes the distribution of keys in the index. The query optimizer uses the information created by update statistics to select the best plan for running queries on the table.
You can create non-clustered local index in parallel for partitioned tables that includes empty partitions.
If the table contains data when you create a nonclustered index, the SAP ASE server runs update statistics on the new index. If the table contains data when you create a clustered index, the SAP ASE server runs update statistics on all the table’s indexes.
Index all columns that are regularly used in joins.
When CIS is enabled, the create index command is reconstructed and passed directly to the SAP ASE associated with the table.
You cannot use create index (clustered or unclustered) on the segment that includes the virtually hashed table, since a virtually hashed table must take only one exclusive segment, which cannot be shared by other tables or databases
You can run writetext concurrently with the online parameter.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for create 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	create
Command or access audited	create 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