Creating nonclustered and nonclustered indexes in SQL Server

Source: Internet
Author: User

Clustered index and nonclustered index, in fact, there are a lot of articles have been detailed introduction.

Nonclustered indexes

Simply put, a clustered index is a column that is appropriate for a field that has little change (as far as possible without an update) and that has a small field repetition rate because the clustered index is the same index as the physical location of the data, so only one clustered index can appear in a single table.

characteristics of a clustered index : A location index representing all data stores, which cannot be updated frequently, preferably without duplication.

Clustered index

Nonclustered indexes are also known to be subdivided into unique indexes, full-text indexes, and ordinary indexes, and when we query for input conditions, the fields that are filtered as conditions are usually set to nonclustered indexes, so that you do not need to read the original records, and querying the indexes directly will save time.

the characteristics of nonclustered indexes : Do not affect the physical order of the data storage, the modification, deletion, addition of the field has little effect, so that we can put the frequently-wrong fields and have the need to quickly query the field to locate the nonclustered index

impact of incorrect build of clustered indexes : The index location may be rearranged every time the data is inserted, resulting in extremely slow write data.

Create a clustered index

 Use Log CREATE CLUSTERED  INDEX  on table name (field name)-- Create a clustered index

To create a nonclustered index

 Use Log -- libraries that you use CREATE nonclustered INDEX -- Create a nonclustered index name  on -- to create an index for the keywords field of a im_logsearch table  with FILLFACTOR =  - -- fill factor is 30% GO

Finally, the fill factor is mainly in some fields we can adjust according to the reading and writing scale flexibly, in order to play a larger role in indexing characteristics.

Fill factor can refer to:

Table with low change (read-write ratio 100:1): 100% fill factor
Table with high change (write over Read): 50-70% fill factor
Read and write each of the half: 80-90% filling factor

Creating nonclustered and nonclustered indexes in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.