Index key columns and containment columns

1, the primary key must be unique, not necessarily the clustered index, we create the primary key by default is to set the primary key is a clustered index. You can re-build the clustered index by manually deleting it.

2, the SQL statement is where to execute first, and then execute the order BY, so we are building a nonclustered index to note the order and the Where and order by the column inside the index key column. The Select section can be placed inside the included column, but be aware of the spatial problem of the index size.

3, order by inside the ascending and descending problem must be the same as the index key column.

Example: Select Id,title from table1 where classid=123 order by created DESC

Situation One

Action: Build a nonclustered index ix_a-> index key column classid (ascending descending does not matter), created (must be descending)

Note the order of the two fields in the index key column, and the two key columns are indispensable.

Performed: 1. Ix_a index Find out id,2. Find out title-> return results by key lookup by ID

Situation Two

Action: Build non-clustered index ix_b-> index key column classid (ascending descending does not matter), created (must be descending), add inclusive columns Id,title

Note the order of the two fields in the index key column, and the two key columns are indispensable.

Performed: 1. Ix_b index Lookup, return results

Above two methods if the sort of created is mistaken, there will be one more step, namely:

Situation one: 1. Ix_a index Find out id,2. Search by key by ID to find out title,3. Sort by, return results

Situation two: 1. Ix_b index Lookup, 2. Order-and-return results

Index key columns and containment columns