SQL Server Index

SQL Server Index
1. Clustered and nonclustered indexes
Clustered index: Sorted by clustered index, nonclustered indexes are faster than nonclustered indexes because they are not sorted by index key (one table has only one clustered index)
2. Unique and non-unique indexes
Unique index value cannot be duplicated

Principles for indexing:
1) The data column defining the primary key must be indexed.
2) The data column that defines the foreign key must be indexed.
3) It is best to index data columns that are queried frequently.
4) For data columns that require quick or frequent queries within a specified range;
5) Data columns that are frequently used in the WHERE clause.
6) often appear in the keyword order BY, group by, distinct after the field, to establish an index. If you are building a composite index, the field order of the index is consistent with the order of the fields following these keywords, otherwise the index will not be used.
7) For columns that are rarely involved in those queries, duplicate values are not indexed for more columns.
8) do not index columns for data types that are defined as text, image, and bit.
9) Avoid indexing for frequently accessed columns
9) Limit the number of indexes on the table. For a table that has a large number of update operations, the number of indexes is typically no more than 3, not more than 5. Although the index improves access speed, too many indexes can affect the update operation of the data.
10) Index The composite index, based on the frequency of the fields appearing in the query criteria. In a composite index, a record is first sorted by the first field. For records with the same value on the first field, the system is sorted by the value of the second field, and so on. Therefore, only the first field of the composite index appears in the query criteria, the index may be used, so the application of a high frequency field, placed in front of the composite index, will allow the system to use the index in the most possible way to play the role of the index.

Grammar
1. Syntax for creating indexes:
CREATE [unique][clustered | Nonclustered] INDEX index_name
on {table_name | view_name} [WITH [Index_property [,.... N]]
Unique: Create a unique index.
CLUSTERED: Build a clustered index.
Nonclustered: Builds a nonclustered index.
Index_property: Indexed properties.
A unique index can take either a clustered index structure or a nonclustered index structure, and the SQL Server system defaults to a nonclustered index structure if the index structure is not specified.

2. Remove the index syntax:
DROP INDEX Table_name.index_name[table_name.index_name]
Description: table_name: The name of the table where the index resides.
Index_name: The name of the index to remove.

3. Display the index information:
Use system stored procedure: SP_HELPINDEX [table_name] to view the index information for the specified table.

For example: There is a D table with D1 (number), D2 (VARCHAR2), D3 (number) Three fields, where the D1,d2 field is indexed.
SELECT * from D;--this time, although there is an index in the table, but will not go index, will be full table scan.
Select D1 from D;--this time will go D1 index;
SELECT * from D where d1=1,--will walk D1 index
SELECT * from D where d2=1,--does not walk the index at this time. Although D2 has an index, D2 is a character type, where conditions are numeric, plus a single citation can go D2 index.

SQL Server Index