SQL Server Index

Reference Original: http://www.cnblogs.com/tjy9999/p/4494662.html

1. Nonclustered indexes

SET STATISTICSIo on SET STATISTICSTime on --1. No index (logical reads 568)SELECTFirstName, LastName fromDbo. ContactWHERELastName like 's%'  --To create a nonclustered indexIF EXISTS(SELECT *  fromSys.indexesWHERE object_id = object_id('Person.Contact') andName= 'FullName') DROP INDEXPerson.Contact.FullName;CREATE nonclustered INDEXFullName onperson.contact (LastName, FirstName);GO --2. Fully-covered queries (logical reads)SELECTFirstName, LastName fromDbo. ContactWHERELastName like 's%'   --3. Queries that are not fully covered (logical reads 568) SQL Server feels that using index lookups does more work than direct scanning and therefore does not use indexes.SELECT *  fromDbo. ContactWHERELastName like 's%'   --4. A query that is not fully covered (logical reads 111) changes the where condition, thereby reducing the scope of the query results and increasing the benefit of using the index, which SQL Server uses to reduce the query time by using index lookups.SELECT *  fromDbo. ContactsWHERELastName like 'ste%'

2. Clustered index

Nonclustered indexes are independent objects, have their own storage space, and clustered indexes and tables are the same object. When a clustered index is created, SQL Server sorts the table with key and maintains the sort when the data is modified. So when the key of the clustered index is the order number, the order of information in the same order is sequential in the table. There can be only one clustered index per table, because tables can only be arranged in one order. If there is no clustered index on a table, the table is also called a heap, so there are two types of tables: the clustered Index table and the heap table.

CREATE CLUSTERED INDEXIx_salesorderdetail ondbo. SalesOrderDetail (SalesOrderID, Salesorderdetailid)GO --1. Heap Table query (logical reads 1495) & Clustered Index Table query (logical reads 3)SELECT *  fromSalesorderdetailwhere SalesOrderID= 43671  andSalesorderdetailid=  ---2. Heap Table query (logical reads 1495) & Clustered Index Table query (logical reads 1513)--The ProductID column is not a key for a clustered index. Both tables are scanned for tables. Because a clustered index is included, the clustered index table is larger, so more times are scanned. SELECT *  fromSalesOrderDetailWHEREProductID= 755

3. Page and area

When SQL Server creates a database, it specifies where the data files are stored. SQL Server reads not a row, and the unit read is one page or more pages. The page is the smallest IO unit, and the size of each page is 8K. A partition contains 8 contiguous pages. The size of each row = the size of all columns + the header information of the row.

4. Include columns

• Each row in the table always has a portal in the index (this rule has an exception, which we'll talk about in the later levels). These portals are always sorted by index keys.
• In a clustered index, the entry of the index is the actual row of the table.
• In a nonclustered index, the entry and data rows are separated, and the index consists of the index key columns and labels, which are the mappings of the index key columns to the table data rows.

Those that often appear in select, rather than the columns in the WHERE clause, are best placed in the containing column. Indexed columns do not affect the ordering of index entries, only the entry for the index is updated, but no movement is required.

CREATE nonclustered INDEXfk_productid_ ModifiedDate onSales.SalesOrderDetail (ProductID, ModifiedDate) INCLUDE (OrderQty, UnitPrice, LineTotal)--the query is faster under a nonclustered index that contains columnsSELECTProductID, ModifiedDate,SUM(OrderQty) as 'No of Items',AVG(UnitPrice)'AVG Price',SUM(LineTotal)'Total Value'  fromSales.SalesOrderDetailWHEREProductID= 888 GROUP  byProductID, ModifiedDate;

5. Tags

Each row in the library can be identified by three numbers at any time: File number-page number-line number. These three-digit composite identifiers are called ROWID, often called RIDs. So file 1 of 77 pages of 12 lines, RIDs will be displayed as 1:77:12.

Nonclustered index of a heap table: RID-based label

Usually, the rows of a heap table are not moved, and once they are inserted into a page, they will remain on this page. It is more accurate to say that the rows in the heap table rarely move, and when they move, they leave a new address in the old location. Because the rows of the heap table do not move, the RID in the heap table permanently identifies each row. Not only is the value permanent, but the physical location is also permanent. The label for each row in the index is very efficient, pointing directly to the corresponding data row.

Nonclustered index for clustered index: key-based label

The rows of the clustered index table can be moved, and may be assigned to another page when the data is modified or the index is maintained. When a row of a clustered index is moved to a new page, it is moved only, and the key value of the clustered index does not change. The index key value can therefore be used as the label for the row. The key for a clustered index should meet three conditions: short, static, and unique. A change in the clustered index key value causes the entry of the corresponding row in each nonclustered index to occur with an update operation. Therefore, if a table has n nonclustered indexes, an update of the index key will become an update of n+1 times.

The entry for a nonclustered index consists of a query key column, a containing column, and a label. The value of a label can be either RID or the key of a clustered index, depending on whether the table is a heap table or a clustered index table. Choosing the best clustered index for a table requires you to make sure that the index key is a good label based on three rules.

SQL Server Index