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.

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 onsegment_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.

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.

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:

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:

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:

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:

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:

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

Standards

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

Creating Indexes Efficiently
Indexes speed data retrieval, but can slow data updates. For better performance, create a table on one segment and create its nonclustered indexes on another segment, when the segments are on separate physical devices.

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

Creating Indexes on Encrypted Columns
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 cannot be used to match case-insensitive data, or to perform range searches of any data.

Space Requirements for Indexes
Space is allocated to tables and indexes in increments of one extent, or eight pages, at a time. Each time an extent is filled, another extent is allocated. Use sp_spaceused to display the amount of space allocated to and used by an index.

Duplicate Rows
Considerations when using ignore_dup_row and allow_dup_row options.

Using Unique Constraints in Place of Indexes
As an alternative to create index, you can implicitly create unique indexes by specifying a unique constraint with the create table or alter table statement. The unique constraint creates a clustered or nonclustered unique index on the columns of a table. These implicit indexes are named after the constraint, and they follow the same rules for indexes created with create index.

Using the sorted_data Option to Speed Sorts
The sorted_data option can reduce the time needed to create an index by skipping the sort step and by eliminating the need to copy the data rows to new pages in certain cases. The speed increase becomes significant on large tables, and increases to several times faster in tables larger than 1GB.

Specifying the Number of Histogram Steps
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.

Index Options and Locking Modes
Allpages-locked and data-only-locked tables are supported by certain index options. On data-only-locked tables, the ignore_dup_row and allow_dup_row options are enforced during create index, but are not enforced during insert and update operations.

Using the sorted_data Option on Data-Only-Locked Tables
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.

create index and Stored Procedures
The SAP ASE server automatically recompiles stored procedures after executing create index statements. Although ad hoc queries that you start before executing create index continue to work, they do not take advantage of the new index.In SAP ASE versions 12.5 and earlier, create index was ignored by cached stored procedures.