MENU service case
Website construction website design Beijing website construction high-end website production company Shangpin China
We create by embracing change
360 ° brand value__
simplified Chinese character
Simplified Chinese English

Shangpin China Joins Hands with Beisheng Internet to Create a New Chapter in Website Construction

Type: Shangpin Dynamic Learn more

If the SQL database is indexed and maintained

Source: Shangpin China | Type: website encyclopedia | Time: July 17, 2014
An index is a table of correspondence between column values and record rows established in a certain order according to one or more columns in the table. In order to improve query performance, you can create a prime index.
If the SQL database is indexed and maintained

1. Create index

In SQL, CREATE INDEX statements are used to build indexes. The general format is as follows:

CREATE [UNIQUE] [CLUSTER] INDEX<index name>

ON<basic table name>(<column name>[<order>], [,<column name>[<order>]]...);

explain:

(1) UNIQUE stipulates that each index value of the index only corresponds to the unique record in the table.

(2) CLUSTER specifies that this index is a clustered index. The so-called clustered index means that the order of index entries is consistent with the physical order of records in the table. Clustered indexes are particularly effective for columns that often search for range values. After using the clustered index to find the row containing the first value, you can ensure that the row containing the subsequent index value is physically adjacent. Using clustered indexes can greatly improve query performance. Omitting CLUSTER means that the created index is a nonclustered index, which is similar to the index in a book. Data is stored in one place, the index is stored in another place, and the index has Website production The pointer points to the storage location of the data. The items in the index are stored in the order of the index key values, while the information in the table is stored in another order (which can be specified by a clustered index).

(3) <Order>: When building an index, the index table of the specified column name is ASC (ascending order) or DESC (descending order). If not specified, it defaults to ascending order.

(4) The arrangement of the index created by this statement is as follows: first, sort by the name and value of the first column; The records with the same column value will be sorted by the next column name.

[Example 3.8] Create a nonclustered index on the attribute column Sno of the Student table.
CREATE INDEX IDX_DNO_SNO
ON Student(Sno ASC);
Example 3.91 Create a clustered index on the attribute column Sname of the Student table.

CREATE CLUSTER INDEX IDX_SNAME ON Student(Sname ASC);

2. Delete index

Although indexes can improve query efficiency, too many or improper indexes will lead to system inefficiency. Every time a user adds an index to a table, the database needs to do more work. Too many indexes may even lead to index fragmentation, reducing system efficiency. Therefore, unnecessary indexes should be deleted in a timely manner. The format of deleted indexes is as follows:

DROP INDEX<index name>

Note: This statement will delete the defined index, and the description of the index in the data dictionary will also be deleted.
[Example 3.10] Delete the index IDX_DNO_SNO of the Student table.

DROP INDEX IDX DNO SNO;
Source Statement: This article is original or edited by Shangpin China's editors. If it needs to be reproduced, please indicate that it is from Shangpin China. The above contents (including pictures and words) are from the Internet. If there is any infringement, please contact us in time (010-60259772).
TAG label:

What if your website can increase the number of conversions and improve customer satisfaction?

Make an appointment with a professional consultant to communicate!

* Shangpin professional consultant will contact you as soon as possible

Disclaimer

Thank you very much for visiting our website. Please read all the terms of this statement carefully before you use this website.

1. Part of the content of this site comes from the network, and the copyright of some articles and pictures involved belongs to the original author. The reprint of this site is for everyone to learn and exchange, and should not be used for any commercial activities.

2. This website does not assume any form of loss or injury caused by users to themselves and others due to the use of these resources.

3. For issues not covered in this statement, please refer to relevant national laws and regulations. In case of conflict between this statement and national laws and regulations, the national laws and regulations shall prevail.

4. If it infringes your legitimate rights and interests, please contact us in time, and we will delete the relevant content at the first time!

Contact: 010-60259772
E-mail: [email protected]

Communicate with professional consultants now!

  • National Service Hotline

    400-700-4979

  • Beijing Service Hotline

    010-60259772

Please be assured to fill in the information protection
Online consultation

Disclaimer

Thank you very much for visiting our website. Please read all the terms of this statement carefully before you use this website.

1. Part of the content of this site comes from the network, and the copyright of some articles and pictures involved belongs to the original author. The reprint of this site is for everyone to learn and exchange, and should not be used for any commercial activities.

2. This website does not assume any form of loss or injury caused by users to themselves and others due to the use of these resources.

3. For issues not covered in this statement, please refer to relevant national laws and regulations. In case of conflict between this statement and national laws and regulations, the national laws and regulations shall prevail.

4. If it infringes your legitimate rights and interests, please contact us in time, and we will delete the relevant content at the first time!

Contact: 010-60259772
E-mail: [email protected]