create index

Description

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, 
			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}]
	[on segment_name]
	[index_partition_clause]

To create index partitions:

index_partition_clause::=
	[local index [partition_name [on segment_name]
	[, partition_name [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

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_id columns 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

Creates a function-based index:

create index sum_sales on mytitles (price * total_sales)

Example 11

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)

Usage

Adaptive Server automatically recompiles stored procedures after executing create index statements. Although adh oc queries that you start before executing create index continue to work, they do not take advantage of the new index.In Adaptive Server versions 12.5 and earlier, create index was ignored by cached stored procedures.

• A table “follows” its clustered index. When you create a table, use the on segment_name extension to create clustered index, the table migrates to the segment where the index is created.

  If you create a table on a specific segment, then create a clustered index without specifying a segment, Adaptive Server moves the table to the default segment when it creates the clustered index there.

  Because text, unitext, and image data is stored in a separate page chain, creating a clustered index with on segment_name does not move text and image columns.

• To create a clustered index, Adaptive Server duplicates the existing data; the server deletes the original data when the index is complete. Before creating a clustered index, use sp_spaceused to make sure that the database has at least 120 percent of the size of the table available as free space.

• The clustered index is often created on the table’s primary key (the column or columns that uniquely identify the row). You can record the primary key in the database (for use by front-end programs and sp_depends) using sp_primarykey.

• To allow duplicate rows in a clustered index, specify allow_dup_row.

• Adaptive Server compresses all rows available for compression during the data copy operation if the table requires sorting.

• Adaptive Server does not perform a data copy if you create a clustered index using sorted_data, and does not compress any data rows while it builds the clustered index.

• Adaptive Server does not compress index key values: It compresses only the values in the data rows.

• You may select index key columns and create unique indexes even if the key columns are compressed. To perform nonclustered uniqueness checks, examine the uncompressed index keys in an index page.

• Adaptive Server uses the uncompressed index key and row formats to verify support for ignore_dup_key, ignore_dup_row, and allow_dup_row.

• Adaptive Server applies the fillfactor parameter only for row-level compression.

• When it applies the fillfactor parameter to the data pages of an allpages-locked clustered index, Adaptive Server considers the:

  • Final compressed row format

  • Space required for compression

  Adaptive Server may additionally compress the page space used with subsequent page compression operations, resulting in lower fillfactor values.

• Adaptive Server applies the respagegap parameter at the page level so it is not affected by compression.

• max_rows_per_page includes a page’s data rows only, and not the hidden page-dictionary, index, and character-encoding entries.

You can create an index on an encrypted column if you specify the encryption key without any initialization vector or random padding. Indexes on encrypted columns are useful for equality and nonequality matches, but are not useful for matching case-insensitive data, or for range searches of any data.

To improve performance on both equality and nonequality searches, and on joins, create indexes on encrypted columns.

create index reports an error if you create:

• A functional index using an expression that references an encrypted column

• An index on a column encrypted with initialization vector or random padding

Use the asc and desc keywords after index column names to specify the sorting order for the index keys. Creating indexes so that columns are in the same order specified in the order by clause of queries eliminates the sorting step during query processing. See Chapter 6, “Indexing for Performance,” in the Performance and Tuning Guide: Locking.

• Use the with statistics clause to specify the number of steps for a histogram for the leading column of an index. Histograms are used during query optimization to determine the number of rows that match search arguments for a column.

• To re-create an index without updating the values in sysstatistics for a column, use 0 for the number of steps. This avoids overwriting statistics that have been changed with optdiag.

• If you specify the histogram tuning factor parameter with a value, then create index uses anywhere between 20 and M*20 steps, depending on the number of frequency cells that have been isolated. The default is 20, but you can specify a different number with the using step values option.

• You can use the sorted_data option to create index only immediately following a bulk-copy operation into an empty table. Once data modifications to that table cause additional page allocations, you cannot use the sorted_data option.

• Specifying different values for space management properties may override the sort suppression functionality of the sorted_data.

• You can use materialized computed columns as index keys, as though they were regular columns.

• To convert a virtual column to a materialized column and index it, use alter table modify with the materialized option before executing create index.

• A computed column need not be deterministic to be used as an index key; however, you must be careful about the possible impact of a nondeterministic column on the queries that reference it.

• A local index inherits the partition strategies, partition columns, and partition bounds (for range and list partitions) of the base table.

• Adaptive Server maintains local indexes, rebuilding the local index if the base table is repartitioned with a different partition key.

• Adaptive Server supports:

  Index type	Table type
  Local clustered and nonclustered partitioned indexes	Partitioned tables
  Global, clustered, unpartitioned indexes	Round-robin-partitioned tables
  Global, nonclustered, unpartitioned indexes	All partitioned tables

• For range-, hash-, and list-partitioned tables, clustered indexes are always local. Adaptive Server creates a local clustered index whether or not “local index” is included in the syntax.

• You can create indexes directly on expressions.

• The expression must be deterministic.

• Since Adaptive Server does not verify the deterministic property of the expression index key, the user must manually maintain the property. A change in this property can cause unexpected results.

• As a function-based index key must be deterministic, its result is preevaluated, and reused without reevaluation. Adaptive Server assumes all function-based index keys to be deterministic and uses their preevaluated values when they are referenced in a query; they are reevaluated only when the values of their base columns are changed.

• An index can have multiple function-based index keys or a combination of function-based index keys and regular columns.

• Expressions used as index keys must be deterministic. An expression key is different from a computed column index key, which needs to be evaluated only once, and does not require the deterministic property. An expression, however, must be reevaluated upon each occurrence of the expression in a specified query, and must always return the same result.

• If a user-defined function that is referenced by a function-based index is dropped or becomes invalid, any operations that call that function fail.

• Adaptive Server does not support clustered function-based indexes.

• You cannot create a function-based index with the sorted_data option.

• Once you create an index key on an expression, subsequent queries recognize the expression as an index key only if the expression is exactly the same as the expression used to create the index key.

• All insert, delete, and update operations on base columns cause Adaptive Server to automatically update the value of function-based index keys.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create index permission defaults to the table owner and is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also