Page 1/2 of MSSQL index creation

The index of a table is very similar to the index attached to a book. It can greatly improve the query speed. For a large table, adding an index usually takes several hours to complete a query. Therefore, there is no reason to add indexes to tables that require frequent queries.

Note:
When your memory capacity or hard disk space is insufficient, you may not want to add an index to a table. For databases that contain indexes, SQL server requires a considerable amount of extra space. For example, to create a clustered index, it takes about 1.2 times the data size. Take a look at the size of the index space occupied by a table in the database. You can use the system stored procedure sp_spaceused and specify the object name as the name of the table to be indexed.

Clustered index and non-clustered Index
Suppose you have found the page number of a sentence through the index of this book. Once you know the page number, you may find the book without any reason until you find the correct page number. Through random lookup, you can finally reach the correct page number. However, there is a more effective way to find the page number.

First, flip the book to about half of the place. If the page number to be searched is smaller than the page number at the half-book, the book will go to 1/4; otherwise, the book will go to 3/4. In this way, you can continue to divide the book into smaller parts until you find the correct page number. This is a very effective way to find a book page.

SQL Server table indexes work in a similar way. A table Index consists of a group of pages, which constitute a tree structure. By pointing to the other two pages, the root page logically divides the records of a table into two parts. The two pages pointed to by the root page split the records into smaller parts. Each page divides the record into smaller segments until it reaches the leaf-level page.

There are two types of indexes: clustered index and non-clustered index. In a clustered index, the leaf page of the index tree contains actual data: The index order of the records is the same as that of the physical order. In a non-clustered index, the leaf-level page points to the records in the table: the physical sequence of records is not necessarily related to the logical sequence.

The cluster index is very similar to a directory table. The order of the Directory table is the same as that of the actual page number. Non-clustered indexes are more like the standard index table of books. The order of the index table is usually different from that of the actual page number. A book may have multiple indexes. For example, it may have both the subject index and the author index. Similarly, a table can have multiple non-clustered indexes.

Generally, you use clustered indexes, but you should understand the advantages and disadvantages of both types of indexes.

Each table can have only one clustered index, because records in one table can only be stored in one physical order. Generally, you need to create a clustered index for a table based on the Identification field. However, you can also create a clustered index for other types of fields, such as numeric, numeric, and datetime fields.

Retrieving data from a table with a clustered index is faster than creating a non-clustered index. When you need to retrieve data within a certain range, it is better to use clustered indexes than to use non-clustered indexes. For example, suppose you use a table to record the activities of visitors on your site. If you want to retrieve the login information within a certain period of time, you should create a clustered index for the datetime type field in this table.

The main restriction on clustering indexes is that each table can only create one clustering index. However, a table can have more than one non-clustered index. In fact, you can create up to 249 non-clustered indexes for each table. You can also create clustering indexes and non-clustering indexes for a table at the same time.

Assume that you want to retrieve data not only by date, but also by user name from your site activity log. In this case, creating a clustered index and a non-clustered index at the same time is effective. You can create a clustered index for the date and time fields and a non-clustered index for the user name field. If you find that you need more indexing methods, you can add more non-clustered indexes.

Non-clustered indexes require a large amount of hard disk space and memory. In addition, although non-clustered indexes can speed up data retrieval from tables, they can also speed up data insertion and update to tables. Whenever you change the data in a table with a non-clustered index, you must update the index at the same time. Therefore, you must carefully consider creating a non-clustered index for a table. If you expect a table to frequently update data, do not create too many non-clustered indexes on it. In addition, if the hard disk and memory space are limited, you should also limit the number of non-clustered indexes.

Index attributes
Both types of indexes have two important attributes: You can use either of the two types to create an index (Composite Index) for multiple fields at the same time ); both types of indexes can be specified as unique indexes.

You can create a composite index or even a composite clustered index for multiple fields. Assume that a table records the surnames and names of visitors at your sites. If you want to retrieve data from the table based on the full name, you need to create an index for both the Last Name field and the name field. This is different from creating a separate index for the two fields respectively. When you want to query more than one field at the same time, you should create an index for multiple fields. If you want to query each field separately, you should create an independent index for each field.

Both types of indexes can be specified as unique indexes. If you create a unique index for a field, you cannot enter duplicate values for the field. An ID field automatically becomes a unique value field, but you can also create a unique index for other types of fields. Assume that you use a table to save the user password of your website. Of course, you do not want two users to have the same password. By forcing a field to become a unique value field, you can prevent this situation.