SQL Server Index concepts

Index: an index is a collection of tables or views. It is used to accelerate the retrieval of rows from a table or to enforce uniqueness.

Clustered index: an index created by physically ordered sorting.

Non-clustered index: an index created using an index table (equivalent to a book directory.

SQL data access method: 1. Table scanning; 2. Using indexes.

Clustered indexes can be seen as English dictionaries. dictionaries are sorted by A-Z. When we need to find a word, such as student, we can

To locate the half of the dictionary.

A non-clustered index can be viewed as a book directory.ArticleOn which page, you can find the page.

The clustered index determines the order in which the table rows are stored. Therefore, each table can have only one clustered index, and the rows in the table cannot be in multiple orders.

Storage.

Data Type: ntext text varchar (max) nvarchar (max) varbinary (max) XML image cannot be used as a clustered index,

The key of a non-clustered index.

When clustered indexes are used:
1. Use between, >,>=, <,<= and other operators to return a series of values.
2. Return the data sorted by the order by or group by clause.
3. Return the data merged using the join clause, which is generally a foreign key column.
4. Return a large result set

If the index table has a clustered index, one or more columns defined in the clustered index are automatically appended to the end of each non-clustered index on the table,

In this way, overwrite queries can be generated without specifying clustered index columns in the definition of non-clustered indexes. For example, if the table has a clustered index of column C, column B and column

The non-clustered index of a column uses the BAC column as its key value.

When using non-clustered indexes:
1. When a user needs to search for data in multiple ways, for example, the user may frequently search for student IDs and names. You can create a non-clustered index as name S.

The clustered index is the student ID.
2. If your table already has a clustered index and you need to index another column, you can only choose to use a non-clustered index.
3. When the index contains all the columns in the query, You can maximize the performance. The coverage refers to the index involved in the query.

Supported columns.
4. You want to use join and group by clauses to improve query performance. The application creates multiple non-clustered indexes for the join and group columns,

Create a clustered index for all foreign keys.
5. The table is updated frequently but contains a large amount of data.
6. No large number of results will be returned.
7. You need to index multiple columns with different values (such as the last name and name. Non-clustered indexes are most suitable for selecting data from high

Unique column.