SQL Server Tour--Nineth stop look at these composite indexes designed by the company's DBAs

This is the last topic of the index, index coverage, of course, the better way to learn is to look at the masters of the design of the index, to see what nutrients can be extracted from the things, let us see

Look at one of the core orders tables in the database.

　　

One: View the schema of the table

<1> See the approximate schema information for this table first

1--View schema information for table 2 SELECT c.column_id,c.name,t.name from Sys.columns as C 3 JOIN sys.types T4 on C.system_type_id=t.system_type _ID5 WHERE c.object_id=object_id (' o_orders ') 6 ORDER by c.column_id

There are about 89 fields from this order table ... Still a lot of, there may be too many historical reasons, there is a question here, for so many fields plus a variety of types, how to plan

Good single-column index and composite index ... Let's take a look at how these experts are designed.

<2> Composite Index

First of all, because my permissions are limited, DBCC IND,PAGE and other commands are not possible, so I have no ability to determine whether the following index is an include index or a composite index, so this is called a unified

Composite Index Bar.

1 SELECT name,type_desc from sys.indexes WHERE object_id=object_id (' o_orders ')

As you can see from the above, there are 9 nonclustered indexes, 1 clustered indexes, and then you can check several indexes by Show_statistics to see which fields are associated and find the two indexes.

Covers up to 6 columns, such as index "Idx_order_status_2", "Ix_o_ordersuid".

DBCC SHOW_STATISTICS (o_orders,idx_order_status_2) DBCC SHOW_STATISTICS (O_ORDERS,IX_O_ORDERSUID)

From the fields associated with these two indexes, you can probably see two points of information:

①: These fields are small, char (1), and smallint,bit, which naturally represents less state.

②: Select several fields with fewer states in the table to make an index in combination with the frequency of access.

But think about it, although in principle it is not appropriate to build an index, but a field like "Order Status" (Orderstatus) is definitely a column that is frequently queried ... Since it's a frequent column,

It's definitely going to be a way to optimize, by building composite indexes that make it easier to hit index overlays in complex SQL.

such as the following:

1 SET STATISTICS IO on 2 SELECT orderstatus, Processstatus, sendticketcity, Flightagency, Eticket, OrderID3 from dbo. Orders WHERE orderstatus= ' P ' and processstatus= ' 1 ' and Sendticketcity=1

Then continue to select a few indexes to aim at ... In general, indexes that cover between 1 and 2 columns are called small indexes.

1 DBCC SHOW_STATISTICS (o_orders,idx_eid_orderdate) 2 DBCC SHOW_STATISTICS (o_orders,ix_o_order_finishdate)

Through the above index can be seen, Eid and finishdate these two columns, a glance to know should be a unique relatively high column, and why to cover 2 columns, that this is based on business

and production of rolling data to determine, then what are the benefits of such an index? It's also easier to bump into an index link, which means that multiple indexes are going to be in the multi-criteria, and that each index contributes a few columns to just

Satisfy all columns in select ... such as the following.

1--You can see that all columns in select have Idx_eid_orderdate and ix_o_order_finishdate contributions of 2 select Orderid,finishdate,prepaytype,eid, OrderDate3 from  dbo. O_orders WHERE eid= ' CCTV1 ' and finishdate>2015-1-1

Well, as the friends of the park said, indexes are pay Paul, and each index you build needs to evaluate its pros and cons.

Original: http://www.cnblogs.com/huangxincheng/p/4269891.html

SQL Server Tour--Nineth stop look at these composite indexes designed by the company's DBAs