Establishment of SQL Index

(from:http://54laobaixing.blog.163.com/blog/static/57843681200952411133121/)

Suppose you want to find a sentence in a book. You can search pages page by page, but it will take a lot of time. And by using the index,

You can quickly find the topic you want to search for.
The index of the table is very similar to the index attached to the back of a book. It can greatly improve the speed of queries. For a larger table,

By adding an index, a query that typically takes several hours to complete can be done in a matter of minutes. Therefore, there is no reason to frequently check

Increase the index of the table being consulted.
Attention:
You might not want to add an index to a table when your memory capacity or hard disk space is low. For databases that contain indexes, the SQL

Sever requires a considerable amount of extra space. For example, to create a clustered index, you need about 1.2 times times the size of the data. To see

When you look at the size of a table's index in the database, you can use the system stored procedure sp_spaceused, which specifies the object name to be

The table name of the index.

Clustered and non-clustered indexes
Suppose you have found the page number of a sentence in the index of the book. Once you know the page number, you are likely to wander

Book until the correct page number is found. By randomly searching, you can finally reach the correct page number. However, there is one way to find the page number more

An effective method.
First, turn the book to about half of the place, if you want to find the page number than the half book page, the book turned to One-fourth, otherwise

, you turn the book to Three-fourths of the place. In this way, you can continue to divide the book into smaller parts until you find the correct page number attached

Nearly. This is a very effective way to find the page.
The table index of SQL Sever works in a similar way. A table index consists of a set of pages that form a tree structure. Root Page Pass

Point to another two pages, dividing the records of a table logically into and two parts. And the two pages that the root page points to separate the records.

into a smaller part. Each page divides the records into smaller segments until they reach the leaf level page.
There are two types of indexes: clustered and non-clustered indexes. In the clustered index, the leaf page of the index tree contains the actual data: the recorded

The index order is the same as the physical order. In a nonclustered index, a leaf-level page points to a record in a table: the physical order and logical order of the records do not necessarily

The contact.
Clustered indexes are very much like catalog tables, and the order of the table of contents is consistent with the actual page number order. Non-clustered indexes are more like standard indexes of books

Table, the order in the index table is usually inconsistent with the actual page number order. A book may have multiple indexes. For example, it may also have primary

Index and author indexes. Similarly, a table can have multiple nonclustered indexes.
Typically, you are using clustered indexes, but you should understand the pros and cons of both types of indexes.
Each table can have only one clustered index, because records in one table can be stored in only one physical order. Usually you have to follow a table

Identifies a field to establish a clustered index. However, you can also create clustered indexes on other types of fields, such as character type, numeric type, and date time

Type field.
Extracting data from a table that has a clustered index is faster than a table with a nonclustered index set up. When you need to take out a certain range of data

, it is better to use a clustered index than a non-clustered index. For example, suppose you use a table to record a visitor's activity on your network. If you want to take

The login information for a certain period of time, you should establish a clustered index on the table's datetime field.
The primary limitation on clustered indexes is that only one clustered index can be established per table. However, a table can have more than one non-clustered index.

In fact, you can create up to 249 nonclustered indexes per table. You can also set up clustered and nonclustered indexes on a table at the same time

。
If you want to take data from your network activity log not only by date, but also by username. In this case, the simultaneous establishment of

A clustered index and non-clustered index are valid. You can set up a clustered index on a datetime field, and create a non-clustered cable for the user name segment

Cited. If you find that you need more indexing, you can add more non-clustered indexes.
Non-clustered indexes require a lot of hard disk space and memory. In addition, while nonclustered indexes can improve the speed of fetching data from tables, it also

Decreases the speed at which data is inserted and updated into the table. Whenever you change the data in a table that has a nonclustered index set up, you must also

The new index. Therefore, you should consider the non-clustered index of a table carefully. If you anticipate that a table needs to update data frequently, it is not

You want to build too many nonclustered indexes on it. In addition, if the hard disk and memory space is limited, you should also limit the number of nonclustered indexes to use.

Indexed properties
Both types of indexes have two important properties: You can index multiple fields at the same time using either of the two types (composite cable

Both types of indexes can be specified as unique indexes.
You can create a composite index on multiple fields, or even a composite clustered index. If you have a table that records your network visitors,

's first and last name. If you want to take the data from the table based on the full name, you need to create a call to both the last Name field and the First Name field

Cited. This is different from establishing a separate index on two fields respectively. When you want to query more than one field at the same time, you should build

An index to multiple fields. If you want to query each field individually, you should establish separate indexes on each field.
Both types of indexes can be specified as unique indexes. If you set a unique index on a field, you will not be able to enter this field

into duplicate values. An identity field automatically becomes a unique value field, but you can also create a unique index on other types of fields. Suppose you

Using a table to save the user password for your network, you certainly do not want two users to have the same password. By forcing a field to be unique

Value field, you can prevent this situation from occurring.

Indexing with SQL
To index a table, start the ISQL/W program in the Task bar SQL Sever program group. After entering the query window, enter the following

Statement:

CREATE INDEX Mycolumn_index on MyTable (myclumn)

This statement establishes an index named Mycolumn_index. You can give any name to a lasso, but you should be in the index name

Contains the field names that are indexed, which is helpful for you to figure out the intention of establishing the index in the future.
Attention:
Once executed, you will receive the following information:
This command does not return Data,and it does not return any rows
This indicates that the statement was executed successfully.
The index Mycolumn_index the MyColumn field of the table MyTable. This is a non-clustered index and is also a non-unique index. (This is

Default property of an index)
If you need to change the type of an index, you must delete the original index and build one. After establishing an index, you can use the following

The SQL statement for the polygon deletes it:

DROP INDEX Mytable.mycolumn_index

Note in the drop INDEX

You want to include the name of the table in the statement. In this example, the index you delete is Mycolumn_index, which is the index of the table mytable.
To create a clustered index, you can use the keyword clustered. Remember that a table can have only one clustered index.
Here's an example of how to create a clustered index on a table:

CREATE CLUSTERED INDEX mycolumn_clust_index on MyTable (MyColumn)

If there are duplicate records in the table, an error occurs when you try to use this statement to make an index. But tables with duplicate records can also be created

The index; you just use the keyword allow_dup_row to tell SQL Sever:

CREATE CLUSTERED INDEX mycolumn_cindex on MyTable (MyColumn) with Allow_dup_row

This statement establishes a clustered index that allows duplicate records. You should try to avoid duplicate records in a table, but if you have

After the advent, you can use this method.
To create a unique index on a table, you can use the keyword unique. This keyword can be used for both clustered and non-clustered indexes. This

There is an example:

CREATE UNIQUE coustered INDEX myclumn_cindex on MyTable (MyColumn)

This is the index-building statement that you will use frequently. Whenever possible, you should try to create a unique clustered index on a table.

To enhance the query operation.
Finally, to create an index of multiple fields--compound index--contains multiple field names in the index creation statement. The following example

The FirstName and LastName two fields are indexed:

CREATE INDEX Name_index on username (firstname,lastname)

This example establishes a single index on two fields. In a composite index, you can index up to 16 fields.

Indexing with transaction manager
Indexing with the transaction manager is much easier than using SQL statements. Using the transaction manager, you can see the list of indexes that have been established and

You can select indexing options from the graphical interface.
With transaction manager you can index in two ways: using the Manage Tables window or using the Manage Indexes window.
To create a new index with the Manage Tables window, click the button Advanced Options (it looks like a plus sign in front

's table). This opens the Advanced Options dialog box. This dialog box has a section labeled Primary Key.

To create a new index, select the name of the field you want to index from the drop-down list. If you want to create an index on multiple fields,

You can select multiple field names. You can also choose whether the index is clustered or not clustered. After saving the table information, the index is automatically created

。 A key appears next to the field name in the Manage Tables window.
You have established a "primary index" for your table. The primary index must be established for fields that do not contain null values. In addition, the primary index enforces a word interleaved

As a unique value field.
To create an index without these restrictions, you need to use Manage

Indexes window. Select manage| from the menu Indexes, open the Manage Indexes window. In the Manage Indexes window,

You can select a table and a specific index from the drop-down box. (see Figure 11.2). To create a new index, select New from the Index drop-down box

Index., and then you can select the field to which you want to index. Click the button add to add the field to the index.

You can choose a number of different options for your index. For example, you can choose whether the index is clustered or not clustered. You can also specify

The index is a unique index. After you have designed the index, click button Build to build the index.

Attention:
The unique index is that the field cannot have duplicate values, rather than just establishing this index.