SQL Server-index Introduction

What is an index?

Take the directory pages (indexes) of the Chinese dictionary for example: just as the Chinese characters in the Chinese Dictionary are stored by page, the data records in SQL Server are also stored by PAGE, and the size of each page is generally 4 K. To speed up searching, Chinese characters (words) are usually sorted by pinyin, strokes, radicals, and other directories (indexes). You can choose to search by pinyin or strokes, quickly find the desired word ).

Similarly, SQL Server allows you to create indexes in a table and specify pre-sorting by a column, which greatly improves the query speed.

• Data in SQL Server is also stored by PAGE (4 KB)

• Index: an internal method for SQL Server to orchestrate data. It provides SQL server with a way to orchestrate and query data.

• Index page: The index data page stored in the database. The index page is similar to the directory page in Chinese characters (words), sorted by pinyin or strokes.

• Index Function: By using indexes, You can greatly improve the database retrieval speed and performance.

 

Index type

•Unique Index: The unique index does not allow two rows to have the same index value.

•Primary Key Index: Defining a primary key for a table will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique and cannot be blank.

•Clustered Index(Clustered): The physical order of each row in the table is the same as the logic (INDEX) Order of the key value. Each table can have only one

•Non-clustered Index(Non-clustered): Non-clustered index specifies the logical sequence of the table. The data is stored in one location, and the index is stored in another location. The index contains a pointer to the data storage location. There can be more than 249

 

Index type: for example, we hope you can understand the concepts of clustered and non-clustered indexes.

 

Unique index:

The unique index does not allow two rows to have the same index value.

If duplicate key values exist in existing data, most databases do not allow you to save the newly created unique index with the table. When the new data duplicates the key value in the table, the database also rejects the data. For example, if the student ID number (stuid) column in The stuinfo table creates a unique index, the ID number of all students cannot be the same.

Tip: If a unique constraint is created, a unique index is automatically created. Although the unique index helps to find information, we recommend that you use the primary key constraint or unique constraint for optimal performance.

 

Primary Key Index:

Defining a primary key for a table in the database relationship diagram will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to data.

 

Clustered Index)

In a clustered index, the physical order of each row in the table is the same as that of the key-value Logic (index. A table can contain only one clustered index. For example, by default, the Chinese character (Word) is sorted by pinyin to orchestrate the page number of each page in the dictionary. Pinyin letters a, B, c, d ...... X, Y, and Z are the logical order of the index, and the page numbers are 1, 2, 3 ...... Physical order. By default, the index order and logical order of a dictionary are the same. That is, the page numbers of words (words) with a higher pinyin order are also large. For example, the page number of the word (Word) corresponding to the pinyin "ha" is later than that of the word (Word) corresponding to the pinyin "ba.

 

Non-clustered Index(Non-clustered)

If it is not a clustered index, the physical sequence of each row in the table does not match the logical sequence of the key value. Clustered indexes provide faster data access speeds than non-clustered indexes. For example, an index sorted by strokes is a non-clustered index. The number of pages corresponding to the words (words) in the "1" painting may be different from the words (words) in the "3" painting) the corresponding page number is large (back ).

Tip: in SQL Server, only one clustered index and multiple non-clustered indexes can be created for a table. If a column is set as the primary key, the column is a clustered index by default.

 

How to create an index

Syntax for creating an index using T-SQL statements:

Create [unique] [clustered | nonclustered]

Index index_name

On table_name (column_name ...)

[With fillfactor = x]

Q unique indicates a unique index. Optional.

Q clustered and nonclustered indicate clustered or non-clustered indexes. Optional.

Q fillfactor indicates the fill factor, which specifies a value between 0 and 100. This value indicates the percentage of space occupied by the index page.

 

Create an index in the writtenexam column of the stumarks table:

Use studb

Go

If exists (Select name from sysindexes

Where name = 'ix _ writtenexam ')

Drop index stumarks. ix_writtenexam

/* -- Create a non-clustered index for the written test column: the fill factor is 30% --*/

Create nonclustered index ix_writtenexam

On stumarks (writtenexam)

With fillfactor = 30

Go

/* ----- Specify to query by index ix_writtenexam ----*/

Select * From stumarks (Index = ix_writtenexam)

Where writtenexam between 60 and 90

Although we can specify which index SQL Server queries data, we generally do not need to manually specify it. SQL Server will automatically optimize the query based on the created index.

 

Advantages and disadvantages of Indexes

• Advantages

-Faster access

-Strengthen the uniqueness of rows.

• Disadvantages

-More storage space is required for indexed tables in the database.

-Data manipulation commands take longer to process because they need to update indexes.

 

Guidelines for creating Indexes

• Select an index column based on the following criteria.

-It is best to create an index for data columns that are frequently queried by this column.

-This column is used to sort data.

-To define a data column with a foreign key, you must create an index. Foreign key columns are usually used for the connection between tables. Creating an index on a table can accelerate the connection between tables.

• Do not create an index using the following columns:

-Do not create indexes for columns that are rarely involved in queries and columns with more repeated values.

-The table contains only a few rows. Creating an index for a small table may be less cost-effective because SQL Server takes longer time to search for data in the index than to search for data row by row in the table.

-Do not create indexes for columns defined as text, image, or BIT data. Because the data size of these data types of data columns is either large or small, it is not conducive to the use of indexes.

From Baidu Library