SQL Server CREATE index

Introduction to the index:

Indexes are clustered and nonclustered, and the index in the database is similar to a book's directory, where you can quickly find the information you want in a book, without having to read through the books.

The main purpose of the index is to improve the performance of the SQL Server system, speed up the data query and reduce the response time of the system.

But the index is not all-powerful for improving query performance, and it is not the better to build more indexes. With fewer indexes, finding data with a WHERE clause is inefficient and not conducive to finding data. Indexes are built up, and are not conducive to new, modified, and deleted operations, because SQL SERVER, in addition to updating the data table itself, also updates all relevant indexes immediately, and too many indexes waste hard disk space.

Classification of indexes:

The index is similar to the Chinese dictionary in front of the directory, according to pinyin or the Ministry of the capital can quickly locate the word you want to find.

Unique index (unique): Index values are unique for each row (unique constraints are created and unique indexes are created automatically)

Primary KEY index: The primary key column that is specified when the table is created automatically creates a primary key index and has a unique attribute.

Clustered index (CLUSTERED): A clustered index is the equivalent of using a dictionary's phonetic lookup, because the clustered index store record is physically contiguous, that is, Ruby A is behind a certain B.

Nonclustered indexes (nonclustered): Nonclustered indexes are equivalent to using the dictionary's radical lookup, the nonclustered indexes are logically contiguous, and the physical storage is not contiguous.

PS: Clustered index A table can have only one, and a table of non-clustered indexes may exist multiple.

Where to use the index:

Grammar:

CREATE [UNIQUE] [CLUSTERED | Nonclustered] INDEXindex_name on <Object>(column_name[ASC | DESC] [,... N] )       [With <backward_compatible_index_option> [,... n] ]      [On {filegroup_name | "Default"}]    <Object>::=  {      [database_name. [Owner_name].|Owner_name.] Table_or_view_name}<Backward_compatible_index_option>::={pad_index| FILLFACTOR = FillFactor    |sort_in_tempdb|Ignore_dup_key|Statistics_norecompute|Drop_existing}

Parameters:

Unique: Creates a unique index for a table or view. A unique index does not allow two rows to have the same index key value. The clustered index of the view must be unique. If the column you want to create a unique index has duplicate values, you must first delete the duplicate values.

CLUSTERED: Indicates that the specified index was created as a clustered index. When an index is created, the logical order of the key values determines the physical order of the corresponding rows in the table. The underlying (or leaf level) of the clustered index contains the actual data rows of the table.

Nonclustered: Indicates that the specified index was created as a nonclustered index. Creates an index that specifies the logical ordering of the table. For nonclustered indexes, the physical ordering of data rows is independent of the index ordering.

Index_name: Indicates the name of the index being created.

database_name: Represents the name of the specified database.

Owner_name: Indicates the specified owner.

Table: Indicates the name of the table that specifies the index to create.

View: Represents the name of the view that specifies the index to create.

Column: One or more columns on which the index is based. Specify two or more column names to create a composite index for the combined values of the specified columns.

[ASC | DESC]: Indicates the ascending or descending sort direction of the specified index column. The default value is ASC.

On filegroup_name: Creates the specified index for the specified filegroup. If no location is specified and the table or view has not been partitioned, the index will use the same filegroup as the underlying table or view. The filegroup must already exist.

On default: Creates the specified index for the default filegroup.

Pad_index = {on | OFF}: Specifies whether to index the population. The default is OFF.

On FillFactor applies to the Index intermediate level page by specifying the percentage of free space.

OFF or FILLFACTOR is not specified, taking into account the key set on the intermediate page, the intermediate-level pages are populated close to their capacity to allow enough space for at least one row of the index to be accommodated.

The PAD_INDEX option is useful only if FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR.

FILLFACTOR = FILLFACTOR: Used to specify that the data for each index page is a percentage of the index page size when the index is created, and the value of FILLFACTOR is 1 to 100.

sort_in_tempdb = {on | OFF}: Used to specify that intermediate sorting results are stored in the tempdb database when the index is created. The default is OFF.

The intermediate sort results for the build index are stored in tempdb. This may reduce the time that is required to create the index only if tempdb is in a different disk set than the user database.

OFF Intermediate sorting results are stored in the same database as the index.

Ignore_dup_key = {on | OFF}: Specifies the error response when an insert operation attempts to insert a duplicate key value into a unique index. The default is OFF.

On inserts a duplicate key value into a unique index, a warning message appears. Only rows that violate uniqueness constraints will fail.

OFF An error message occurs when a duplicate key value is inserted into a unique index. The entire INSERT operation is rolled back.

Statistics_norecompute = {on | OFF}: Used to specify whether expired index statistics are automatically recalculated. The default is OFF.

On does not automatically recalculate stale statistics.

OFF enables the automatic statistics update feature.

drop_existing = {on | Off}: Indicates if the index is still dropped on the table and then in create a new one. The default is OFF.

On specifies that you want to delete and regenerate the existing index, which must have the same name as the parameter index_name.

OFF Specifies that an existing index is not deleted and rebuilt. If the specified index name already exists, SQL Server displays an error.

Example:

To create a unique clustered index:

--Create a unique clustered indexCreate Unique Clustered        --represents creating a unique clustered indexIndexUq_clu_stuno--Index name onStudent (S_stuno)--data table name (indexed column name) with(Pad_index= on,--represents the use of padding    FillFactor= -,--indicates a fill factor of 50%Ignore_dup_key= on,--indicates that inserting duplicate values to a unique index ignores duplicate valuesStatistics_norecompute=off    --indicates that the automatic statistics Update feature is enabled)

To create a unique nonclustered index:

--Create a unique nonclustered indexCreate Unique nonclustered        --represents creating unique nonclustered indexesIndexUq_nonclu_stuno--Index name onStudent (S_stuno)--data table name (indexed column name) with(Pad_index= on,--represents the use of padding    FillFactor= -,--indicates a fill factor of 50%Ignore_dup_key= on,--indicates that inserting duplicate values to a unique index ignores duplicate valuesStatistics_norecompute=off    --indicates that the automatic statistics Update feature is enabled)

--Create a clustered indexCreate Clustered IndexClu_index onStudent (S_stuno) with(drop_existing= on)    --To create a nonclustered indexCreate nonclustered IndexNonclu_index onStudent (S_stuno) with(drop_existing= on)    --Create a unique indexCreate Unique IndexNonclu_index onStudent (S_stuno) with(drop_existing= on)

PS: When you create INDEX, if you do not specify clustered and nonclustered, the default is nonclustered.

To create a nonclustered composite index:

-- To create a nonclustered composite index Create nonclustered Index Index_stuno_sname  on Student (s_stuno,s_name)  with (drop_existing=on)

-- creating a nonclustered composite index, not specifying a nonclustered index by default Create Index Index_stuno_sname  on Student (s_stuno,s_name)  with (drop_existing=on)

Using the INCLUDE clause in the CREATE INDEX statement, you can define the included nonkey columns (that is, overwrite indexes) when you create the index with the following syntax:

CREATE nonclustered INDEX Index name  on {Table name | [][]  ) INCLUDE (< column Name 1>  < column Name 2>[,... n])

--Create a nonclustered overwrite indexCreate nonclustered IndexNonclu_index onStudent (S_stuno) include (s_name,s_height) with(drop_existing= on)--creating a nonclustered overwrite index, not specifying a nonclustered index by defaultCreate IndexNonclu_index onStudent (S_stuno) include (s_name,s_height) with(drop_existing= on)

PS: Clustered indexes Cannot create indexes that contain nonkey columns.

To create a filtered index:

--To create a nonclustered filtered indexCreate nonclustered IndexIndex_stuno_sname onStudent (S_stuno)whereS_stuno>= 001  andS_stuno<= 020 with(drop_existing= on)--creating a nonclustered filtered index, not specifying a nonclustered index by defaultCreate IndexIndex_stuno_sname onStudent (S_stuno)whereS_stuno>= 001  andS_stuno<= 020 with(drop_existing= on)

To modify an index:

-- Modify Index Syntax ALTER INDEX {Index name |  All }on< table name | View name >{REBUILD  | DISABLE  | REORGANIZE}[]

REBUILD: Represents the specified rebuild index.

DISABLE: Specifies that the index is marked as disabled.

REORGANIZE: Indicates the index leaf level at which the specified will be re-organized.

-- to disable an index named Nonclu_index Alter Index  on Student Disable

To delete and view an index:

--view indexes in the specified table Studentexecsp_helpindex Student--deletes the index named Index_stuno_sname in the specified table StudentDrop IndexStudent.index_stuno_sname--Check the fragmentation information for index Uq_s_stuno in table StudentDBCCShowcontig (Student,uq_s_stuno)--Defragment the index Uq_s_stuno of table Student in the Test databaseDBCCIndexdefrag (Test,student,uq_s_stuno)--UPDATE STATISTICS for all indexes in table StudentUpdate StatisticsStudent

Index definition principles:

Avoid too many indexes on frequently updated tables, and as few columns as possible in the index. For fields that are frequently used for queries, you should create an index, but avoid adding unnecessary fields.

Indexes are indexed on columns that are often used in conditional expressions, with a large number of different values, and do not index on columns with fewer values.

Indexes are indexed on columns that are frequently sorted or grouped (that is, group by or ORDER by operations), and if there are multiple columns to be sorted, a composite index can be established on those columns.

When selecting an index key, use columns of the small data type as the key so that each index page can hold as many index keys and pointers as possible, which minimizes the index pages that a query must traverse, and, as much as possible, uses integers as key values because the integer accesses the fastest.

Reference:

Http://www.cnblogs.com/knowledgesea/p/3672099.html

Https://msdn.microsoft.com/zh-cn/library/ms188783.aspx

SQL Server CREATE index