Creating nonclustered and nonclustered indexes in SQL Server

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