Clustered, nonclustered index, unique index, composite index, System self-built index



Description: Red font to pay special attention to the point

An index is an object created on a database table or view to speed up queries on a table or view.

Grouped by storage: clustered and nonclustered indexes
According to the maintenance and Management index angle: Unique index, composite index and system automatically created index.

The structure of the index is composed of: root node---> Non-leaf node---> non-leaf node---> Leaf node

1. Clustered index : The data stored in the table is stored in the order of the index, the retrieval efficiency is higher than the normal index, but the impact on the data new/modified/deleted is relatively large. The logical order determines the physical order of the corresponding rows in the table.
Characteristics:
(1) A table can create up to 249 indexes
(2) Building a clustered index before creating a nonclustered index
(3) Non-clustered index data and index different order
(4) Data and index in different locations
(5) The index is stored on the leaf node and there is a "pointer" on the leaf node that points directly to the range of data to be queried
(6) data is not rearranged according to the order of the index keys

(7) If a range query is made on the field, or the table is rarely modified or deleted

Syntax for creating a clustered index:
Create nonclustered INDEX idximpid on EMP (EmpID)
2. Nonclustered index : does not affect the data store order in the table, the retrieval efficiency is lower than the clustered index, has little influence on the data new/modified/deleted
。 is a two-fork tree data structure to describe the logical sequence, features:
(1) No index, data unordered
(2) Index, data and index in the same order
(3) The data is rearranged according to the order of the index keys
(4) A table can have only one index
(5) The data pointed to by the leaf node is also stored in the same location
Grammar:
Create CLUSTERED INDEX idxempid on EMP (EmpID)
3, unique index: A unique index ensures that the indexed column does not contain duplicate values.
Multiple columns can be used, but the index ensures that each value combination in the indexed column is unique
Name
Li Yi
John
Harry
Syntax: Create unique index idxempid on EMP (first name, first name)

4. Composite Index : If you create an index on more than two columns, it is called a composite index.
Well, it's impossible to have two rows of surnames and names that are duplicates
Grammar:
Create INDEX Indxfullname on AddressBook (firstname,lastname)

Attention: If the composite clustered index field is queried separately.
With this question, let's take a look at the following query speed (the result set is 250,000 data): (Date column Fariqi first row at the beginning of the composite clustered index, user name Neibuyonghu in the latter column):
(1) Select Gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> ' ' 2004-5-5 '
Query speed: 2513 ms
(2) Select Gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> ' 2004-5-5 ' and neibuyonghu= ' ' Office '
Query speed: 2516 ms
(3) Select Gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu= ' office '
Query speed: 60280 ms
From the above tests, we can see that if you use only the starting column of the clustered index as the query criteria and the query speed of all columns that use the composite clustered index at the same time is almost the same, even faster than using all of the composite indexed columns (in the same case as the number of query result sets);This index is not useful if you are using only the non-starting column of a composite clustered index as a query condition。 Of course, the query speed of statements 1 and 2 is the same as the number of entries in the query, if all the columns of the composite index are used and the query results are few, this will result in an "index overlay", thus achieving optimal performance. Also, keep in mind:regardless of whether you use other columns that aggregate indexes frequently, the leading columns must be the most frequently used columns.

5, the system's own index:When you use a primary KEY or a unique constraint when you create a table using the T_sql statement, a unique index is automatically created on the table
Auto-created indexes cannot be deleted
Grammar:
CREATE TABLE ABc
(EmpID int primary KEY,
FirstName varchar (UNIQUE),
LastName varchar (UNIQUE),
)
This result comes out with three indexes, but only one clustered index OH


6, the method of creating the index:
1. Enterprise Manager
(1) Right click on a table, all tasks---Manage the index, open the management index, click New to create the index
(2) Design table in the design table, management index/Key
(3) in the diagram, after adding a table, right-click a table in the diagram, there is an index/key
(4) Through the wizard, the database---Create the index Wizard
(5) through T-SQL statements
2, can pass the "Index Optimization Wizard" to optimize the Index wizard, through which you can decide which columns to choose as the index column
　
Twowhen to use a clustered or nonclustered index

The following table summarizes when to use clustered or nonclustered indexes (very important):

Action Description	Using Clustered Indexes	Using Nonclustered indexes
Columns are often sorted in groups	Should	Should
Returns data in a range	Should	should not be
One or very few different values	should not be	should not be
A small number of different values	Should	should not be
A large number of different values	should not be	Should
frequently updated columns	should not be	Should
FOREIGN key columns	Should	Should
Primary key columns	Should	Should
Frequently modify index columns	should not be	Should

Original: http://sunct.iteye.com/blog/1933511