SQL index creation

This article is transferred from:
Http://www.cnblogs.com/abcdwxc/archive/2007/12/11/990274.html
-----------------------

Creates an index for a given table or view.
Only the owner of a table or view can create an index for the table. The owner of a table or view can create an index at any time, regardless of whether the table has data. You can create an index for a table or view in another database by specifying a qualified database name.

Syntax
Create [unique] [clustered | nonclustered] index index_name
On {table | view} (column [ASC | DESC] [,... n])
[With <index_option> [,... n]
[On filegroup]

<Index_option >::=
{Pad_index |
Fillfactor = fillfactor |
Ignore_dup_key |
Drop_existing |
Statistics_norecompute |
Sort_in_tempdb
}

Parameters
Unique

Create a unique index for a table or view (two rows with the same index value are not allowed ). The clustered index on the view must be a unique index.

When creating an index, if the data already exists, Microsoft? SQL Server? Checks whether repeated Values exist and performs this check every time data is added using the insert or update statement. If duplicate key values exist, the create index statement is canceled and an error message is returned, indicating the first duplicate value. When a unique index is created, multiple null values are considered as copies.

If a unique index exists, the update or insert statement that generates duplicate key values will be rolled back, and the SQL server will display an error message. This can happen even if the update or insert statement changes many rows but only produces one duplicate value. If data is input with a unique index and the ignore_dup_key clause is specified, only rows that violate the unique index will fail. Ignore_dup_key does not work when processing the update statement.

SQL Server does not allow creating unique indexes for columns that already contain duplicate values, regardless of whether ignore_dup_key is set. If you try to do this, SQL Server displays an error message. duplicate values must be deleted before you can create a unique index for these columns.

Clustered

Create an object where the physical sorting of rows is the same as that of the index, and the lowest level (leaf level) of the clustered index contains the actual data rows. A table or view can have only one clustered index at a time.

A view with clustered indexes is called an index view. You must create a unique clustered index for the view before defining other indexes for the view.

Create a clustered index before creating any non-clustered index. When creating a clustered index, recreate the existing non-clustered index on the table.

If clustered is not specified, a non-clustered index is created.

 

The clustering index has the same leaf level as its data page according to the definition, therefore, when you create a clustered index, The on filegroup clause will actually move the table from the file used when the table is created to the new file group. Before creating a table or index on a specific file group, check which file groups are available and have sufficient space for the index. The size of the file group must be at least 1.2 times the size required for the entire table.

Nonclustered

Creates a logical sorting object for the specified table. For non-clustered indexes, the physical sorting of rows is independent of the index sorting. The leaf level of a non-clustered index contains the index rows. Each index row contains a non-clustered key value and one or more row locators (pointing to rows containing this value ). If the table has no clustered index, the row locator is the disk address of the row. If a table has a clustered index, the row locator is the clustered index key of the row.

Each table can have a maximum of 249 non-clustered indexes (no matter how these non-clustered indexes are created: using primary key and unique constraints for implicit creation or using CREATE index for explicit creation ). Each index can provide access to data in different sorting order.

For an index view, you can only create a non-clustered index for a view that has a clustered index defined. Therefore, the row positioner of non-clustered indexes in the index view must be the clustering key of rows.

Index_name

Is the index name. The index name must be unique in the table or view, but not in the database. The index name must follow the identifier rules.

Table

The table that contains the columns for which the index is to be created. You can specify the database and table owner.

View

The name of the view to which the index is to be created. You must use schemabinding to define a view to create an index on The View. View definitions must also be deterministic. If all expressions, where, and group by clauses in the selection list are deterministic, the view is also deterministic. Furthermore, all key columns must be accurate. Only a non-key column of a view may contain a floating point expression (using a float data expression), and a float expression cannot be used anywhere in the view.

To search for columns in the deterministic view, use the columnproperty function (isdeterministic attribute ). The isprecise attribute of this function can be used to determine whether the key column is accurate.

You must first create a unique clustered index for the view to create a non-clustered index for the view.

In SQL Server Enterprise or development edition, the query optimizer can use the index view to speed up query execution. OptimizationProgramTo replace the view, you do not need to reference it in the query.

When creating an index view or performing operations on the rows in the Table participating in the index view, there are seven set options that must be specified. Set options: arithabort, concat_null_yields_null, quoted_identifier, ansi_nulls, ansi_padding, and ansi_warning must be on. Set option numeric_roundabort must be off.

If it is different from the preceding settings, the data modification statements (insert, update, and delete) executed on any table referenced by the index view will fail, and the SQL server will display an error message, lists All set options that violate the settings. In addition, for select statements involving the index view, if the value of any set option is not the required value, SQL server does not consider replacing the index view when processing the SELECT statement. This ensures that the query results are correct when the set option is affected.

If the application uses a DB-library connection, you must assign the required values to all seven set options on the server. (By default, set options except arithabort are set correctly for ole db and ODBC connections .)

If not all of the preceding set options have the required values, some operations (such as BCP, replication, or distributed queries) may not be able to update the tables involved in the index view. In most cases, setting arithabort to on can avoid this problem.

We strongly recommend that you create the first index view or index on the computing column in any database on the server and set the arithabort user option to on as early as possible within the server range.

For more information about the notes and restrictions of the index view, see the Notes section.

Column

Apply the index column. Specify two or more column names to create a composite index for the combined values of the specified column. List the columns to be included in the composite index (sorted by order priority) in the parentheses after the table ).

 

It indicates that columns composed of ntext, text, or image data types cannot be specified as index columns. In addition, a view cannot contain any text, ntext, or image columns, even if they are not referenced in the create index statement.

When two or more columns are best searched as one unit, or many queries reference only the specified columns in the index, a composite index should be used. A maximum of 16 columns can be combined into a composite index. All columns in the composite index must be in the same table. The maximum size of the combined index value is 900 bytes. That is to say, the total length of a fixed-size column that compose a composite index cannot exceed 900 bytes. For more information about variable type columns in a composite index, see the comments section.

[ASC | DESC]

Determines the ascending or descending direction of an index column. The default value is ASC.

N

It indicates that multiple columns placeholders can be specified for a specific index.

Pad_index

Specify the reserved space for each page (node) in the middle of the index. The pad_index option is useful only when fillfactor is specified, because pad_index uses the percentage specified by fillfactor. By default, given the key set on the intermediate page, SQL Server ensures that the available space on each index page can accommodate at least one of the largest rows allowed by the index. If the percentage specified for fillfactor is not large enough to accommodate a row, SQL server uses the allowed minimum internally to replace the percentage.

 

This indicates that the number of rows on the intermediate index page will never be less than two, no matter how small the fillfactor value is.

Fillfactor = fillfactor

Specify the page-level filling of each index during the SQL Server Index creation process. If an index page is filled up, SQL Server must take the time to split the index page to free up space for new rows, which requires a great deal of overhead. For tables with frequent updates, selecting an appropriate fillfactor value is better than selecting an inappropriate fillfactor value. The original value of fillfactor will be stored with the index in sysindexes.

If fillfactor is specified, SQL Server rounds up the number of rows to be placed on each page. For example, if you issue create clustered index... fillfactor = 33, a clustered index with fillfactor 33% will be created. Assume that SQL Server calculates 33% rows of space per page. SQL Server rounds it up so that six lines are placed on each page.

 

It indicates that the explicit fillfactor setting is only applied when the index is created for the first time. SQL Server does not dynamically retain the specified percentage of available space on the page.

The fillfactor value specified by the user can range from 1 to 100. If no value is specified, the default value is 0. If fillfactor is set to 0, only leaf pages are filled. You can change the default fillfactor settings by executing sp_configure.

Fillfactor 100 can be used only when no insert or update statement appears (for example, for a read-only table. If fillfactor is 100, SQL Server will create a leaf-level page 100% filled index. If you execute insert or update after creating an index with fillfactor of 100%, the page will be split for each insert operation and every update operation.

If the fillfactor value is small (except 0), SQL Server will create a new index with incomplete leaf pages. For example, if we know that the data contained in a table is only a small part of the final data to be included in the table, it is reasonable to set fillfactor to 10 when creating an index for the table. If the fillfactor value is small, the index occupies a large amount of storage space.

The following table describes how to fill the index page with fillfactor specified.

Fillfactor intermediate page leaf page
0 fill up an available item 100%
1%-99 one available item <= fillfactor % fill
100% one available item 100% fill

One available item refers to the space on the page that can accommodate another index item.

 

It is important to use a fillfactor value to create a clustered index, which affects the amount of storage space occupied by data, because SQL Server re-distributes data when creating a clustered index.

Ignore_dup_key

Controls what happens when you try to insert duplicate key values to columns that belong to a unique clustered index. If you specify ignore_dup_key for the index and execute the insert statement that creates the duplicate key, SQL server sends a warning message and ignores duplicate rows.

If the ignore_dup_key is not specified for the index, SQL server sends a warning message and rolls back the entire insert statement.

The following table shows when ignore_dup_key can be used.

Index type options
Clustering not allowed
Unique aggregation allows the use of ignore_dup_key
Non-clustering is not allowed
Unique non-clustering allows ignore_dup_key

Drop_existing

Specify that you should remove and recreate the named existing clustered index or non-clustered index. The specified index name must be the same as the existing index name. Because a non-clustered index contains a clustered key, a non-clustered index must be rebuilt when the clustered index is removed. If clustered indexes are re-built, non-clustered indexes must be re-built to use the new key set.

When a clustered index is created for a table that already has a non-clustered index (the same or different key sets are used), The drop_existing clause can improve the performance. The drop_existing clause replaces the execution of the drop index Statement on the old clustered index, and then the execution of the create index statement on the new clustered index. Non-clustered indexes only need to be rebuilt once, and are only required when keys are different.

If the key is not changed (the provided index name and column are the same as the original index), The drop_existing clause will not sort the data again. This is useful when indexes must be compressed.

You cannot use the drop_existing clause to convert a clustered index to a non-clustered index. However, you can change a unique clustered index to a non-unique index, and vice versa.

 

When the create index statement with the drop_existing clause is executed, SQL Server assumes that the index is consistent (that is, the index is not damaged ). The rows in the specified index should be sorted by the specified key referenced in the create index statement.

Statistics_norecompute

The specified expired index statistics are not automatically recalculated. To restore automatic update statistics, You can execute update statistics without the norecompute clause.

 

Important If you disable automatic re-calculation of distribution statistics, the SQL Server Query Optimizer may be prevented from selecting the best execution plan for queries involving the table.

Sort_in_tempdb

The intermediate sorting result used to generate the index is stored in the tempdb database. If tempdb and user database are not in the same disk set, this option may reduce the time required to create an index, but will increase the disk space used to create an index.

For more information, see tempdb and index creation.

On filegroup

Creates a specified index on a given filegroup. The file group must have been created by executing create database or alter database.

Note
When a table or index is allocated space, an extended Disk Area (8 8 KB pages) is increased each time ). Each time you fill in an extended disk area, one will be assigned. If a table is very small or empty, its index will be allocated on a single page until eight pages are added to the index, and then the extended Disk Area is allocated. To obtain a Report on the amount of space allocated and occupied by the index, use sp_spaceused.

Creating clustered indexes requires that the available space in the database be approximately 1.2 times the data size. This space does not include the space occupied by existing tables. Data will be copied to create a clustered index. The old unindexed data will be deleted after the index is created. When the drop_existing clause is used, the space required for clustered indexes is the same as that for existing indexes. The extra space required may also be affected by the specified fillfactor.

When creating an index in SQL Server 2000, you can use the sort_in_tempdb option to indicate that the database engine stores intermediate index sorting results in tempdb. If tempdb is different from the disk set where the user database is located, this option may reduce the time required to create an index, but will increase the disk space used to create an index. In addition to the space required to create an index in the user database, tempdb must have approximately the same additional space to store intermediate sorting results. For more information, see tempdb and index creation.

The create index statement is optimized like other queries. The SQL Server Query processor can scan another index instead of executing a table scan to save I/O operations. In some cases, sorting is not required.

On a multi-processor computer running SQL Server Enterprise Manager and programmer edition, create Index automatically uses multiple processors to perform scanning and sorting, which is the same as other queries. The number of processors used to execute a create index statement is determined by the configuration option Max degree of parallelism and the current workload. If SQL server detects that the system is busy, the concurrency of the create index operation is automatically reduced before the statement is executed.

All file groups affected by the create index statement since the last file group backup must be backed up as one unit. For more information about file and file group backup, see backup.

The backup and create index operations do not affect each other. If a backup is in progress, an index is created in the full logging mode, which may require additional log space.

To display reports about object indexes, execute sp_helpindex.

You can create indexes for temporary tables. When a table is removed or the session is terminated, all indexes and triggers are removed.

Variable type columns in the Index
The maximum size of the index key is 900 bytes. However, SQL Server 2000 allows you to create an index on a column that may contain a large number of variable types of columns. The maximum size of these columns exceeds 900 bytes.

When creating an index, SQL Server checks the following conditions:

The total length of all fixed data columns involved in index definition must be less than or equal to 900 bytes. When the index to be created is composed of only fixed data columns, the total size of the fixed data column must be less than or equal to 900 bytes. Otherwise, you cannot create an index and SQL server returns an error.

If the index definition consists of Fixed-type columns and variable-type columns, and the fixed data column meets the preceding conditions (less than or equal to 900 bytes ), the SQL Server still needs to check the total size of a variable type column. If the maximum size of a variable type column and the sum of the fixed data column size is greater than 900 bytes, SQL Server creates an index. However, a warning message is returned to remind you: if the total size of a subsequent insertion or update operation on a variable type column exceeds 900 bytes, the Operation will fail and the user will receive a runtime error. Similarly, if the index definition only consists of Variable-type columns and the maximum size of these columns is greater than 900 bytes, SQL Server creates an index, but returns a warning message.
For more information, see the maximum value of the index key.

Considerations when creating an index on a calculated column and view
In SQL Server 2000, you can also create indexes on computing columns and views. Creating a unique clustered index on a view can improve query performance, because the view is stored in the database in the same way as the table with clustered indexes.

As long as the unique or primary key meets all the index conditions, it can contain the computed column. Specifically, the calculated column must be deterministic, accurate, and cannot contain text, ntext, or image columns. For more information about certainty, see deterministic and non-deterministic functions.

Creating an index on a calculated column or view may cause the previous insert or update operation to fail. This error may occur when the calculation column causes an arithmetic error. For example, although column C in the following table may cause arithmetic errors, the insert statement is still valid:

Create Table T1 (A int, B INT, C as A/B)
Go
Insert into T1 values ('1', '0 ')
Go

On the contrary, if an index is created on column C after a table is created, the insert statement fails.

Create Table T1 (A int, B INT, C as A/B)
Go
Create unique clustered index idx1 on t1.c
Go
Insert into T1 values ('1', '0 ')
Go

The query results obtained by using indexes on a view defined by a number or float expression may be different from the results obtained by similar queries that do not use indexes on The View. This difference may be caused by rounding errors during insert, delete, or update operations on the base table.

To prevent SQL Server from using the index view, include the option (expand views) Prompt in the query. In addition, incorrect settings of any of the listed options will prevent the optimizer from using the indexes on The View. For more information about option (expand views) prompts, see select.

Restrictions on the index View
The SELECT statement defining the index view cannot contain the top, distinct, compute, having, and Union keywords. Nor can it contain subqueries.

The select list cannot contain asterisks (*), 'table. * 'wildcard list, distinct, count (*), count (<expression>), calculation columns, and scalar aggregation in the base table.

The non-aggregate select list cannot contain expressions. The aggregate select list (queries containing group by) may contain sum and count_big (<expression>); it must contain count_big (*). Other Aggregate functions (Min, Max, STDev,...) are not allowed ,...).

Complex aggregation using AVG cannot participate in the select list of the indexed view. However, if the query uses such aggregation, the optimizer will be able to use this index view and replace AVG with a simple aggregation combination of sum and count_big.

If a column is obtained from a float data type or an expression that uses a float expression for value, it cannot be used as the index key of the index view or computed column in the table. Such columns are considered inaccurate. Use the columnproperty function to determine whether the columns in a specific calculation column or view are accurate.

The index view is subject to the following additional restrictions:

The creator of the index must own the table. All tables, views, and indexes must be created in the same database.

Select statements defining the index view cannot contain views, row set functions, in-row functions, or derived tables. The same physical table can only appear once in this statement.

In any join table, the outer join operation is not allowed.

Subquery, contains, or freetext predicates cannot be used in search conditions.

If a view definition contains a group by clause, the select list of the view must contain all groups based on the column and count_big (*) expression. In addition, the create unique clustered index clause must contain only these columns.
The definition subject of a view that can create an index must be deterministic and accurate, which is similar to the index requirement of a computing column. For more information, see create an index on a calculated column.

Permission
The create index permission is granted to SysAdmin fixed server roles, db_ddladmin and db_owner fixed database roles and table owners by default, and cannot be transferred.

Example
A. Use simple Indexes
The following example creates an index for the au_id column of the authors table.

Set nocount off
Use pubs
If exists (Select name from sysindexes
Where name = 'au _ id_ind ')
Drop index authors. au_id_ind
Go
Use pubs
Create index au_id_ind
On authors (au_id)
Go

B. Use a unique clustered Index
The following example creates an index for the employeeid column of the emp_pay table and enforces uniqueness. Because the clustered clause is specified, the index physically sorts the data on the disk.

set nocount on
Use pubs
If exists (select * From information_schema.tables
where table_name = 'EMP _ pay ')
drop table emp_pay
go
Use pubs
If exists (Select name from sysindexes
where name = 'employeeid _ ind ')
drop index emp_pay.employeeid_ind
go
Use pubs
go
Create Table emp_pay
(
employeeid int not null,
base_pay money not null,
Commission decimal (2, 2) not null
)
insert emp_pay
values (1,500 ,. 10)
insert emp_pay
values (2, 1000 ,. 05)
insert emp_pay
values (3,800 ,. 07)
insert emp_pay
values (5, 1500 ,. 03)
insert emp_pay
values (9,750 ,. 06)
go
set nocount off
create unique clustered index employeeid_ind
On emp_pay (employeeid)
go

C. Use a Simple Composite Index
The following example creates an index for the orderid column and the employeeid column of the order_emp table.

Set nocount on
Use pubs
If exists (select * From information_schema.tables
Where table_name = 'order _ emp ')
Drop table order_emp
Go
Use pubs
If exists (Select name from sysindexes
Where name = 'emp_order_ind ')
Drop index order_emp.emp_order_ind
Go
Use pubs
Go
Create Table order_emp
(
Orderid int identity (1000, 1 ),
Employeeid int not null,
Orderdate datetime not null default getdate (),
Orderamount money not null
)

insert order_emp (employeeid, orderdate, orderamount)
values (5, '2014/1/98 ', 4/12)
insert order_emp (employeeid, orderdate, orderamount)
values (5, '2014/1/98 ', 5/30)
insert order_emp (employeeid, orderdate, orderamount)
values (1, '2014/1/98 ', 2039.82)
insert order_emp (employeeid, orderdate, orderamount)
values (1, '2014/1/98 ', 1/22)
insert order_emp (employeeid, orderdate, orderamount)
values (4, '2014/1/98 ', 4/05)
insert order_emp (employeeid, orderdate, orderamount)
values (7, '2014/1/98 ', 3/21)
insert order_emp (employeeid, orderdate, orderamount)
values (7, '2014/1/98', 1598.23)
insert order_emp (employeeid, orderdate, orderamount)
values (7, '2017/98 ', 3/22)
go
set nocount off
Create index emp_order_ind
On order_emp (orderid, employeeid)

D. Use the fillfactor option.
The following example uses the fillfactor clause to set it to 100. If fillfactor is 100, each page is fully filled. This option is useful only when the index value in the table is determined to never change.

Set nocount off
Use pubs
If exists (Select name from sysindexes
Where name = 'zip _ ind ')
Drop index authors.zip _ ind
Go
Use pubs
Go
Create nonclustered index zip_ind
On authors (ZIP)
With fillfactors = 100

E. Use ignore_dup_key
The following example creates a unique clustered index for the emp_pay table. If a duplicate key is entered, the insert or update statement is ignored.

set nocount on
Use pubs
If exists (select * From information_schema.tables
where table_name = 'EMP _ pay ')
drop table emp_pay
go
Use pubs
If exists (Select name from sysindexes
where name = 'employeeid _ ind ')
drop index emp_pay.employeeid_ind
go
Use pubs
go
Create Table emp_pay
(
employeeid int not null,
base_pay money not null,
Commission decimal (2, 2) not null
)
insert emp_pay
values (1,500 ,. 10)
insert emp_pay
values (2, 1000 ,. 05)
insert emp_pay
values (3,800 ,. 07)
insert emp_pay
values (5, 1500 ,. 03)
insert emp_pay
values (9,750 ,. 06)
go
set nocount off
go
create unique clustered index employeeid_ind
On emp_pay (employeeid)
with ignore_dup_key

F. Use pad_index to create an index
The following example creates an index for the Author ID in the authors table. Without the pad_index clause, SQL Server will create a leaf-level page filled with 10%, but the pages above the leaf-level are almost completely filled. When pad_index is used, the intermediate page also fills up 10%.

 

If pad_index is not specified, at least two items will appear on the index page of the unique clustered index.

Set nocount off
Use pubs
If exists (Select name from sysindexes
Where name = 'au _ id_ind ')
Drop index authors. au_id_ind
Go
Use pubs
Create index au_id_ind
On authors (au_id)
With pad_index, fillfactor = 10

G. Create an index for the view
The following example creates a view and creates an index for the view. Then, two queries using the index view are introduced.

Use northwind
Go

-- Set the options to support indexed views.
Set numeric_roundabort off
Go
Set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
Go

-- Create view.
Create view V1
With schemabinding
As
Select sum (unitprice * quantity * (1.00-discount) as revenue, orderdate, productid, count_big (*) as Count
From DBO. [Order Details] OD, DBO. Orders o
Where OD. orderid = O. orderid
Group by orderdate, productid
Go

-- Create index on The View.
Create unique clustered index iv1 on V1 (orderdate, productid)
Go

-- This query will use the above indexed view.
Select sum (unitprice * quantity * (1.00-discount) as Rev, orderdate, productid
From DBO. [Order Details] OD, DBO. Orders o
Where OD. orderid = O. orderid and productid in (2, 4, 25, 13, 7, 89, 22, 34)
And orderdate> = '2014/1/123'
Group by orderdate, productid
Order by REV DESC

-- this query will use the above indexed view.
select orderdate, sum (unitprice * quantity * (1.00-discount) as Rev
from DBO. [Order Details] OD, DBO. orders o
where OD. orderid = O. orderid and datepart (mm, orderdate) = 3
and datepart (YY, orderdate) = 1998
group by orderdate
order by orderdate ASC