Create an index using SQL

This article describes the usage SQL Create an index.

Suppose you want to find a sentence in the book. You can search one page by one, but it takes a lot of time. By using indexes, you can quickly find the topic you want to search.

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 that the index of your book finds the page number of a sentence. 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.

Create an index using SQL

To create an index for a table, start the taskbar SQL ServerProgramISQL/W programs in the group. Enter the query window and enter the following statement:

The following is a reference clip: Create index mycolumn_index on mytable (myclumn)

This statement creates an index named mycolumn_index. You can give an index any name, but you should include the field name of the index in the index name, which is helpful for you to figure out the intention of creating the index in the future.

Note:

After the command is executed, the following information is received:

This command did not return data, and it did not return any rows

This indicates that the statement is successfully executed.

Index mycolumn_index to the mycolumn field of the table mytable. This is a non-clustered index and a non-unique index. (This is the default attribute of an index)

If you want to change the index type, you must delete the original index and recreate it. After an index is created, you can use the following SQL statement to delete it:

The following is a reference clip: Drop index mytable. mycolumn_index

Note that you must include the table name in the drop index statement. In this example, the index you deleted is mycolumn_index, which is the index of mytable.

To create a clustered index, you can use the keyword clustered. Remember that a table can only have one clustered index.

Here is an example of how to create a clustered index for a table:

The following is a reference clip: Create clustered index mycolumn_clust_index on mytable (mycolumn)

If the table contains duplicate records, an error occurs when you try to use this statement to create an index. However, you can create indexes for tables with duplicate records. You only need to use the keyword allow_dup_row to tell SQL Sever:

The following is a reference clip: Create clustered index mycolumn_cindex on mytable (mycolumn) with allow_dup_row

This statement creates a clustered index that allows Repeated Records. You should try to avoid repeated records in a table. However, if the record already appears, you can use this method.

To create a unique index for a table, you can use the keyword unique. This keyword can be used for clustered indexes and non-clustered indexes. Here is an example:

The following is a reference clip: Create unique coustered index myclumn_cindex on mytable (mycolumn)

This is the index creation statement that you will frequently use. Whenever possible, you should try to create a unique clustered index for a table to enhance query operations.

Finally, you need to create an index for multiple fields -- composite index -- the index creation statement contains multiple field names at the same time. The following example creates an index for the firstname and lastname fields:

The following is a reference clip: Create index name_index on username (firstname, lastname)

In this example, a single index is created for two fields. In a composite index, You Can index up to 16 fields.

Create an index using the Transaction Manager

Creating an index using the Transaction Manager is much easier than using SQL statements. Using the Transaction Manager, you can view the list of created indexes and select the index option on the GUI.

You can use the Transaction Manager to create an index in two ways: Use the manage tables window or use the manage indexes window.

Create a new index in the manage tables window and click the advanced Options button (it looks like a table with a plus sign in front ). The advanced Options dialog box is displayed. In this dialog box, some labels are named primary key.

To create a new index, select the field name you want to create an index from the drop-down list. If you want to create an index for multiple fields, you can select multiple field names. You can also choose whether the index is clustered or non-clustered. After the table information is saved, the index is automatically created. A key appears next to the field name in the manage tables window.

You have created a "primary index" for your table ". The primary index must be created for fields that do not contain null values. In addition, the primary index forces a field to become a unique value field.

To create indexes without these restrictions, you need to use the manage indexes window. Select Manage | indexes from the menu to open the manage indexes window. In the manage indexes window, you can select a table and a specific index from the drop-down list. (See Figure 11.2 ). To create a new index, select new index. From the index drop-down box, and then select the field to be indexed. Click Add to add the field to the index.

You can select many different options for your index. For example, you can choose whether the index is clustered or non-clustered. You can also specify this index as a unique index. After the index is designed, click build to create the index.

Note:

A unique index means that this field cannot have duplicate values, rather than creating this index.