SQL Server Index Introduction

I. Overview of Indexes

1. Concept: a database index is a structure that sorts the values of one or more columns in a data table, just like a directory in a book, indexes provide the ability to quickly query specific rows in rows.

2. Advantages and Disadvantages:

2.1 Advantages: 1. It greatly accelerates data search, which is the main reason for the introduction of indexes.

2. Create a unique index to ensure the uniqueness of each row of data in the database table.

3. Accelerate the connection between tables, especially for Data Reference integrity.

4. Data Retrieval Using grouping and sorting clauses can also reduce the time used.

2. 2 disadvantages: 1. The index requires physical space and the clustered index occupies a larger space.

2. It takes time to create and maintain indexes. This time will increase as the data volume increases.

3. When you add or modify a record to a data table that contains an index, SQL server modifies and maintains the corresponding index, which increases the additional overhead of the system and reduces the processing speed.

3. Index classification:

1. The storage structure can be divided:

A. Clustered index: the physical storage order is identical to the index order. It consists of the upper and lower layers. The upper layer is the index page and the lower layer is the data page. There is only one sorting method, therefore, each table can only create one clustered index.

B. Non-clustered index: the data storage order is generally different from the storage structure of the physical data of the table. The following table shows how to create a non-clustered index on the student ID)

2. Check whether the base index key value is unique and whether it is a unique index. The index created based on the combination of multiple fields is a composite index. 2. Index operations:

1. Create: (1) Principle: a. Only the table owner can create an index in the same table;

B. Each table can only create one clustered index;

C. A maximum of 249 non-clustered indexes can be created in each table;

D. Create an index on frequently queried fields;

E. indexes cannot be created when the data types of text, image, and bit are defined;

F. You can create an index on the foreign key column. The primary key must have an index;

G. Do not create an index on columns with a small number of duplicate values.

(2) method: a. use SQL server Management Studio to create an index.

B. Use the create Index statement in the T-SQL statement to create an Index

C. When you use the Create table or alter Table statement to define the primary key constraint or uniqueness constraint for a table column, the primary key index and unique index are automatically created.

Here we will talk about how to create an index using the T-SQL statement:

Syntax:

Copy codeThe Code is as follows: create relational index create [unique] [clustered | nonclustered] index index_name on <object> (cloumn [asc | desc] [,… N]) [include (column_name [,...... N])] [with (<relational_index_option> [,...... N])] [onfilegroup_name]

Note: 1, include (column_name [,...... N]) specifies the leaf-level non-key column to be added to a non-clustered index.

2. on filegroup_name: Creates the specified index for the specified file group.

For example, in the course table, create a clustered index zindex for the "course code" column.

Copy codeThe Code is as follows: use db_student create clustered index zindex on course (course code)

2. View indexes: (1) use SQL ServerManagement Studio to view index information

(2) Use the system stored procedure to query index information. Use SP_helpindex to return all index information in the table.

For example, you can view the index information of the course table.

Use db_student execsp_helpindex course [/code]

3. Modify the index:
 
(1) modify the index in SQL Server Management Studio
 
(2) Use the Alter Index statement to modify the Index.
 
Here is an example:
 
Modify all indexes in the course data table and specify options
 
Copy codeThe Code is as follows: use db_student alterindex all on course rebuild with (fillfactor = 80, sort_in_tempdb = on, statistics_norecompute = on)

4. delete an index:
 
(1) use SQL Server Management Studio to delete an index
 
(2) Use the Drop index statement to delete the index.
 
For example, in the course table, delete the zindex
 
Copy codeThe Code is as follows: use db_student drop index course. zindex

Iii. Index Analysis and maintenance: 
 
Analysis: 1. Use the showplan statement
 
Syntax: set showplan_all {on | off}, set showplan_next {on | off}
 
Example: shows the course code, course type, course content, and Query Process of the course table.
 
Copy codeThe Code is as follows: use db_student set showplan_all on select course code, course type course content from course where course content = 'loving'

2. Use the statistics io statement
 
Syntax: statistics io {on | off} on and off are displayed and not displayed, respectively. The usage is the same as on.
 
Maintenance: 1. Use the dbcc showcontig statement to display the data and index fragmentation information of the specified table. After a large amount of data is modified or added to a table, run this statement to check whether fragments exist.
 
Syntax: dbcc showcontig [{table_name | table_id | view_name | view_id}, index_name | index_id] with fast
 
2. Use the dbcc dbreindex statement to reconstruct one or more indexes of tables in the database.
 
Syntax:
Copy codeThe Code is as follows: dbcc dbreindex (['database. owner. table_name '[, index_name [, fillfactor]) [withno_infomsgs]
Description: database. owner. table_name. Name of the table for re-Indexing
 
Index_name: the name of the index to be rebuilt.
 
Fillfactor: the percentage of space on each index page to store data when you create an index.
 
With no_infomsgs, disable display of all information messages
 
 
3. Use dbcc indexdefrag to sort out the clustered index and secondary index fragmentation of the specified table or view.
 
Syntax:
Copy codeThe Code is as follows: dbcc indexdefrag ({database_name | database_id | 0}, {table_name | table_id | 'view _ name' | view_id}, {index_name | index_id}) with no_infomsgs

To sum up, we only have full familiarity with indexes. We have mastered four basic operations for adding, deleting, modifying, and querying indexes, and learned to use SQL Server ManagerSdudio to implement these functions, and learn to use T-SQL statements to achieve (self-feeling the use of SQL Server Manager Sdudio is simpler); of course, you also need to learn to analyze and maintain the index, so that it will better let it serve us!