SQL Data Operations Basics (intermediate) 6

The tenth chapter of the data "SQL Basics" gives you a preliminary introduction to SQL. You learned how to query with SELECT statements, and you learned how to build your own tables. In this chapter, you will deepen your knowledge of SQL. You will learn how to build an index to speed up your query. You will also learn to manipulate the data in the table using more SQL statements and functions.

Establish an index

Suppose you want to find one of the sentences in this book. You can search page by page, but it will take a lot of time. By using the index of this book, you can quickly find the topic you are searching for.

The index of the table is very similar to the index appended to the book. It can greatly improve the speed of queries. For a larger table, a query that typically takes several hours to complete can be completed in a few minutes by adding an index. There is therefore no reason to add indexes to tables that require frequent queries.

Attention:

When your memory capacity or hard disk space is low, you may not want to add an index to a table. For databases that contain indexes, SQL sever requires a considerable amount of extra space. For example, to set up a clustered index, you need about 1.2 times times the size of the data space. To see how much space the index of a table occupies in the database, you can use the system stored procedure sp_spaceused, which specifies the name of the table to be indexed.

Clustered index and nonclustered index

Suppose you have found the page number of a sentence by the index of the book. Once you know the page number, you are likely to search the book aimlessly until you find the correct page number. By randomly searching, you can finally get to the correct page number. However, there is a more efficient way to find page numbers.

First of all, turn the book to about half of the place, if you want to find the page number is smaller than the half of the book, the book turned to One-fourth, otherwise, the book to Three-fourths places. In this way, you can continue to divide the book into smaller portions until you find the correct page number. This is a very effective way to find the pages.

SQL Sever table indexes work in a similar way. A table index consists of a set of pages that form a tree structure. The root page divides the records of a table logically into and two parts by pointing to another two pages. The two pages that the root page points to divide the records into smaller portions. Each page divides the records into smaller partitions until the leaf page is reached.

There are two types of indexes: Clustered index and nonclustered index. In a clustered index, the leaf page of the index tree contains the actual data: the records are indexed in the same order as the physical. In nonclustered indexes, leaf pages point to records in a table: the physical order of the records is not necessarily associated with the logical order.

Clustered indexes are very much like catalog tables, and the order of the table of contents is the same as the actual page number order. Non-clustered indexes are more like the standard index tables of books, and the order in the index table is usually inconsistent with the actual page order. A book may have multiple indexes. For example, it might have both a subject index and an author index. Similarly, a table can have multiple nonclustered indexes.

Typically, you are using a clustered index, but you should have an understanding of the pros and cons 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. Usually you have to set up a clustered index on a table by identifying the field. However, you can also create clustered indexes on other types of fields, such as character, numeric, and date-time fields.

Fetching data from a table that has a clustered index is faster than a table that has a nonclustered index set up. When you need to take out a certain range of data, the clustered index is better than the nonclustered index. For example, suppose you use a table to record the visitor's activity on your network. If you want to retrieve the login information for a certain period of time, you should set up a clustered index on the datetime field of the table.

The primary limitation to clustered indexes is that each table can only establish one clustered index. However, a table can have more than one nonclustered index. In fact, you can create up to 249 nonclustered indexes on each table. You can also create clustered and nonclustered indexes on a table.

If you want to take data from your network activity log, not only by date but also by user name. In this case, it is effective to establish both a clustered index and a nonclustered index. You can set up a clustered index on a date-time field and create a nonclustered index on the user name segment. If you find that you need more index options, you can add more nonclustered indexes.

Non-clustered indexes require a large amount of hard disk space and memory. In addition, although nonclustered indexes can increase the speed of fetching data from a table, it also reduces the speed of inserting and updating data into a table. Every time you change the data in a table that is not clustered, you must update the index at the same time. Therefore, you should consider carefully when establishing a nonclustered index on a table. If you expect a table to update data frequently, do not create too many nonclustered indexes on it. In addition, if the hard disk and memory space are limited, you should limit the number of nonclustered indexes used.

Indexed properties

Both types of indexes have two important attributes: You can index multiple fields (composite indexes) at the same time with either type, and both types of indexes can be specified as unique indexes.

You can create a composite index for multiple fields, or even a composite clustered index. If you have a table that records the last name and the name of your dot visitor. If you want to take the data from the table based on the full name, you need to create an index of both the last and first name fields. This is different from setting up separate indexes on two fields, respectively. When you want to query more than one field at the same time, you should create an index to multiple fields. If you want to query each field separately, you should create a separate index for each field.

Both types of indexes can be specified as unique indexes. If you set up a unique index on a field, you will not be able to enter duplicate values into the field. An identity field automatically becomes a unique value field, but you can also create a unique index for other types of fields. Suppose you use a table to store the user's password for your network, and you certainly don't want two users to have the same password. You can prevent this from happening by forcing a field to become a unique value field.