SQL Server Index Introduction _mssql2005

One, overview of the index

1, Concept: A database index is a structure that sorts the values of one or more columns in a datasheet, like a book's TOC, which provides the ability to quickly query specific rows in a row.

2, advantages and disadvantages:

2.1 Advantages: 1, greatly speed up the search data, which is the main reason for the introduction of the index.

2, create a unique index that guarantees the uniqueness of each row of data in a database table.

3, it is particularly meaningful to accelerate the connection between table and table, especially in the realization of referential integrity of data.

4, when you use grouping and sorting clauses for data retrieval, you can also reduce their use time.

2,2 Disadvantage: 1, the index needs to occupy the physical space, the clustered index occupies a larger space.

2, it takes time to create indexes and maintain indexes, which can increase as the amount of data increases.

3, when you add or modify records to a datasheet that contains an indexed column, SQL Server modifies and maintains the corresponding index, which increases the overhead of the system and reduces processing speed.

3, the classification of the index:

1, according to the storage structure can be divided into:

A, clustered index: refers to the physical storage order is exactly the same as the index order, it consists of the upper and lower layers, the top of the index page, the lower level is a data page, only one sort, so each table can only create a clustered index.

B, nonclustered indexes: the stored data order generally differs from the storage structure of the physical data of the table. The following table allows us to analyze: (in which a nonclustered index is established on the school number)

2, the basic index key value is unique, you can determine whether a unique index, based on the combination of multiple fields to create the index of the composite index. Second, the operation of the index:

1, create: (1), principle: A, only the owner of the table can create an index in the same table;

b, only one clustered index can be created in each table;

C, you can create up to 249 nonclustered indexes per table;

D, the index is established on the field of the frequently queried;

E, defining text,image and bit data types of lacerations cannot create an index;

F, the foreign key column can be indexed, the primary key must be indexed;

G, do not index on columns with fewer queries than those with duplicate values.

(2), method: A, to create an index using SQL Server Management Studio.

b, creating an index using the CREATE INDEX statement in a T-SQL statement

C, primary key and unique indexes are automatically created when you use CREATE TABLE or ALTER TABLE statements to define a PRIMARY KEY constraint or uniqueness constraint for a table column.

Here's a T-SQL statement creates an index.

Grammar:

Copy Code code 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]

Description: 1,include (column_name[,...... n]) specifies the Non-key columns to add to the leaf level of the nonclustered index.

2,on filegroup_name, creates the specified index for the specified filegroup.

For example, in the course table, create a clustered index ZIndex for the course Code column.

Copy Code code as follows:

Use db_student create clustered index ZIndex on course (course code)

2, viewing indexes: (1), using SQL Servermanagement Studio to view index information

(2), use System stored procedure to query index information, use SP_HELPINDEX to return all index information in the table

For example: View index information for a course table

Use Db_student Execsp_helpindex Course[/code]

3, modify the index:
 
(1) Modifying indexes in SQL Server Management Studio
 
(2) Modifying an index using the ALTER INDEX statement
 
Here's an example for everyone:
 
In the course datasheet, modify all indexes and specify options
 

 
 

  
  
 
   Copy Code code as follows: 
  
 
 
 
 

  
  

    Use Db_student AlterIndex All in Course rebuild with (Fillfactor=80,sort_in_tempdb=on,statistics_norecompute=on) 
  
 
 
 

4, delete index:
 
(1), using SQL Server Management Studio to delete indexes
 
(2) Using the DROP INDEX statement to delete an index
 
For example: In the course table, delete the zindex index
 

 
 

  
  
 
   Copy Code code as follows: 
  
 
 
 
 

  
  

    Use db_student DROP Index Course.zindex 
  
 
 
 

third, the analysis and maintenance of the index: 
 
Analysis: 1, using the SHOWPLAN statement
 
Syntax: Set Showplan_all{on|off},set Showplan_next{on|off}
 
Example: Show Table Course Course code, course type, course content, and show query process
 

 
 

  
  
 
   Copy Code code as follows: 
  
 
 
 
 

  
  

    Use db_student set SHOWPLAN_ALL on select Course Code, course type course content from course where course content = ' loving ' 
  
 
 
 

2, using the statistics IO statement
 
Syntax: Statistics Io{on|off} on and off are displayed and not displayed respectively, using the same method as on.
 
Maintenance: 1, use the DBCC SHOWCONTIG statement to display fragmentation information for the data and indexes of the specified table. When you make a large number of modifications to a table or add data, you should execute this statement to see if there is any fragmentation.
 
Syntax: DBCC SHOWCONTIG[{TABLE_NAME|TABLE_ID|VIEW_NAME|VIEW_ID},INDEX_NAME|INDEX_ID] with fast
 
2, using the DBCC DBREINDEX statement, which means rebuilding one or more indexes of the tables in the database.
 
Grammar:

 
 

  
  
 
   Copy Code code as follows: 
  
 
 
 
 

  
  

    DBCC DBREINDEX ([' Database.owner.table_name ' [, Index_name[,fillfactor]]]] [WITHNO_INFOMSGS] 
  
 
 
 
Description: Database.owner.table_name, rebuild the table name of the index
 
Index_name, is the name of the index to be rebuilt
 
FillFactor the percentage of space to be used for storing data on each index page when the index is created.
 
With NO_INFOMSGS, suppresses all informational messages
 
 
3, use DBCC INDEXDEFRAG to defragment the clustered index and secondary index fragments of the specified table or view.
 
Grammar:

 
 

  
  
 
   Copy Code code 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, only we are fully familiar with the index, we have mastered the index of additions and deletions to check the four basic operations, learn to use SQL Server Managersdudio to implement these functions, and learn to use T-SQL statements to achieve (self-sensing using SQL Server Manager Sdudio simple); Of course, we must learn to analyze and maintain the index, so that it will be better to serve us!